jump to navigation

Index Block Dumps and Index Tree Dumps Part I: (Knock On Wood) February 8, 2010

Posted by Richard Foote in Block Dumps, Index Internals, Oracle Indexes, Tree Dumps.
trackback

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.

About these ads

Comments»

1. Argus - February 9, 2010

Completely off-topic. Nothing to do with this post, just with your alter ego. I saw this today: http://www.theonion.com/content/news/nasa_launches_david_bowie_concept
Maybe you can use it some day.
Feel free to delete this comment

Richard Foote - February 9, 2010

Hi Argus

LOL, very funny link.

Thank you :)

2. Jonathan Lewis - February 11, 2010

Richard,

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.

Regards
Jonathan Lewis

Richard Foote - February 16, 2010

Hi Jonathan

Yes indeed, freelist groups are an exception to the rule. Thanks for reminding me to avoid the word “always” as it’s always a dangerous word to use :)

3. Brian TKatch - February 17, 2010

Richard, this is great stuff.

4. Blogroll Report 05/02/2009 – 12/02/2010 « Coskan’s Approach to Oracle - March 10, 2010

[...] Richard Foote-Index Block Dumps and Index Tree Dumps Part I: (Knock On Wood) [...]

5. patrick huynh - March 12, 2010

Hi Richard,

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?

Regards,
Patrick Huynh

Richard Foote - March 15, 2010

Hi Patrick

Jonathan Lewis has recently posted a nice article on how to use a treedump as an external table and get a nice summary on how index entries are distributed:

http://jonathanlewis.wordpress.com/2010/03/07/treedump-2/

Rather than rebuild an index that has large areas of fragmented space, consider a coalesce instead. Do a search in this blog where I describe the differences between the two.

6. Index Block Dump: Block Header Part I (Editions Of You) « Richard Foote’s Oracle Blog - July 20, 2010

[...] Index Internals, Oracle Indexes. trackback I’ve previously looked at how to generate an Oracle block dump, time to now go into a little more [...]

7. What are the contents of the Headers in Control,Redo log and data file in a typical Oracle DB? - November 24, 2010

[...] DCookie This blog tells you how to dump an Oracle database [...]

8. Dax - September 28, 2011

Hi Richard

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?

SQL> drop table test;

Table dropped.

SQL> create table test (name varchar2(1000));

Table created.

SQL> create index ind_test on test (name) ;

Index created.

SQL> insert into test
2 select name from
3 (select substr(rpad(‘X’, 1000, ‘X’) || to_char(level), -1000) name, level l from dual connect by level
select length(name), substr(name, 995) from test; — to fit output here i just display last 5 character and rest 995 contains X

LENGTH(NAME) SUBSTR(NAME,995)
———— ————————
1000 XXXXX1
1000 XXXXX2
1000 XXXXX3
1000 XXXXX4
1000 XXXXX5

SQL> analyze index ind_test validate structure;

Index analyzed.

SQL> SELECT * from index_stats where name = ‘IND_TEST’;

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
———- ———- —————————— —————————— ———- ———- ———– ———- ———- ———- ———– ———- ———– ————— ————- —————– ———– ———- ———- ———— ——————– ———- ———— ————– —————-
1 8 IND_TEST 5 1 5065 8000 0 0 0 0 0 0 5 1 8000 5065 64 1 2 0 0 0 0

=== above output shows there is only one leaf block and it also confirm through treedump

SQL> select object_id, object_name from dba_objects where object_name = ‘IND_TEST’;

OBJECT_ID OBJECT_NAME
———- ——————————————————————————————————————————–
22855 IND_TEST

SQL> ALTER SESSION SET EVENTS ‘immediate trace name treedump level 22855′;

Session altered.

=== output from trace file

—– begin tree dump
leaf: 0x40b141 4239681 (0: nrow: 5 rrow: 5)
—– end tree dump

=== end of output

SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4239681 ),
2 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4239681 ) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4239681) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4239681)
——————————————— ———————————————-
1 45377

SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 45377;

System altered.

=== above command generate following in trace file

Start dump data blocks tsn: 0 file#:1 minblk 45377 maxblk 45377
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4239681
BH (0x1E3F7044) file#: 1 rdba: 0x0040b141 (1/45377) class: 1 ba: 0x1E32E000
set: 17 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 23037 objn: 22855 tsn: 0 afn: 1 hint: f
hash: [0x247F6E44,0x3EF13818] lru: [0x273F6E74,0x1E3F701C]
obj-flags: object_ckpt_list
ckptq: [0x3E9FFF94,0x1FFE69C0] fileq: [0x3E9FFFA8,0x273F6DD0] objq: [0x2D12AA40,0x2D12AA40] objaq: [0x2D12AA30,0x2D12AA30]
st: XCURRENT md: NULL tch: 5
flags: buffer_dirty redo_since_read
LRBA: [0x24.ed2.0] LSCN: [0x38.ebf3e52f] HSCN: [0x38.ebf3e52f] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x247F6DC8) file#: 1 rdba: 0x0040b141 (1/45377) class: 1 ba: 0×24728000
set: 20 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 22862 objn: 22855 tsn: 0 afn: 1 hint: f
hash: [0x207F0518,0x1E3F70C0] lru: [0x29BF3624,0x3E9FCBAC]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: FREE md: NULL tch: 0 lfb: 15
flags: foreground_waiting
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x207F049C) file#: 1 rdba: 0x0040b141 (1/45377) class: 1 ba: 0x2062A000
set: 22 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 22862 objn: 22855 tsn: 0 afn: 1 hint: f
hash: [0x253EDE2C,0x247F6E44] lru: [0x237F8678,0x23FF03A0]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 0 rdba: 0x0040b141 (1/45377)
scn: 0×0038.ebf3e516 seq: 0×01 flg: 0×04 tail: 0xe5160601
frmt: 0×02 chkval: 0xaf6a type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0EF80200 to 0x0EF82200
EF80200 0000A206 0040B141 EBF3E516 04010038 [....A.@.....8...]
EF80210 0000AF6A 00000002 000059FD EBF3E516 [j........Y......]
EF80220 00000038 00020002 00000000 00000000 [8...............]
EF80230 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
EF80250 00000000 00000000 00000000 02800000 [................]
EF80260 00000000 00240000 1F401F64 00000000 [......$.d.@.....]
EF80270 00000000 00000000 00000000 00001F64 [............d...]
EF80280 00001F60 00000000 00000000 00000000 [`...............]
EF80290 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
EF821F0 00000000 00000000 00000000 E5160601 [................]
Block header dump: 0x0040b141
Object id on Block? Y
seg/obj: 0x59fd csc: 0×38.ebf3e516 itc: 2 flg: – typ: 2 – INDEX
fsl: 0 fnx: 0×0 ver: 0×01

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
Leaf block dump
===============
header address 251134556=0xef8025c
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0×24
kdxcofeo 8036=0x1f64
kdxcoavs 8000
kdxlespl 0
kdxlende 0
kdxlenxt 0=0×0
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
*** dummy key ***
row#0[8032] flag: ——, lock: 0, len=4
col 0; len 0; (0):
col 1; len 0; (0):
—– end of leaf block dump —–
End dump data blocks tsn: 0 file#: 1 minblk 45377 maxblk 45377

=== this block does not shows any data here.. am I missing something here? Pl. help me to understnad this.

Richard Foote - September 28, 2011

Hi Dax

There are all sorts of little (and not so little) differences between database versions and platforms with regard to block dumps.

You likely need to flush the block to disk before performing the block dump in your case.

9. ezuall - October 5, 2011

Great post. Reading this again recently reminded me of a project I wanted to start to visualise index tree dumps. It’s a work in progress.

http://www.shockinggrey.com/?q=node/409

10. Construyendo un Arbol B+ (B+Tree) « El bienestar de la mayoría, supera al bienestar de la minoría. O de uno solo. - August 20, 2012
11. What are the contents of the Headers in Control,Redo log and data file in a typical Oracle DB? - Just just easy answers - September 7, 2013

[…] This blog tells you how to dump an Oracle database block. […]


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

Follow

Get every new post delivered to your Inbox.

Join 1,901 other followers

%d bloggers like this: