In-depth interpretation of Oracle data block implementation principles

Learning oracle 10g r2 concepts in the afternoon

Leave a note here.

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 an integer (Oracle) data block, instead of accessing data according to the size 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 (nonstandardblock size ). The block capacity should be an integer (smaller than the maximum limit) of the operating system block capacity to reduce unnecessary I/O operations. Oracle data blocks are the minimum storage units that Oracle can use and allocate.

Data Block Structure

In Oracle, whether the data block stores a table, index, or cluster data, its internal structure is similar. The block structure is as follows:

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 two arrows in the figure indicate that the capacity of the available space area in a data block is variable.

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 ).

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.

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.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.

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.

Row 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.

Available space 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, transaction entries take about 23 bytes ).

Available space management

Available space can be manually or automatically managed

In the database, the available space of each segment can be automatically managed. The available/used space in the specified segment is recorded as bitmap, which is different from the list-based management of available blocks. Automatic segment space management has the following advantages:

● Ease of use ● higher space utilization efficiency, especially for tables (or other objects) with large data capacity differences per row) ● Better adjust the current data in real time ● Better multi-instance behavior. in terms of performance/space utilization

You can select the automatic segment space management function when creating a locally managed tablespace (locally managed tablespace. In this way, the segments created in this tablespace are set to automatic segment space management by default.

Effectiveness and optimization of data block available space

There are two types of SQL statements that can increase the available space in the data block: The DELETE statement and the UPDATE statement that updates the existing data value to a smaller value. Under the following two conditions, the space released by the above two operations can be used by subsequent INSERT statements:

● If the INSERT statement and the preceding two operations are in the same transaction and are placed after the statement to release 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, they 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.

Row Chaining and 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 ).

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)

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 (datablock) in the data segment corresponding to this table should retain at least 20% of the available space for the new 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)

Explains 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, when you CREATE a TABLE using the create table statement, the following parameters are specified:

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 is lower than that of PCTFREE)

Functions of PCTUSED

How PCTFREE and PCTUSED work together

PCTFREE and PCTUSED work together to optimize the space usage of data blocks.

Use PCTFREE and PCTUSED parameters to manage the available space of data blocks

 

Explains how 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 the limit of PCTUSED, Oracle will remove 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.