jump to navigation

Index Tree Dumps in Oracle 12c Database (New Age) June 22, 2015

Posted by Richard Foote in 12c, TreeDumps.
trackback

I’ve previously discussed Index Tree Dumps but I’ve recently found a nice little improvement that’s been introduced in Oracle Database 12c.

Let’s begin by creating a little table and index:

SQL> create table bowie (id number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create index bowie_id_i on bowie(id);

Index created.

To generate an Index Tree Dump, we first need the OBJECT_ID of the index:

SQL> select object_id from dba_objects where object_name='BOWIE_ID_I';

OBJECT_ID
----------
98829

And then use it to generate the Index Tree Dump:

SQL> alter session set events 'immediate trace name treedump level 98829';

Session altered.

Previously, an Index Tree Dump looked like the following:

—– begin tree dump
branch: 0x100023b 16777787 (0: nrow: 21, level: 1)
leaf: 0x100023c 16777788 (-1: nrow: 485 rrow: 485)
leaf: 0x100023d 16777789 (0: nrow: 479 rrow: 479)
leaf: 0x100023e 16777790 (1: nrow: 479 rrow: 479)
leaf: 0x100023f 16777791 (2: nrow: 479 rrow: 479)
leaf: 0x1000240 16777792 (3: nrow: 479 rrow: 479)
leaf: 0x1000241 16777793 (4: nrow: 479 rrow: 479)
leaf: 0x1000242 16777794 (5: nrow: 479 rrow: 479)
leaf: 0x1000243 16777795 (6: nrow: 479 rrow: 479)
leaf: 0x1000244 16777796 (7: nrow: 479 rrow: 479)
leaf: 0x1000245 16777797 (8: nrow: 479 rrow: 479)
leaf: 0x1000246 16777798 (9: nrow: 479 rrow: 479)
leaf: 0x1000247 16777799 (10: nrow: 479 rrow: 479)
leaf: 0x1000249 16777801 (11: nrow: 479 rrow: 479)
leaf: 0x100024a 16777802 (12: nrow: 479 rrow: 479)
leaf: 0x100024b 16777803 (13: nrow: 479 rrow: 479)
leaf: 0x100024c 16777804 (14: nrow: 479 rrow: 479)
leaf: 0x100024d 16777805 (15: nrow: 479 rrow: 479)
leaf: 0x100024e 16777806 (16: nrow: 479 rrow: 479)
leaf: 0x100024f 16777807 (17: nrow: 479 rrow: 479)
leaf: 0x1000250 16777808 (18: nrow: 479 rrow: 479)
leaf: 0x1000251 16777809 (19: nrow: 414 rrow: 414)
—– end tree dump

So this index is a Level 1 Index with a root block and 21 Leaf Blocks. The first entry always corresponds to the index root block and is followed by the 21 leaf blocks. Each leaf block entry details the relative block address, the sequence number, the number of index entries (nrow) and the number of non-deleted index entries (rrow).

If we look at the same Index Tree Dump in 12c (12.0.1.2):

branch: 0x180017b 25166203 (0: nrow: 21, level: 1)
leaf: 0x180017c 25166204 (-1: row:485.485 avs:828)
leaf: 0x180017d 25166205 (0: row:479.479 avs:820)
leaf: 0x180017e 25166206 (1: row:479.479 avs:820)
leaf: 0x180017f 25166207 (2: row:479.479 avs:820)
leaf: 0x18004c8 25167048 (3: row:479.479 avs:820)
leaf: 0x18004c9 25167049 (4: row:479.479 avs:819)
leaf: 0x18004ca 25167050 (5: row:479.479 avs:820)
leaf: 0x18004cb 25167051 (6: row:479.479 avs:820)
leaf: 0x18004cc 25167052 (7: row:479.479 avs:820)
leaf: 0x18004cd 25167053 (8: row:479.479 avs:819)
leaf: 0x18004ce 25167054 (9: row:479.479 avs:820)
leaf: 0x18004cf 25167055 (10: row:479.479 avs:820)
leaf: 0x18004d1 25167057 (11: row:479.479 avs:820)
leaf: 0x18004d2 25167058 (12: row:479.479 avs:820)
leaf: 0x18004d3 25167059 (13: row:479.479 avs:819)
leaf: 0x18004d4 25167060 (14: row:479.479 avs:820)
leaf: 0x18004d5 25167061 (15: row:479.479 avs:820)
leaf: 0x18004d6 25167062 (16: row:479.479 avs:820)
leaf: 0x18004d7 25167063 (17: row:479.479 avs:820)
leaf: 0x18004d8 25167064 (18: row:479.479 avs:819)
leaf: 0x18004d9 25167065 (19: row:414.414 avs:1795)
—– end tree dump

We notice the format is a little different in that it also now includes the avs (free space) within the leaf block as well.

If we now delete a few rows (and hence index entries) and look at the updated tree dump:

SQL> delete bowie where id between 1 and 400;

400 rows deleted.

SQL> commit;

Commit complete.

SQL> alter session set events 'immediate trace name treedump level 98829';

Session altered.

branch: 0x180017b 25166203 (0: nrow: 21, level: 1)
leaf: 0x180017c 25166204 (-1: row:485.85 avs:828)
leaf: 0x180017d 25166205 (0: row:479.479 avs:820)
leaf: 0x180017e 25166206 (1: row:479.479 avs:820)
leaf: 0x180017f 25166207 (2: row:479.479 avs:820)
leaf: 0x18004c8 25167048 (3: row:479.479 avs:820)
leaf: 0x18004c9 25167049 (4: row:479.479 avs:819)
leaf: 0x18004ca 25167050 (5: row:479.479 avs:820)
leaf: 0x18004cb 25167051 (6: row:479.479 avs:820)
leaf: 0x18004cc 25167052 (7: row:479.479 avs:820)
leaf: 0x18004cd 25167053 (8: row:479.479 avs:819)
leaf: 0x18004ce 25167054 (9: row:479.479 avs:820)
leaf: 0x18004cf 25167055 (10: row:479.479 avs:820)
leaf: 0x18004d1 25167057 (11: row:479.479 avs:820)
leaf: 0x18004d2 25167058 (12: row:479.479 avs:820)
leaf: 0x18004d3 25167059 (13: row:479.479 avs:819)
leaf: 0x18004d4 25167060 (14: row:479.479 avs:820)
leaf: 0x18004d5 25167061 (15: row:479.479 avs:820)
leaf: 0x18004d6 25167062 (16: row:479.479 avs:820)
leaf: 0x18004d7 25167063 (17: row:479.479 avs:820)
leaf: 0x18004d8 25167064 (18: row:479.479 avs:819)
leaf: 0x18004d9 25167065 (19: row:414.414 avs:1795)
—– end tree dump

We notice that it now correctly details how many non-deleted index entries we now have in the first leaf block (85). Unfortunately, the free space remains the same and doesn’t take into account the deleted index entries (still recorded as 828 bytes).

Of course, if we perform any additional DML that impacts this leaf block such as another delete:

SQL> delete bowie where id=401;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter session set events 'immediate trace name treedump level 98829';

Session altered.

—– begin tree dump
branch: 0x180017b 25166203 (0: nrow: 21, level: 1)
leaf: 0x180017c 25166204 (-1: row:85.84 avs:6725)
leaf: 0x180017d 25166205 (0: row:479.479 avs:820)
leaf: 0x180017e 25166206 (1: row:479.479 avs:820)
leaf: 0x180017f 25166207 (2: row:479.479 avs:820)
leaf: 0x18004c8 25167048 (3: row:479.479 avs:820)
leaf: 0x18004c9 25167049 (4: row:479.479 avs:819)
leaf: 0x18004ca 25167050 (5: row:479.479 avs:820)
leaf: 0x18004cb 25167051 (6: row:479.479 avs:820)
leaf: 0x18004cc 25167052 (7: row:479.479 avs:820)
leaf: 0x18004cd 25167053 (8: row:479.479 avs:819)
leaf: 0x18004ce 25167054 (9: row:479.479 avs:820)
leaf: 0x18004cf 25167055 (10: row:479.479 avs:820)
leaf: 0x18004d1 25167057 (11: row:479.479 avs:820)
leaf: 0x18004d2 25167058 (12: row:479.479 avs:820)
leaf: 0x18004d3 25167059 (13: row:479.479 avs:819)
leaf: 0x18004d4 25167060 (14: row:479.479 avs:820)
leaf: 0x18004d5 25167061 (15: row:479.479 avs:820)
leaf: 0x18004d6 25167062 (16: row:479.479 avs:820)
leaf: 0x18004d7 25167063 (17: row:479.479 avs:820)
leaf: 0x18004d8 25167064 (18: row:479.479 avs:819)
leaf: 0x18004d9 25167065 (19: row:414.414 avs:1795)
—– end tree dump

We notice the leaf block has now cleaned out the previously deleted index entries and the free space has been updated accordingly (now 6725 bytes).

Showing the amount of free space within a block is a nice little improvement to the format of the index tree dump.

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: