No image

Oracle Block Structure

  • Block Layout

Oracle blocks usually use 8K sizes in OLTP environments. The first 20 bytes is an area called Block Header or Cache Layer. Oracle blocks usually use 8K sizes in OLTP environments. The first 20 bytes is an area called Block Header or Cache Layer. All areas of this Cache Layer have a common suffix"kcbh" (kernel cache block header) and store general information such as type and version format of the block. Generally, the type of data block with ITL is 06, and the first byte begins with 06 in the figure above.

The second area is Transaction Layer, which is 24 bytes each for Transaction Header and Intersted Transaction Lists. The default value is usually 2, so the figure shows two ITLs. The 8bytes after 72 bytes in the Transaction Layer is an unused area and then the Data area begins. The row offset value expressed as the record is added is the starting point for the data region. The Data Area consists of 14 bytes Data Header, Table Directory, and Row Directory.

 

     1. Block Header (Cache Layer)

 

     2. Transaction Header

 

     3. ITL (Interested Transaction Lists)

 

     4. Data Header

 

     5. Table Directory, Row Directory

Table Directory 03 means there are three records in this block, and row offset 1's '1f88' is the offset from Data Header start point 'c063' to data row #1 start point. In addition, the first byte '2c' of data row #3 is Flag Byte, which is the binary number '00101100' and corresponds to 'fb: --H-FL—' of the oracle formatted dump below. 

The second byte, '01', means that transactions of data row #3 are currently being managed in ITL #1.
The third byte, '02', indicates that there are two columns of data row #3, followed by '0a', which means the length of the column is 10.
The value of the first column '01' then means the length of the column 1 and the last '41' is the value of the second column, A.
The last 4 bytes of Oracle Block is a tail check, a combination of the last 2 bytes of SCN base and the block Type SCN sequence. Here SCN base 0x00a7be66 + Type 06 + SCN seq 0x01 => 'be660601' becomes the tail check value.

 

  • Block Layout

      - Oracle internal structure when one record is made on Oracle block

When one record is INSERT on the Oracle block, it is first assigned the TX Slot of Undo Segment and the location is recorded as ITL's XID value of 0x000b.000.000005da. Store the INSERT prefix in the opening block and record its location as ITL's UBA '0x018000d2.0313.01. The ITL's Lck column records the number of records related to transactions currently being processed by the ITL, '1'. The value of ITL's Flag is related to Oracle's Delayed Block Cleanout and is discussed again in the next chapter. Register Row Offset #1 with Row Directory and save the record from the bottom of the block.

        - Oracle internal structure when two records are made on Oracle block

When you enter the second record, you are assigned a different ITL and enter the second block from the bottom of the block in the same procedure as the first.

        - Oracle internal structure when three records are made on Oracle block

Since the two transactions processed earlier before entering the third block have been commited, change the ITL's Lck to zero, set the Flag to 'C---', and change the Data Row #1 and Data Row #2's Lock Byte to '00'. When the third block is entered, reuse ITL '01' to complete the entry from the bottom of the block in the same procedure.

Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]