Oracle data block implementation principles in-depth interpretation (GO)

Oracle manages the storage space in a database data file (datafile) as a data block. A data block is the smallest (logical) unit of data in a database. Corresponding to the data block, the smallest physical storage unit of all data at the operating system level is byte (byte). Each operating system has a parameter called block size. Each time Oracle acquires data, it always accesses an integer (Oracle) block of data instead of accessing it 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, users can specify five non-standard block sizes (nonstandardblock size). The block capacity should be set to an integer of the operating system block capacity (which is less than the maximum limit for block capacity) in order to reduce unnecessary I/O operations. Oracle data blocks are the smallest storage units that Oracle can use and allocate.

Data block structure

In Oracle, the internal structure of a table (table), index, or cluster table (clustered data) is similar regardless of whether the data block is stored. The block structure diagram looks like this:

This diagram shows the various components of the data block, including: Data size (including standard content and variable content) (Common and variable header), Table directory area (table directory), row directory area (row directories), Free space, the row data area. The two arrows in the figure indicate that the capacity of the free space area in a block is variable.

Data block (includes standard content and variable content)

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

Table Directory Area

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 (table directory).

Row Directory Area

This area contains information about the rows of data stored in the data block (row piece) addresses in the row data area of each data line fragment. [A data block may hold a complete row of data, or only part of the data row, so use row piece]

When the row directory space of a data block is used, the row directory space is not reclaimed, even if the data row is deleted (delete). For example, when a block of data that once contained 50 records is emptied, the row directory area of its header will still occupy 100 bytes (byte) of space

Administrative overhead

The data block header, the table directory area, and the row directory area (rowdirectory) are collectively referred to as administrative overhead (overhead). Some of the overhead capacity is fixed, while the total capacity of some of the overhead is variable. The capacity of fixed and variable management overhead in a data block averages between 84 and 107 bytes (byte).

Row data

The data block row data area contains the actual data for the table or index. A data row can span multiple blocks of data.

Free Space Area

When you insert a new row of data, or when you need more space to update a row of data (for example, the last character of a row blank (trailing null), you are now updating to a non-null value), the space in the free space area is used. If a data block is part of a table or cluster table's data segment, or an index segment (index segment) that belongs to the index, the transaction entry (transaction Entry) may also be stored in its free space area. If the data row (row) in a data block is being made by Insert,update,delete, and SELECT ... For UPDATE statement, you need to save the transaction entry in this data block. The storage space required for transaction entries depends on the operating system. In a common operating system, the transaction entry takes approximately 23 bytes (byte).

Available space management

Free space can be managed manually or automatically

In the database, the free space for each segment (segment) can be automatically managed. The available/used space in a segment is recorded as a bitmap (bitmap), which differs from the management of the available blocks in a list. Automatic segment Space management (Automatic Segment-space Management) offers the following benefits:

Easy-to-use space utilization is more efficient, especially for tables (or other objects) with large differences in data capacity per row to better adjust Better multi-instance behavior in real time for current data conditions. In terms of performance/space utilization

Users can select the automatic Segment space management (Automatic segment-space Management) feature when creating a locally managed tablespace (locally managed tablespace). This way, the segments created in this tablespace are set to automatic segment space management by default.

Validity and optimization of the available space of the data block

There are two types of SQL statements that can increase the free space in a block: DELETE statements, and UPDATE statements that have existing data values that consume less capacity. In the following two conditions, the space freed by the above two operations can be used by subsequent INSERT statements:

If the INSERT statement is in the same transaction as the above two operations (transaction) and is located after the statement that freed the space, the INSERT statement can use the freed space. If the INSERT statement and the statement that frees the space are in a different transaction (for example, the two are submitted by a different user), the INSERT statement uses the freed space only after the statement that freed the space is committed and the data block is required to insert the data.

The freed space in the data block may not be contiguous with the free space area. Oracle merges freed space into free space when the following conditions are met: (1) The INSERT or UPDATE statement selects a block of data with enough free space to hold the new data, (2) but the free space in the block is not contiguous and the data cannot be written to contiguous space in the data block. Oracle merges the available space in a block only when the above conditions are met, in order to avoid too frequent spatial consolidation affecting database performance.

Line link (row Chaining) and row migration (row migrating)

There are two situations in which a row of data in a table is too large to fit in a data block. In the first case, a block of data cannot be accommodated when a row of data is inserted. In this case, Oracle stores this row of data in a block chain (chain) within a segment. A row link (row chaining), such as a data row containing a data type of long or long RAW columns, occurs frequently when inserting rows with large amounts of data. The line link is unavoidable at this time.

In the second case, the rows of data that were originally stored in a block of data, because the update operation resulted in length growth, and the available space of the data block did not accommodate the growth of the data rows. In this case, Oracle migrates this row of data (migrate) to the new data block. Oracle holds a pointer to the new data block where the migrated data row was originally located. The rowid of the migrated data row remains unchanged.

When a data row is linked (chain) or migrated (migrate), access to it will result in an I/O performance degradation because Oracle must access more data blocks when it obtains data for these data rows.

pctfree,pctused, and line link (row Chaining)

In a manually managed tablespace (manually managed tablespaces), users can use the two storage management parameters of Pctfree and pctused to control insert and update operations on a segment (segment). How to use the available space in the data block that belongs to this segment. Users can also set the PCTFREE parameter for a user when creating or modifying an index (the index is stored in the index segment)

PCTFREE parameters

The PCTFREE parameter is used to set the minimum amount of free space (in percent) to be reserved in a data block, in preparation for the amount of data that may occur when data is updated in the data block. For example, the following parameters are specified when the user creates a table with the CREATE TABLE statement:

PCTFREE 20

This parameter sets each data block (DataBlock) in the data segment for this table to hold at least 20% of the free space to be used when the data in the block is updated. As long as the data area of the block and the volume of data is not more than 80% of the total capacity of the block, the user can insert new data into it, the data row is put into the row data area, the relevant information is written to the data size (overhead areas)

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 based on the sum of the data area (row data) and the size of the data block (overhead) and the maximum percentage of the total capacity of the chunk. When the percentage of free space in a data block is less than the Pctfree parameter, Oracle considers that this block cannot be used to insert new data until the footprint ratio in the block is less than the pctused parameter's qualification. Until the capacity ratio is greater than the limit of the pctused parameter, Oracle only uses the available space for this block of data when it updates data in the block. For example, the following parameters are specified when the user creates a table with the CREATE TABLE statement:

Pctused 40

In the example, Oracle does not use this block of data to insert new rows of data when a chunk of this table occupies more than 40% of its capacity (assuming that the available space for this block was less than the PCTFREE limit)

The role of pctused

How PCTFREE and pctused work together

PCTFREE and pctused work together to optimize the spatial use of data blocks.

Managing the free space of a data block using the PCTFREE and pctused parameters

Illustrates how PCTFREE and pctused work together to manage the use of the data block's available space.

In the first step, the data block occupies less than 80% of the space to insert new data, because the Pctfree parameter qualifies that 20% of the free space must be reserved for updates to existing data within the block. In the second step, the reserved space in the data block can be used for update operations that already have data in the data block. You can insert new data into a block only if it occupies less than 40% of the space within it. In the third step, this block can again be used to insert new data when the amount of space within the block is less than 40%. In the fourth step, the data block occupies less than 80% of the space to insert new data, because the Pctfree parameter qualifies that 20% of the free space must be reserved for updates to existing data within the block. This process is so cyclic.

In the newly allocated block (data block), the space available for inserting data is equal to the total capacity of the block minus the chunk overhead and the reserved free space (PCTFREE). The existing data in the update data block can use all the available space in the data block. As a result, update operations can limit the amount of free space within a block to PCTFREE, because these spaces are reserved for update operations.

In each segment (data segment) and index segments (index segment), Oracle manages one or more available block lists (free list), which lists all data extensions (extent) that belong to this segment, and the percentage of free space is greater than PCTFREE a restricted block of data. These blocks can be used by inserting (insert) operations. When the user submits an INSERT statement, Oracle selects the first valid block to use from the list of available blocks. If the available space for this block is insufficient to accommodate the data submitted by the INSERT statement, and the block occupies more than pctused, Oracle removes it from the list of available blocks. A segment can use multiple available block lists at the same time to reduce the contention that occurs when concurrent insertions (concurrent inserts) are made to a table.

When a user submits a DELETE or UPDATE statement, Oracle processes the statement and checks to see if the footprint ratio in the relevant data block is less than the pctused rule. If satisfied, the block is placed in the header of the available block list (free list) being used by the current transaction (transaction), which is used first if the current transaction also needs to write data. When a transaction commits, the free space in this data block can also be used by other transactions.

Transfer from http://www.jb51.net/article/31929.htm

Oracle data block implementation principles in-depth interpretation (GO)