<?xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/>
Data Block ( Data Block ) Principle of in-depth analysis
What is a data block
Data Block (Oracle Data Blocks), the Oracle block size is typically an integral multiple of the operating system block.
This article is referred to as "block", which is the smallest Oracle storage unit and Oracle data is stored in "block". A block occupies a certain amount of disk space. Notably, the "block" here is Oracle's "block", not the "block" of the operating system.
Each time Oracle requests data, it is in blocks. That is, the data that Oracle requests each time is an integral multiple of blocks. Oracle will also read the entire block if Oracle requests less than one piece of data. So, "block" is the smallest unit or most basic unit of Oracle Read and write data.
The standard size of the block is specified by the initialization parameter db_block_size. Blocks with a standard size are called standard blocks (Standard block). Block size and standard block size of different blocks are called non-standard blocks (nonstandard block). In the same database , oracle9i and above support the use of both standard and non-standard blocks in the same database. Oracle allows the designation of 5 non-standard blocks (nonstandard block).
Each time the operating system executes I/O, it is in the block of the operating system, and each time Oracle performs I/O, it is in the block of Oracle.
The format of the block (data blocks format)
Blocks contain the data of the table and the indexed data, regardless of which type of data is stored, the format of the blocks is the same, the blocks are Header/common and Variable, table directory (table directory), row directory, The free spaces and row data (row) are composed of five parts, as shown in the following figure.
Block size (Header/common and Variable) : The basic information of the block, such as the physical address of the block, the type of segment to which the block belongs (whether it is a data segment or an index segment).
Table directory (table directory : The information that holds the table, that is, if some of the table's data is stored in the block, then the relevant information for those tables is stored in the table directory.
Row directory (row directory ): If there are rows of data in the block, the information for those rows is recorded in the row directory. This information includes the address of the line, and so on.
line data ( row ): is where the table data and index data are actually stored. This part of the space is space that has been occupied by the data row.
Free Spaces ): Free space is an unused area of a block that is used for inserting new rows and updating existing rows.
Head Information area (overhead : We put the block size (Header/common and Variable), table directory (table directory), row directory (row directory) These three parts are called the header information area (overhead). The header information area does not hold the data, it stores the entire block of information. The size of the header information area is variable. In general, the header information area is between 84 bytes (bytes) and 107 bytes (bytes).
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml"/> use of free space in data blocks
When inserting (insert) data into a database, the free space in the block is reduced, and the free space in the block is reduced when you modify (UPDATE) the existing rows in the block (to increase the length of the record).
The DELETE statement and the UPDATE statement increase the free space in the block. When you use the DELETE statement to delete a record in a block or use an UPDATE statement to change the value of a column to a smaller value, Oracle frees up a portion of the free space. The free space released is not necessarily continuous. In general, Oracle does not merge discontinuous free space in blocks. Because discontinuous free space in a merged block of data can affect the performance of the database. Oracle merges discontinuous free space in a block of data only when the user is inserting (insert) or updating (update) operations, but cannot find contiguous free space.
for free space in a block, Oracle offers two kinds of Management mode: Automatic management, manual management of row links and row migrations (row chaining and migrating)
row link (Row chaining If we insert (insert) a row of data into a database that is so large that a block of data cannot be stored in a single row, Oracle will divide a row of data into several pieces of data that are called line links (row chaining). As shown in the following illustration:
If a row of data is a normal row, this row of data can be stored in a block of data, and if a row of data is a link row, the row of data is stored in more than one block of data.
row migrations (row migrating): There is a record in the block of data in which the user performs update update, and the update operation makes the record longer, at which point Oracle searches the data block. But unable to find the space to hold this record, Oracle can only move the entire row of data to a new block of data. The original block of data retains a "pointer", which points to the new block of data. The rowid of this record being moved remains unchanged. The principle of row migration is shown in the following illustration:
Whether the row or row is migrated, the performance of the database can be affected. When Oracle reads such a record, Oracle scans multiple blocks of data and performs more I/O. Automatic management of free space in block
Oracle uses bitmaps (bitmap) to manage and track blocks of data, which are called "automated management" in the form of space management. Automatic management has the following benefits:
* Easy to use
* Better use of space
* Can be used to adjust the space in real time free space in the Block manual management
Users can adjust the use of space in blocks by Pctfree, pctused, which is called manual management. Compared with the automatic management, manual management method is more troublesome, not easy to master, easy to cause a waste of space in the block.
The Pctfree parameter is used to specify the minimum amount of free space that must be retained in the block. The reason to reserve such a space is because the update requires these spaces. If there is no space in the update, Oracle assigns a new block, which results in a row migration (migrating).
Pctused is also used to set a percentage, which is identified as a valid state when the proportion of space used in the block is less than this percentage. Only valid blocks are allowed to insert data.
row links, row migration Solutions
If there is a row link in the database, row migration can severely affect the performance of the database. Therefore, we should try to eliminate the row link and row migration. Follow these steps to eliminate row and row links.
STEP1: Creating a data dictionary table chained_rows
The Data dictionary table chained_rows is used to receive analyze ... List CHAINED rows data, the data dictionary table chained_rows created by script utlchain.sql or script utlchn1.sql.
STEP2: Analyzing a table
ANALYZE TABLE study LIST CHAINED ROWS;
Executing this command will write the results of the analysis to the table chained_rows
STEP3: Identify the link line or migration line
SELECT *
From Chained_rows
WHERE table_name = ' study ';
Owner_name table_name Clust ... Head_rowid TIMESTAMP
---------- ---------- -----... ------------------ ---------
TT student_infor ... AAAALUAAHAAAAA1AAA 04-mar-2008
TT student_infor ... Aaaaluaahaaaaa1aab 04-mar-2008
TT student_infor ... AAAALUAAHAAAAA1AAC 04-mar-2008
Chained_rows a query out of a link row or migration line from a data dictionary table. The statement lists the link rows or migrated rows that exist in the table student_infor. If there is a link row or migration row in the table, follow the steps below, and if the statement does not have any output, the table does not have a link row or migration row.
STEP4: Creating an intermediate table
CREATE TABLE Int_study
As SELECT *
From study
WHERE ROWID in
(SELECT Head_rowid
From Chained_rows
WHERE table_name = ' study ');
Intermediate table Int_study is used to temporarily store link rows or migrate rows. The structure of the table must be exactly the same as the structure of the table study (the table in this example). After executing the above command, the link line or migration line has been inserted into the intermediate table Int_study.
STEP5: Delete link rows and migration rows from existing table study
DELETE from study
WHERE ROWID in
(SELECT Head_rowid
From Chained_rows
WHERE table_name = ' study ');
STEP6: Inserts data from the middle table into an existing table student_infor
INSERT into study
SELECT *
From Int_study;
This step allows the original data row to be reinserted, eliminating row migration.
STEP7: elimination of link lines
If a link row exists in Chained_rows, you can only increase the size of the data block to allow a row of data to be accommodated in a block of data. However, if the column type is long, or if it is a larger char,varchar2, the row link will not be avoided.
data block sizeselection
Usually the size of the database block is 8K, but for the applications that are not used (OLTP
