An in-depth interpretation of Oracle data block implementation principle _oracle

In the afternoon learning Oracle 10g R2 Concepts

Leave a pen here.

The unit in which Oracle manages storage space in the database data file (datafile) is the data block. A block of data is the smallest (logical) unit of data in a database. The minimum physical storage unit of all data at the operating system level is bytes (byte), corresponding to the block of data. Each operating system has a parameter called block size. Each time Oracle acquires data, it always accesses an integer (Oracle) block of data, rather than accessing the 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, 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 (at the same time less than the maximum 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 the data block is similar regardless of whether it is stored in tables (table), index, or cluster table (clustered data). The block structure diagram looks like this:

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

Data size (including standard content and variable content)

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

Table Directory Area

If a datasheet stores rows of data in this block, the information for the datasheet is recorded in the table directory area of the data block (catalog directory).

Row Directory Area

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

When a data block's Row directory space 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 size (header) still occupies 100 bytes (byte) of space

Administrative overhead

The data block header, the table directory area (directory), 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 overhead is variable. The volume of fixed and variable management overhead in a block is averaged between 84 and 107 bytes (byte).

Row data

The data block (row data) in the row is comprised of 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 if you need more space to update a row of data (for example, if the last character of a line blank (trailing null) is now updated to a non-null value), you will use space in the available space area (free spaces). A Transaction entry (transaction Entry) may also be stored in its free space area if a data block is part of a table or cluster table (data segment) or an indexed segment (index segment) that belongs to the index. If the data row (row) in a block of data is being made by Insert,update,delete, and SELECT ... For UPDATE statement, this block of data requires that transaction entries be saved. The storage space required for transaction entries depends on the operating system. In a common operating system, transaction entries require approximately 23 bytes (byte).

Free space Management

Free space can be managed manually or automatically

Database, the available space for each segment (segment) can be automatically managed. The available/used space within a segment is recorded as a bitmap (bitmap), which differs from the available block management in a list manner. Section Space Automatic Management (Automatic segment-space Management) has the following advantages:

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

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

The validity and optimization of the free space of data block

There are two types of SQL statements that can increase the amount of free space in a block of data: The DELETE statement, and update the existing data value to an UPDATE statement that occupies a smaller value. 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 (transaction) as the above two operations, and after the statement that frees the space, the INSERT statement can use the freed space. If the INSERT statement and the statement to free space are in a different transaction (for example, the two are submitted by different users), the INSERT statement uses the freed space only after the statement that freed the space is committed and the data block must be used to insert the data.

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

Row links (row chaining) and row migration (row migrating)

There are two situations that cause a row of data in a table to be too large for a block of data to hold. In the first case, when one row of data is inserted, a block of data cannot be accommodated. In this case, Oracle stores this row of data in a data block chain (chain) within the segment. A row link (row chaining), such as a data row that contains a long or long RAW column, is often occurred when inserting rows with large data. The line link is unavoidable at this time.

In the second case, a row of data that was originally stored in a data block, because the update operation caused the length to grow, and the free space in the block of data could not accommodate the growing rows of data. In this case, Oracle migrates this row of data (migrate) to the new data block. Oracle saves a pointer to a new block of data at the location where the migrated data line 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 can degrade I/O performance because Oracle must access more data blocks when it obtains data from these data rows.

Pctfree,pctused, and row links (row chaining)

In a manually managed tablespace (manually managed tablespaces), users can control insert and update operations on a segment (pctused) using both the Pctfree and segment storage management parameters. How to take advantage of the free space in the data block that belongs to this segment. Users can also set PCTFREE parameters for the index when they are created or modified (the index is stored in the index segment (indexed segment))

PCTFREE parameters

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

PCTFREE 20

This parameter sets each data block (DataBlock) in the corresponding data segment of this table (segment) to retain at least 20% of the available space for use when data is already updated in the block. The user can insert new data into the data block as long as the sum of the data area of the row and the size of the database does not exceed 80% of the total capacity of the block, and the data row is put into the row data area (row and field) and the relevant information is written to the data size (overhead area

The following figure illustrates the role of Pctfree:

pctused parameters

The pctused parameter is used to determine whether a block of data can be used to insert new data, based on the sum of the capacity of the data area (row) and the size of the data block (overhead), and the maximum percentage of the total chunk capacity. When the amount of free space in a block of data is less than the Pctfree parameter, Oracle considers that the block cannot be used to insert new data until the size of the data block is less than the limit of the pctused parameter. Oracle uses the available space for this block of data only when the data is already in the data block when the occupancy ratio is greater than the pctused parameter. For example, when a user creates a table with a CREATE TABLE statement, the following parameters are specified:

Pctused 40

In the example, Oracle will not use this data block for inserting new data rows when a block of data in this table occupies a capacity greater than 40% (assuming that the available space for this block is once lower than the PCTFREE limit)

The role of pctused

How PCTFREE and pctused play a role together

PCTFREE and pctused can optimize the space use of data blocks.

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

The diagram above illustrates how PCTFREE and pctused work together to manage the use of data block free space.

In the first step, the data block occupies less than 80% space to insert new data, because the Pctfree parameter qualifies that 20% of the available space must be retained for updates to the existing data in the block. In the second step, you can use the reserved space in the data block for update operations on the data in the block. You can insert new data into a data block only if the percentage of space occupied is less than 40%. In the third step, the data block can again be used to insert new data when the percentage of space occupied in the block is less than 40%. In step fourth, the data block takes up less than 80% space to insert new data, because the Pctfree parameter qualifies that 20% of the available space must be retained for updates to the existing data in the block. This process is so reciprocating.

In a newly allocated block of data, the space that can be used to insert (insert) data is equal to the total capacity of the block minus the size of the data blocks (overhead) minus the reserved free space (PCTFREE). Existing data in the update (update) block can use all available space in the data block. As a result, the update operation enables the PCTFREE limit of the free space within the data block to be lower, because the space is reserved for update operations only.

Oracle manages one or more list of available blocks (free list) in each data segment (segment) and index segment (segment)--which lists all data extensions (extent) that belong to this segment, with a ratio of free space greater than PCTFREE a qualified block of data. These blocks can be used by inserts (insert) operations. When the user submits an INSERT statement, Oracle selects the first valid block of data from the list of available blocks to use. If the available space for this block is insufficient to accommodate the data submitted by the INSERT statement, and the capacity of this block has exceeded the pctused limit, Oracle will remove it from the list of available blocks. A segment can use more than one list of available blocks at the same time to reduce the competition that occurs when a table is concurrently inserted (concurrent insert).

When the user submits a DELETE or UPDATE statement, Oracle processes the statement and checks that the percentage of space occupied in the relevant data block is less than the pctused requirement. If satisfied, the block is placed on the head of the list of available blocks (free list) being used by the current transaction (transaction), which will be used first if the current transaction needs to write data. When a transaction is committed, the available space in this block can also be used by other transactions.