In-depth analysis of Oracle data block principles

Data Blocks (Oracle Data Blocks) are the smallest storage unit in Oracle. Oracle Data is stored in Blocks. A block occupies a certain amount of disk space. Note that the "block" here is the "data block" of Oracle, not the "block" of the operating system ".
Each time Oracle requests data, the unit is block. That is to say, the data in each request of Oracle is an integer multiple of the blocks. If the amount of data requested by Oracle is less than one, Oracle will also read the entire block. Therefore, "Block" is the minimum or most basic unit for Oracle to read and write data. The standard block size is specified by the initialization parameter DB_BLOCK_SIZE. A Block with a Standard size is called a Standard Block ). The Block size is different from the standard Block size. A non-standard Block is called a non-standard Block ). In the same database, Oracle9i and later versions support the use of both standard and non-standard blocks in the same database. Oracle allows you to specify five non-standard blocks ). Www.2cto.com
Each time the operating system executes I/O, the unit is the operating system block. Each time the Oracle executes I/O, the unit is the Oracle block.
The size of an Oracle data block is generally an integer multiple of that of an operating system block. The Data Block Format is used to store table Data and index Data. No matter what type of Data is stored, the Block Format is the same, blocks are composed of header (header/Common and Variable), Table Directory (Table Directory), Row Directory (Row Directory), Free Space (Free Space), and Row Data (Row Data) composed of five parts,
As shown in. Header (header/Common and Variable): stores the basic information of a block, such as the physical address of the block and the type of the block (data segment or index segment ). Table Directory: Stores Table information, that is, if some Table data is stored in this block, information about these tables will be stored in the "Table directory. Row Directory: If the block contains Row data, the information of these rows is recorded in the Row Directory. This information includes the address of the row. Row Data: the place where table Data and index Data are stored. This space is occupied by data rows. Free Space: an unused area in a block. This area is used for inserting new rows and updating existing rows. Overhead: the header (header/Common and Variable), Table Directory, Row Directory) these three parts are collectively referred to as the header information area (Overhead ). The header information area does not store data. It stores the information of the entire block. The size of the header information area is variable. Generally, the header information area is between 84 bytes (bytes) and 107 bytes (bytes.
Free Space in the data block www.2cto.com when data is inserted into the database, the free space in the block is reduced; when the existing row in the block is modified (UPDATE) when the length of the record increases, the free space in the block is also reduced. The DELETE statement and the UPDATE statement increase the free space in the block. When you use the DELETE statement to DELETE records in a block or use the UPDATE statement to change the column value to a smaller value, Oracle releases some free space. The free space released is not necessarily continuous. Generally, Oracle does not merge discontinuous free space in blocks. Because the discontinuous free space in the merged data blocks will affect the database performance. Oracle merges discontinuous free space in data blocks only when you INSERT or UPDATE data but cannot find continuous free space.
For free space in blocks, Oracle provides two management methods: Automatic Management, manual management of Row links and Row Chaining ): if we INSERT a row of data into the database, this row of data is so large that a data block cannot store a whole row, oracle Splits a Row of data into several data blocks. This process is called Row Chaining ). As shown in:
If a row of data is a normal row, this row of data can be stored in one data block; if a row of data is a link row, this row of data is stored in multiple data blocks.
Www.2cto.com Row migration (Row Migrating): There is a record in the data block. The user executes the UPDATE operation to UPDATE this record, which leads to a longer record. At this time, oracle searches for this data block, but cannot find the space that can hold this record. However, Oracle can only move the entire row of data to a new data block. The original data block retains a "Pointer", which points to the new data block. The rowid of the moved record remains unchanged. Shows the principle of row migration: whether it is row link or row migration, it will affect the database performance. When Oracle reads such a record, Oracle scans multiple data blocks and executes more I/O operations. Oracle uses bitmap (bitmap) to manage and track data blocks in free space. This block space management method is called "Automatic Management ". Automatic Management has the following benefits: ◆ ease of use ◆ better space utilization ◆ manual management of free space in real-time space adjustment blocks users can use PCTFREE, PCTUSED is used to adjust the space usage in the block. This management method is called manual management. Compared with automatic management, manual management is more troublesome and difficult to master, which may lead to a waste of space in blocks.
The parameter www.2cto.com PCTFREE is used to specify the minimum percentage of free space that must be retained in a block. This space is reserved because it is required during UPDATE. If there is no free space during UPDATE, Oracle will allocate a new block, which will generate Row migration (Row Migrating ).
PCTUSED is also used to set a percentage. When the percentage of space used in the block is smaller than this percentage, the block is marked as valid. Only valid blocks are allowed to insert data.