Index Block Dump: Block Header Part II and Read Consistency (I Can’t Read)

OK, let’s look at the next portion of the index block dump.
 
Following the hex dump of the block (as we ended Part I of the series) is the second part of the block header (see below):   

Block header dump:  0x0201490a
 Object id on Block? Y
 seg/obj: 0x1c205  csc: 0x00.2d11214  itc: 2  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.02d11214

  

The following details are listed initially:
 
0x0201490a – rdba again
seg/obj: 0x1c205 – data_object_id again
csc: 0x00.2d11214 – initial scn of block
itc: 2 – number of Interested Transaction List (ITL) entries in the block
typ: 2 – INDEX  – denotes this specific block as indeed an Index block
 
Then follows the Interested Transaction List (Itl) which is used by Oracle to store critical information of any transaction making changes within the block. An index leaf block is basically assigned 2 ITL entries by default (but only 1 if it’s an index Branch block). However the number of pre-assigned ITL entries is set by the INITRANS physical attribute parameter of the index. 

A ITL entry basically consists of:
 
A unique slot ID (starting with 01)
Transaction ID
Undo Block Address (Uba) which denotes the Undo segment assigned to the specific transaction
Flag and Lock Bytes
System Change Number (SCN) 
 

Any transaction wishing to make a change to the block must first be allocated to an ITL entry, with the first ITL entry (01) reserved by Oracle for internal recursive operations. If there’s an ITL entry that’s not being used by a current transaction, great, a new transaction will simply use a free ITL entry. However, if all ITL slots are already assigned to current transactions within the index block, then an additional ITL entry is created and allocated to the new transaction by Oracle, providing there’s sufficient free space within the block or the 255 limit hasn’t been reached (or the MAXTRANS parameter is not reached in older versions of Oracle).
 
As a simple demo to see this in action, I’m going to insert two additional rows into the table (and so create two new index entries in the index): 

 
SQL> insert into bowie values (4, 'THIN WHITE DUKE');
 
1 row created.
 
SQL> insert into bowie values (5, 'ALADDIN SANE');
 
1 row created.
 
SQL> commit;
 
Commit complete.

         
If we now dump the index block and look at the ITL slots:
 

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x000a.006.000065d4  0x00c00441.2fb5.09  --U-    2  fsc 0x0000.02d120c8

   

We see the transaction has used ITL slot number 02 as relevant details are now assigned.
 
Points of interest includes the Flag is now set to U (Unclean) and that the Lock attribute is assigned the value 2. This means the transaction has been committed but the index block has not been cleaned out and is currently “unclean” in that the 2 new index rows associated with the transaction still have the lock byte set (as we’ll see in a later post).
 
The other important piece of information is the value of the SCN within the ITL (0x0000.02d120c8). The SCN is basically a timestamp that denotes when the transaction made the associated changes to the index block. As this is the last transaction to have changed the block, this SCN effectively denotes the point of time when the block was last changed.
 
If we now also look at the start of the block dump and the block header details discussed in Part I of this series:
 

Start dump data blocks tsn: 8 file#:8 minblk 84234 maxblk 84234
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=33638666
BH (0x157E78EC) file#: 8 rdba: 0x0201490a (8/84234) class: 1 ba: 0x1548C000
  set: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0 lid: 0x00000000,0x00000000
  dbwrid: 0 obj: 115205 objn: 115205 tsn: 8 afn: 8
  hash: [0x292CCE8C,0x292CCE8C] lru: [0x19FFB3BC,0x2930A050]
  obj-flags: object_ckpt_list
  ckptq: [0x1A3EA500,0x18FF0A90] fileq: [0x293178F0,0x19FFB398] objq: [0x2183AA3C,0x2183AA3C]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read gotten_in_current_mode
  LRBA: [0x627.e9dd.0] LSCN: [0x0.2d120c8] HSCN: [0x0.2d120c8] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
  buffer tsn: 8 rdba: 0x0201490a (8/84234)
  scn: 0x0000.02d120c8 seq: 0x03 flg: 0x02 tail: 0x20c80603
  frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
 

We notice the header has also been changed and now has the SCN from this transaction stamped in the header. Therefore any process needing this index block to be at a specific point in time (say a Select statement during a consistent read operation) can now quickly check the block header to see “when” it was last changed.
 
If the block has indeed changed since the (say) Select statement started, it need now only check the ITL slots to see which transaction has matching SCN details and so find the specific transaction that last changed the index block. Knowing the transaction in question, Oracle can then reference the Undo Block Address in the ITL entry and so determine the specific undo segment that contains details of the previous state of the block and rollback all changes made by this transaction and create a new consistent image of the block as it was prior to the transaction making the block changes. The Select statement can now check the previous SCN in the header and repeat the same process as necessary until the index block is at a point in time prior to the Select statement starting.
 
In short,  basically how consistent reads are implemented in Oracle.

Everything I’ve discussed so far in Parts I & II are just as applicable to tables (and other segments) as they are to indexes. In the next post, we’ll look at the next portion of an index block dump which is unique to index segments …