treedump

Jump to update for new format details

Here’s a mechanism for examining indexes in some detail if you think that something odd may be going on inside them. It’s a feature that I first decribed in Practical Oracle 8i, although the book doesn’t mention a problem with it that I subsequently discovered that means I always test it carefully on a small index before I use it on a large one.  Here’s a demonstration – cut from an SQL*Plus session on 10.2.0.3:

SQL> select object_id
  2  from user_objects
  3  where object_name = 'ORD_PLACED'
  4  ;

 OBJECT_ID
----------
    100466
1 row selected.

SQL> alter session set events 'immediate trace name treedump level 100466';
Session altered.

The treedump dumps a trace file with a summary of the index (or index partition) whose object_id (not data_object_id) you have specified in the level. In general you get one line in the trace file for each block in the index – but there are some versions of Oracle where you get a full symbolic dump for every leaf block, hence the need to check with a small index before trying it on a large one.

The dump will look something like the following (which is the start of a dump of the index sys.i_source1):

branch: 0x40035a 4195162 (0: nrow: 3, level: 2)
   branch: 0x40e2c0 4252352 (-1: nrow: 312, level: 1)
      leaf: 0x40035b 4195163 (-1: nrow: 240 rrow: 240)
      leaf: 0x40215f 4202847 (0: nrow: 218 rrow: 218)
      leaf: 0x40035c 4195164 (1: nrow: 444 rrow: 444)
      leaf: 0x40035d 4195165 (2: nrow: 444 rrow: 444)
       ... etc ...

To generate the dump, Oracle walks the tree structure in order one block at a time. The hexadecimal and decimal numbers starting each line are the block address expressed in two different ways.

The first line of the dump shows the root block. In this case the index has blevel=2 (which means a height of 3), so a walk down the index will visit the root, one level 1 branch, and then hit a leaf block. The root block currently has three pointers (nrow = 3) to branch blocks at level 1.

Note 1 – The root block is normally no different in format from a branch block, but when an index consists of nothing but the root block the root block is formatted as a leaf block not a branch block.

Note 2 – whatever block splits happen, the root block is always kept at the same location – the block after the segment header block and any freelist group blocks.

Indented one step (three characters) line 2 shows you the first branch block of the level below – the first of the level 1 branch blocks in this case. In brackets you see that this is entry -1, has 312  pointers to blocks at the next level and is a level 1 branch block. The significance of level 1 is that the entries will be pointing to leaf blocks.

It may seem a little odd that Oracle starts at the “-1th” branch block: computer programs usually start counting at zero or one, but Oracle has a rationale for starting at -1 in branch blocks. Most of the entries in a branch block are pairs of the form (partial key, block address) – and these are listed in the block’s row directory – but there is one special entry (called the “leftmost child – kdxbrlmc”) which omits the partial key and gets stored in a separate place outside the row directory. Oracle omits the key to save a little space and infers the value from the block’s parent (in the branch level above). Since the row directory starts counting at row 0, the leftmost child has to be labelled “-1”.

I haven’t shown it, but 312 lines further down the trace file, you’d find branch block zero, and the other two branch blocks would be a similar sort of distance further down again.

The third line of the trace file is the first leaf block (and again we start counting from -1). Note that there is no “level” in the leaf block. Instead you get two entries for rows: the number of current rows (rrow) in the block and the current size (nrow) of the block’srow directory (see glossary). The row directory may hold references to rows that have been deleted, committed, but not yet cleaned out of the structure, which is why the dump shows two pieces of information.

The part of the index shown in the example is completely clean and tidy (rrow = nrow in every block) but something a little odd has happened at the start of the index as we can see evidence of a 50/50 leaf block split at the low end due to inserts after the index was intially filled – note how the leaf block addresses are nearly in sequence (035b, 215f, 035c, 035d) but a completely different block number appears between the first and third blocks to hold the overflow as the first block split.

For comparison purposes, here’s a section of the index produced during one run of my “index explosion” code:

branch: 0x140118a 20976010 (0: nrow: 551, level: 1)
   leaf: 0x140118b 20976011 (-1: nrow: 409 rrow: 409)
   leaf: 0x140118c 20976012 (0: nrow: 214 rrow: 214)
   leaf: 0x140118d 20976013 (1: nrow: 214 rrow: 214)
   leaf: 0x140118e 20976014 (2: nrow: 213 rrow: 213)
   leaf: 0x1401190 20976016 (3: nrow: 206 rrow: 206)
   leaf: 0x140118f 20976015 (4: nrow: 327 rrow: 327)
   leaf: 0x1401191 20976017 (5: nrow: 169 rrow: 169)
   leaf: 0x1401192 20976018 (6: nrow: 169 rrow: 169)
   leaf: 0x1401194 20976020 (7: nrow: 159 rrow: 159)
   leaf: 0x1401193 20976019 (8: nrow: 169 rrow: 169)
   leaf: 0x1401196 20976022 (9: nrow: 160 rrow: 160)
   leaf: 0x1401195 20976021 (10: nrow: 327 rrow: 327)
    ...
   leaf: 0x140152d 20976941 (544: nrow: 141 rrow: 141)
   leaf: 0x140152c 20976940 (545: nrow: 146 rrow: 146)
   leaf: 0x140152f 20976943 (546: nrow: 140 rrow: 140)
   leaf: 0x140152e 20976942 (547: nrow: 282 rrow: 282)
   leaf: 0x1401530 20976944 (548: nrow: 282 rrow: 282)
   leaf: 0x1401531 20976945 (549: nrow: 138 rrow: 138)

We have an index with blevel = 1, and 551 leaf blocks (numbered -1 to 549).

The first leaf block is 100% packed and underwent a “90/10” split.

The next 4 leaf blocks show clear evidence of 50/50 splits (each holds roughly half of the maximum 409 rows in the first block).  The next leaf block suggests that a bit of a catastrophe hit the block and its ITL grew sharply – this block is 100% “full” (a fact that I ascertained by doing a block dump), but can only fit 327 entries because its ITL has grown to about 70 entries.

The size of the ITL doesn’t change over the next few blocks, but we can see a few 50/50 splits followed by a 90/10 split – and so it goes on. By the time we get to the end of the list, we’ve had another glitch, and a “full” block now holds only 282 rows, and blocks showing 50/50 splits hold about 140 rows. In this test run, the ITLs grew to a worst case of about 112 entries – not a complete disaster but certainly bad news.

The treedump isn’t detailed enough to tell you everything that’s happened to your index – but it is relatively short (one line per block) and gives you some good clues about what’s been going on. And the presence of the decimal form of the block address makes it easy to dump individual blocks if you want to investigate further, for example:

SQL> select
  2     dbms_utility.data_block_address_file(20976011) file#,
  3     dbms_utility.data_block_address_block(20976011) block#
  4  from
  5     dual
  6  /

     FILE#     BLOCK#
---------- ----------
         5       4491

1 row selected.

SQL> alter system dump datafile 5 block 4491;

System altered.

Footnote:

In some versions of Oracle you get a full block dump of every leaf block – which makes it very expensive to do a treedump.
In versions of Oracle prior to 9.2.0.6 there is a bug which means your session will crash with a “numeric overflow” error if you try to dump an index that has been created automatically to support a unique or primary key constraint. This makes it hard to dump index organized tables(IOTs). This is because bit 12 (4096) been set in ind$.property and the code isn’t expecting such a large number. (Technically it is possible to update the property column directly; it works, but I wouldn’t do it on the production system – but maybe on a backup you’re about to discard.)

Update Dec 2010

:
See this comment on a related post): Each line of a treedump takes about 75 bytes, so you need to be able to generate a tracefile of around (75 * leaf_blocks / 1048576)MB. Make sure you check parameter max_dump_file_size before you start.

Update Aug 2022

At some point between 11g and 19c the format of the tree dump changed to add a little extra information about the leaf blocks and re-arrange the way the “maximim/current” figures were displayed.

Old format

branch: 0x1400983 20973955 (0: nrow: 15, level: 2)
   branch: 0x1400c73 20974707 (-1: nrow: 238, level: 1)
      leaf: 0x1400984 20973956 (-1: nrow: 186 rrow: 47)
      leaf: 0x1401e6a 20979306 (0: nrow: 186 rrow: 43)
      leaf: 0x1401412 20976658 (1: nrow: 191 rrow: 53)

New format

branch: 0x9000104 150995204 (0: nrow: 15, level: 2)
   branch: 0x9000322 150995746 (-1: nrow: 238, level: 1)
      leaf: 0x900017f 150995327 (-1: row:186.47 avs:3904)
      leaf: 0x9001534 151000372 (0: row:186.43 avs:3904)
      leaf: 0x9000b6a 150997866 (1: row:191.53 avs:3794)

Note how the leaf blocks now show the available space (avs:), and the nrow: N rrow: M have been collapsed to just row: N.M

The figures came from a test script run against 11.2.0.4 and 19.11.0.0 that did a large “random insert” followed by a bulk delete – so the data processing seems to have behaved consistently across those two versions to produce the (logically) identical results.