Index Block Dumps and Index Tree Dumps Part I: (Knock On Wood)

I thought before I jump into a topic that requires looking at a number of index block dumps, it might be worth briefly recapping how one goes about dumping index blocks in Oracle.
 
A block dump is simply a formatted representation of the contents of a particular Oracle database block.  Although I’ll be focusing specifically on index related blocks, any Oracle data block type can potentially be dumped and investigated.
 
The basic command to dump a specific block is:
 
ALTER SYSTEM DUMP DATAFILE 5 BLOCK 42;
 
where the block 42 in datafile 5 is dumped.
 
To dump a number of consecutive blocks with the one command you can also:
 
ALTER SYSTEM DUMP DATAFILE 5 BLOCK MIN 42 BLOCK MAX 50;
 
The resultant representation of the dumped block(s) are written to a trace file in the user_dump_dest directory.
 
Although these commands are not in the official Oracle documentation (the last time I had a real good look, it was only briefly mentioned in the Database Vault Administration Guide) and are not officially supported, there are enough references in Metalink/MOS and various writings for these commands to be widely known and used. I’ve been dumping the contents of Oracle blocks since the mid 1990’s and although they can sometimes take some time to decipher, I find them a vital source of information on determining how Oracle actually works under the covers.
 
From an index perspective, the question is how can one figure which specific blocks to dump for a given index. There are a couple of useful little tips. 

The first thing to point out with an index is that the critical Root Block of an index is always the block after the index segment header. This is always the case regardless of the database version, platform or type of tablespace. I’ve discussed how the index root block is always the block after the index segment header in this earlier post:
 
Therefore, to start exploring a specific index, we first find the root block details after the index segment header:

 
SQL> SELECT header_file, header_block+1 FROM dba_segments WHERE segment_name='BOWIE_I';
 
HEADER_FILE HEADER_BLOCK+1
----------- --------------
          7         219274

 
The following command will then dump the associated index root block:

 
SQL> ALTER SYSTEM DUMP DATAFILE 7 BLOCK 219274;
 
System altered.

As we’ll see in a later post, from the dump of the index root block, we can then find what other index blocks the root block points to and references.
 
However, another useful method of determining which index blocks might be worth dumping is to do a “treedump” of an index.
 
One first needs to find the object_id of the index in question:

 
SQL> SELECT object_id FROM dba_objects WHERE object_name = 'BOWIE_I';
 
 OBJECT_ID
----------
    106315

 
To then do a treedump of the index:

 
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 106315';
 
Session altered.

 
where level 106315 is the object_id of the index.
 

A partial listing from a treedump follows:
 
branch: 0x1c3588a 29579402 (0: nrow: 222, level: 1)
   leaf: 0x1c3588b 29579403 (-1: nrow: 485 rrow: 485)
   leaf: 0x1c3588c 29579404 (0: nrow: 479 rrow: 479)
   leaf: 0x1c3588d 29579405 (1: nrow: 479 rrow: 479)
   leaf: 0x1c3588e 29579406 (2: nrow: 479 rrow: 479)
   leaf: 0x1c3588f 29579407 (3: nrow: 479 rrow: 479)

…

A treedump simply lists each index block in the logical order of the index structure. Starting always with the index root block at the top, we notice that it’s simply listed as a branch (albeit a rather important one). The characters after the branch keyword represent a hex (0x1c3588a) and decimal (29579402) version of the Relative Block Address (RBA), which is used by Oracle to find the actual physical location of the block.  As there’s only ever the one root block, it starts from position 0, the nrow: 222 denotes the root block points to 222 distinct index blocks in the level below it and level 1 denotes this is a level 1 index (height 2) so the blocks below the root block must all be leaf blocks (there are no intermediate branch levels in this case).

The first leaf block listed is the first block being referenced within the parent root block and must therefore be the  “left-most” leaf block in the index structure. It has a RBA of hex (0x1c3588b) decimal(29579403), the –1 denotes it’s the first leaf block (as the counter starts at -1), the nrow: 485 denotes the leaf block has 485 index entries and the rrow: 485 denotes that 485 of the index entries are non-deleted entries (meaning there are no deleted index entries in this specific leaf block).

The next leaf block in the treedump corresponds to the second block (number 0) referenced in the root block and is the second left-hand most leaf block in the index structure, followed by its specific details. The third leaf block (number 1) in the treedump is the third leaf block in the index structure and so on for all 222 leaf blocks in the index (the last leaf block numbered 220).

The RBA of any of these blocks in the treedump can be then used to determine which block of interest to block dump. The DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE and DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK functions can be used to covert the RBA into the corresponding DATAFILE ID and BLOCK ID in which to dump the block.

For example, to determine the DATAFILE and BLOCK of the third leaf block in the index:

SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29579405),
  2         DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29579405)
  3  FROM dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29579405)
----------------------------------------------
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29579405)
-----------------------------------------------
                                             7
                                         219277

So now we can confirm the index block of interest here is specifically located at datafile 7, block 219277.

OK, we now have the necessary basics to start block dumping a few index blocks and having a look at what they might show us.