Principles of in-depth analysis of Oracle data blocks data blocks (Oracle Data Blocks), the paper referred to as the "block" is the smallest unit of storage Oracle, Oracle data is stored in "block" in the. A block occupied by some disk space. Special attention is paid, where the "block" is Oracle's "data blocks", not the operating system "block."
Oracle time for each request, are in blocks. In other words, Oracle data per request is a multiple of the block. If Oracle is less than a piece of data requested, Oracle will read the entire block. Therefore, the "block" is the smallest unit of data in Oracle or read the basic unit.
The standard block size specified by the initialization parameter DB_BLOCK_SIZE. The size of the blocks with a standard known as standard block (Standard Block). The standard block size and the size of the block is called non-standard blocks of different block (Nonstandard Block). The same database, Oracle9i and above support both the same database using the standard blocks and non-standard block. Oracle allows you to specify five kinds of non-standard blocks (Nonstandard Block).
Each time the operating system I / O, when the operating system is based on the block as a unit; Oracle each execution of the I / O time, are based on Oracle's block as a unit.
Oracle data block size is generally a multiple operating system blocks.
The format of the data block (Data Block Format)
Block of data stored in the table and index data, regardless of what type of data stored, the format is the same block, block by the block header (header / Common and Variable), Table catalog (Table Directory), line catalog (Row Directory ), free space (Free Space) and the row of data (Row Data) of five parts,
As shown below.
Block header (header / Common and Variable): the basic information storage block, such as: the physical address of the block, the block belongs to the type of segment (a segment or index segment.)
Table catalog (Table Directory): store the table information, that is: if some form of data is stored in the block, then the information about these tables will be stored in the "Table directory".
Line catalog (Row Directory): If there are rows of data block exists, then, these lines of information will be recorded line directory. This information includes the address line.
Rows of data (Row Data): a real table data and index data stored in the place. This part of the space is already occupied by rows of space.
Free space (Free Space): a block of free space is not used in the region, this area used to insert new rows and update rows that already exist.
Header information area (Overhead): We block header (header / Common and Variable), Table catalog (Table Directory), line catalog (Row Directory) together known as the head of the three-part information area (Overhead). Header information area does not store data, store it in the entire block of information. Area the size of the header information is variable. In general, the header information of 84 bytes between the size (bytes) to 107 bytes (bytes) between.
Block the use of free space when inserted into the database (INSERT) data, the block in the free space will be reduced; When the block to modify existing rows (UPDATE) when (the record length increases), the block The free space will be reduced.
DELETE statement and the UPDATE statement causes the block of free space increase. When using the DELETE statement to delete records in the block or use the UPDATE statement to change the value of the column values into a smaller time, Oracle will release part of the free space. Released free space is not necessarily continuous. Normally, Oracle does not block the free space is not contiguous to merge. Because the combined data block is not contiguous free space will affect the performance of the database. Only when the user data into (INSERT) or update (UPDATE) operations, but can not find contiguous free space when, Oracle will merge the data block is not contiguous free space.
Free space for the block, Oracle offers two management practices: automatic management, manual links, and line management line migration (Row Chaining and Migrating)
Line link (Row Chaining): If we insert into the database (INSERT) line of data, this line of data so large that a whole block of data storage is not the next line, Oracle will be divided into paragraphs, there is a row of data into several data block, a process called line link (Row Chaining). As shown below:
If the row of data is an ordinary line, this line of data can be stored in a data block; if the row is a link line, this line of data stored in multiple data blocks.
Bank Transfer (Row Migrating): a record exists in the data block, the user perform UPDATE Update this record, the UPDATE operation so that the variable-length record, this time, Oracle in the data block to find, but could not find be able to accommodate Under this record the space, desperation, Oracle only the whole row of data to a new data block. Retain the original data block of a "pointer", the "pointer" points to the new data block. This moved the record ROWID remains unchanged. The principle line of migration as shown below:
Link line or lines, whether migration will affect the performance of the database. Oracle at the time to read such a record, Oracle will scan multiple data blocks, performs more I / O.
Block, Oracle automatically manage the free space bitmap (bitmap) to manage and track data blocks, this block of space management is called "automatic management." Automatic management of the following benefits:
Easy to use ◆ ◆ ◆ better use of space can be adjusted in real time on the space of free space block users can manually manage PCTFREE, PCTUSED to adjust the block, the use of space, the management approach called managed manually. Relative to the automatic management of cumbersome manual management is not easy to grasp, easy to cause a waste of space in the block.
PCTFREE parameter specifies the smallest block of free space must be retained percentage of cases. The reason to set aside this space, because the UPDATE, you need the space. If the UPDATE, there is no free space, Oracle will allocate a new block, which will produce the line migration (Row Migrating).
PCTUSED is also used to set a percentage, when the space has been used in the block is less than the percentage of the time, this block is marked as active. Only valid blocks are allowed into the data.