Go Detailed introduction to Oracle Data Block architecture

Data Block Overview 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 obtains data, it always accesses the entire number of data blocks (Oracle) rather than the capacity of the operating system block to access the data.

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 (nonstandard block size). Block capacity should be set to an integer multiple of the capacity of the operating system block (at the same time 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.

See also: More information about block capacity (data block size) in Oracle documents for specific operating systems multiple data block capacity (multiple block Sizes)

2.2.1 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. Figure 2-2 illustrates the structure of the data block.

Figure 2-2

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 area ( Free space), row data area. The following sections explain each component separately. The two arrows in the figure indicate that the capacity of the free space area in a block is variable.

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

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

2.2.1.3 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. Row Directory area space is only re-used when new data is inserted (insert) in the data block.

2.2.1.4 Management Overhead

The data block header, the table directory area (row directory), is collectively referred to as the 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).

2.2.1.5 rows of 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. Here's the line link (row Chaining) and row migration (row migrating)

2.2.1.6 Free Space Zone

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 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, there are approximately two types of SQL statements that need to be used to increase the free space in the data block: The DELETE statement, and the UPDATE statement that updates the existing data value to a smaller size. 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 the free space zone when the following conditions are met:

(1) The INSERT or UPDATE statement selects a block of data with enough free space to accommodate the new data.

(2) However, the free space in this block is not contiguous, and the data cannot be written to contiguous space in the 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.

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

See also:

1. "Data line structure and length" For information about the structure of data rows

2. "Rowid of the data line" For information about ROWID

3. "Physical Rowid" learn about Rowid Oracle Database Performance Tuning Guide Learn how to reduce row link and row migration in order to improve system I/O performance byte (byte).

2.2.3 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 the insertion and update operation of a segment (segment), using blocks of data that belong to this segment ( The available space in the data block. The user can also set the PCTFREE parameter for the index when it is created or modified (the index is stored in the index segment).

Tips:

The contents of this section do not apply to LOB data types (Blob,clob,nclob, and BFILE). These types of data are stored without using the PCTFREE parameter and the list of available blocks (free list).

2.2.3.1 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 a user creates a table with the CREATE TABLE statement: PCTFREE 20 This parameter sets the data block for each chunk (data segment) in this table to hold at least 20% of the available space. Used when the data in the block has been 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 placed into the row data area, and the relevant information is written to the data size (overhead areas).

The pctused parameter 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 a 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 the table has a block of capacity greater than 40% (assuming that the available space for this block has been less than PCTFREE of the limit).

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

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.

2.3 Data Extension Overview

Data extension (extent) is a database logical storage allocation unit consisting of a contiguous set of data blocks. The segment (segment) is comprised of one or more data extensions. Oracle allocates a new data extension for this segment when the space already in the segment is exhausted.

When the 2.3.1 data extension is assigned

When a user creates a data table, Oracle allocates an initial data extension (initial extent) that contains several data blocks for this table's data segment. Although there is no data in the data table at this time, the data blocks in this initial data extension are ready to insert new data.

If the data block in the initial data extension (segment) of a segment (initial extent) is full and new data is inserted to require space, Oracle automatically allocates an incremental data extension for this segment (incremental extent )。 An incremental data extension is a subsequent data extension that is assigned after a segment trunk has been expanded, and her capacity is greater than or equal to the previous data extension.

In order to manage the need, each segment (segment) of the segment header (header block) contains a directory that records all data extensions (extent) for this segment.

From "Bit Network" original link: http://soft.chinabyte.com/database/56/12199056.shtml

Go Detailed introduction to Oracle Data Block architecture