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:

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 denotesthis 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:

I’m sure I’ve seen this in one of your presentations somewhere, but there is an exception to the “segment header block + 1″. If the index has been declared with multiple freelist groups (and I can’t remember the last time I saw one of those) they go between the segment header block and the root block.

Is this the only way to find out how many index entries in a block?
The reason i am asking this is to find out if i need to rebuild my index.

I have read your other post regarding rebuild indexes and in general we don’t need to do it, but my index is inserted using sequential number and the entries got deleted quite often. However, there are still a fair bit of old entries, so i guess those blocks will not be reused for the new entry as the number getting bigger ( please correct me if i am wrong).

By doing the treedump (or other methods) i can identify the index blocks that have many deleted entries and if there’s a large number of index blocks like that. Is it worthwhile to rebuild the index?

Thanks for this articles, I am trying to learn Index internals. I follow your stpes in my oracle database. I observed that in treedump it shows block XXX and as per index_stats it contains only one leaf block so I was expecting that block dump of XXX will show me my rows but it is not showing in that block. it must be some other leaf block. how it possible?