Introduction to Oracle Data Block System

 

Data Block overview

Oracle manages the storage space in the database data file. The unit is data block ). A data block is the smallest (logical) data unit in a database. The minimum physical storage unit of all data at the operating system level is byte ). Each operating system has a parameter called block size. Each time Oracle acquires data, it always accesses the entire data block (Oracle), instead of accessing data according to the capacity of the operating system block.

The standard data block capacity in the database is specified by the initialization parameter db_block_size. In addition, you can specify five non-standard data block capacities (nonstandard block size ). The block capacity should be set to an integer multiple of the block capacity of the operating system (also smaller than the maximum limit of the block capacity) to reduce unnecessary I/O operations. Oracle data blocks are the minimum storage units that Oracle can use and allocate.

See also:

Oracle documents for specific operating systems contain more information about data block size.

Multiple block sizes)

2.2.1 data block structure

In Oracle, whether the data block stores a table, index, or cluster data, its internal structure is similar. Figure 2-2 illustrates the structure of the data block.

Figure 2-2 data block structure

 

 

This figure shows the components of a data block, including the common and variable header, table directory ), row directory, free space, and row data ). The following sections describe the components. The two arrows in the figure indicate that the capacity of the available space area in a data block is variable.

2.2.1.1 data block header (including standard content and variable content)

The data block header contains the summary information of the data block, such as the block address and the segment to which the data block belongs (for example, table or index ).

2.2.1.2 table directory

If a data table stores data rows in this data block, the data table information is recorded in the table directory area of the data block.

2.2.1.3 row directory

 

This area contains the information of the data rows stored in the data block (the address of each row segment (row piece) in the row data area ). [A data block may save a complete data row or only a part of the data row. Therefore, we use row piece]

　

When the row directory space of a data block is used, the row directory space will not be recycled even if the row is deleted. For example, when a data block that once contains 50 records is cleared, its header (header) Row directory still occupies 100 bytes of space. The row directory space will be reused only when new data is inserted into the data block.

2.2.1.4 management overhead

Data Block header, table directory, and row directory are collectively referred to as overhead ). Some overhead capacity is fixed, while some overhead total capacity is variable. The average capacity of fixed and variable management overhead in data blocks is between 84 and 107 bytes (bytes.

2.2.1.5 rows of data

The row data area of a data block contains the actual data of a table or index. A Data row can span multiple data blocks. This shows "Row chaining" and "Row migrating)

 

2.2.1.6 Zone

When inserting a new data row or updating a data row requires more space (for example, if the last field of a row is null (trailing null), it must be updated to a non-null value ), the free space is used.

If a data block is a data segment of a table or cluster table, or an index segment of an index ), the transaction entry may also be stored in the zone ). If the row in a data block is being accessed by the insert, update, delete, and select... for update statements, the transaction entries must be saved in the data block. The storage space required for transaction entries depends on the operating system. In common operating systems, there are about two types of SQL statements required for transaction entries to increase the available space in data blocks:
Delete statement, and update the existing data value to an update statement with a smaller capacity. Under the following two conditions, the space released by the above two operations can be used by subsequent insert statements:

 

 

If the insert statement is in the same transaction as the preceding two operations and is located after the statement that releases the space, the insert statement can use the space to be released.

If the insert statement and the space release statement are in different transactions (for example, the two are submitted by different users), only after the space release statement is submitted, when this data block is required for data insertion, the insert statement uses the space to be released.

The space released in a data block may not be contiguous with the free space. Oracle merges the released space into the available space only when the following conditions are met: (1) the insert or update statement selects a data block with enough available space to accommodate new data, (2) However, the available space in this block is not consecutive and data cannot be written into the continuous space in the data block. Oracle only merges the available space in the data block when the preceding conditions are met. This is done to avoid too frequent space merge operations affecting the database performance.

Occupies 232.2.2.2 row Link (row chaining) and row migration (row migrating)

In either case, the data of a row in the table is too large and cannot be accommodated by a data block. In the first case, when a row of data is inserted, a data block cannot be accommodated. In this case, Oracle stores this row of data in a chain within the segment. When a row with a large amount of data is inserted, row chaining usually occurs. For example, a data row containing long or long raw columns. In this case, the line link is inevitable.

In the second case, the data rows originally stored in a data block increase in length due to the update operation, the available space of the data block cannot accommodate the increasing data rows. In this case, Oracle migrates this row of data (migrate) to a new data block. Oracle saves a pointer to the new data block in the original location of the migrated data row. The rowid of the migrated data row remains unchanged.

When a data row is linked or migrated (migrate), access to it will result in lower I/O performance, because Oracle obtains the data of these data rows, you must access more data blocks ).

See also:

1. "Data row structure and length"

2. "rowid of data rows" to learn about rowid

3. "physical rowid" for rowid Information

Oracle Database Performance Tuning Guide describes how to reduce row links and migrate rows to improve system I/O performance byte ).

2.2.3 pctfree, pctused, and row chaining)

In a manually managed tablespace (manually managed tablespaces), you can use the pctfree and pctused storage management parameters to control the insert and update operations on a segment, how to use the available space in the data block that belongs to this segment. You can also set the pctfree parameter when creating or modifying an index (the index is stored in the index segment ).

Tip:

 

This section does not apply to lob data types (blob, clob, nclob, and bfile ). The pctfree parameter and free list are not used for data storage of these types ).

 

2.2.3.1 pctfree Parameters

The pctfree parameter is used to set at least the available space (percentage value) to be retained in a data block ), prepare for the possible increase in data volume when there is data update in the data block. For example, when you create a table using the create table statement, the following parameters are specified:

Pctfree 20

This parameter sets that each data block in the corresponding data segment of the table should retain at least 20% of the available space for the updated data in the block. As long as the sum of the capacity of the row data zone and the data block header in the data block does not exceed 80% of the total capacity of the data block, you can insert new data into it, data rows are placed in the row data area, and related information is written into the data block header (overhead area ). Figure 2-3 illustrates the role of pctfree.

 

Pctused Parameters

The pctused parameter is used to determine whether a data block can be used to insert new data. It is based on the Data Partition (row data) and data block header (overhead) the maximum percentage of the total capacity of the data block. When the proportion of available space in a data block is smaller than the pctfree parameter, Oracle considers that the data block cannot be used to insert new data, until the proportion of the occupied capacity in the data block is smaller than the limit set by the pctused parameter. Before the capacity proportion is greater than the pctused parameter, Oracle uses the available space of this data block only when updating existing data in the data block. For example
The following parameters are specified when the create table statement is used to create a table:

Pctused 40

In this example, when the proportion of a data block in this table is greater than 40%, oracle does not use this data block to insert new data rows (assuming that the available space of this data block was lower than that of pctfree ). Figure 2-4 illustrates the role of pctused.

 

Figure 2-4

How pctfree and pctused work together

Pctfree and pctused work together to optimize the space usage of data blocks.

 

How does pctfree and pctused work together to manage the use of data block space.

 

In step 1, new data can be inserted only when the proportion of space occupied by data blocks is less than 80%, because the pctfree parameter limits that 20% of the available space must be reserved for updating existing data in the block.

 

In step 2, you can use the reserved space in the data block to update existing data in the data block. New data can be inserted into a data block only when the proportion of occupied space in the data block is less than 40%.

 

In step 3, when the proportion of occupied space in the data block is less than 40%, the data block can be used to insert new data again.

 

In step 4, new data can be inserted only when the proportion of space occupied by data blocks is less than 80%, because the pctfree parameter limits that 20% of the available space must be reserved for updating existing data in the block. This process is so cyclical.

In the newly allocated data block, the space used for inserting (insert) data is equal to the total capacity of the data block minus the data block header (Block overhead) then subtract the reserved space (pctfree ). However, the existing data in the update data block can use all the available space in the data block. Therefore, the update operation can lower the available space in the data block than the pctfree limit, because these spaces are reserved for the update operation.

In each data segment and index segment, Oracle manages one or more free lists of available blocks) -- All data extensions (extent) that belong to this segment are listed, and the proportion of available space is greater than that specified by pctfree. These blocks can be used by insert operations. After you submit an insert statement, Oracle selects the first valid data block from the available block list. If the available space of this data block is insufficient to accommodate the data submitted by the insert statement, and the occupied capacity of this block has exceeded pctused
Oracle removes it from the list of available blocks. One segment can use multiple available block lists at the same time to reduce competition for concurrent insert (concurrent insert) to a table.

After the user submits a delete or update statement, the Oracle processing statement checks whether the proportion of occupied space in the data block is smaller than that specified by pctused. If yes, the data block is placed in the header of the Free List in use by the current transaction (transaction). If the current transaction still needs to write data, this block will be used first. After a transaction is committed, the available space in this data block can be used by other transactions.

2.3 Data expansion Overview

Data extension (extent) is a logical storage allocation unit of a database composed of a group of continuous data blocks. Segments are composed of one or more data extensions. When the existing space in a segment is used up, Oracle allocates new data extensions for this segment.

2.3.1 when data expansion is allocated

When you create a data table, Oracle assigns an initial data extension (initial extent) containing several data blocks to the Data Segment of the table ). Although there is no data in the data table at this time, the data block in this initial data expansion is ready to insert new data.

If the data block in the initial data extension (initial extent) of a segment is full and new data insertion requires space, oracle automatically allocates an incremental data extension (incremental extent) for this segment ). Incremental data expansion is the subsequent data expansion allocated after the expansion of existing data in a segment relay. Its capacity is greater than or equal to the previous data expansion.

For management purposes, the header block of each segment contains a directory that records all the data extensions of this segment (extent.