Simple formatting on index tree dump

I often need to do index block dump while troubleshooting index problem.

For instance, let’s assume that I want to take a dump for 3rd index leaf block. It’s a very tedious job and error-prone. So I made a simple but cute tool to make a life simple.

UKJA@ukja116> select * from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production          
PL/SQL Release 11.1.0.6.0 - Production                                          
CORE	11.1.0.6.0	Production                                                      
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production                         
NLSRTL Version 11.1.0.6.0 - Production                                          

Elapsed: 00:00:00.03
UKJA@ukja116> 
UKJA@ukja116> create table t1(c1 int, c2 char(100));

Table created.

Elapsed: 00:00:00.01
UKJA@ukja116> 
UKJA@ukja116> insert into t1
  2  select level, 'dummy' from dual
  3  connect by level  <= 1000
  4  ;

1000 rows created.

Elapsed: 00:00:00.01
UKJA@ukja116> 
UKJA@ukja116> create index t1_n1 on t1(c1, c2);

Index created.

Elapsed: 00:00:00.01
UKJA@ukja116> -- do index tree dump
UKJA@ukja116> exec tree_dump('t1_n1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

The result of index tree dump is as following.

----- begin tree dump
branch: 0x1c00af4 29362932 (0: nrow: 17, level: 1)
   leaf: 0x1c00af5 29362933 (-1: nrow: 62 rrow: 62)
   leaf: 0x1c00af6 29362934 (0: nrow: 62 rrow: 62)
   leaf: 0x1c00af7 29362935 (1: nrow: 61 rrow: 61)
   leaf: 0x1c00af8 29362936 (2: nrow: 61 rrow: 61)
   leaf: 0x2802ad9 41954009 (3: nrow: 61 rrow: 61)
   leaf: 0x2802ada 41954010 (4: nrow: 61 rrow: 61)
   leaf: 0x2802adb 41954011 (5: nrow: 61 rrow: 61)
   leaf: 0x2802adc 41954012 (6: nrow: 61 rrow: 61)
   leaf: 0x2802add 41954013 (7: nrow: 61 rrow: 61)
   leaf: 0x2802ade 41954014 (8: nrow: 61 rrow: 61)
   leaf: 0x2802adf 41954015 (9: nrow: 61 rrow: 61)
   leaf: 0x2802ae0 41954016 (10: nrow: 61 rrow: 61)
   leaf: 0x2c0268a 46147210 (11: nrow: 61 rrow: 61)
   leaf: 0x2c0268b 46147211 (12: nrow: 61 rrow: 61)
   leaf: 0x2c0268c 46147212 (13: nrow: 61 rrow: 61)
   leaf: 0x2c0268d 46147213 (14: nrow: 61 rrow: 61)
   leaf: 0x2c0268e 46147214 (15: nrow: 22 rrow: 22)
----- end tree dump

To take a dump for specific index block, you need to 1) copy & paste the DBA, 2) convert the block address to file# and block#, 3) type the “alter system dump …” command. I really hate these kinds of boring procedures.

This is how I make my life simpler and easier with litte automized script.

UKJA@ukja116> 
UKJA@ukja116> select
  2  	prefix||
  3  	type ||
  4  	' max_rows=' || nrow ||', '||
  5  	'cur_rows=' || rrow ||', '||
  6  	'dump=alter system dump datafile ' ||
  7  			dbms_utility.data_block_address_file(to_dec(dba)) || ' block ' ||
  8  			dbms_utility.data_block_address_block(to_dec(dba))
  9  from (
 10  	select
 11  		regexp_substr(column_value, '^[[:space:]]+') as prefix,
 12  		regexp_substr(column_value, '(branch|leaf)') as type,
 13  		regexp_replace(regexp_substr(column_value, '(branch:|leaf:) [^ ]+'),
 14  							'(branch:|leaf:) 0x', '') as dba,
 15  		substr(regexp_substr(column_value, 'nrow: [[:digit:]]+'), 7) as nrow,
 16  		substr(regexp_substr(column_value, 'rrow: [[:digit:]]+'), 7) as rrow
 17  	from table(get_trace_file1)
 18  	where regexp_like(column_value, '(branch:|leaf:)')
 19  )
 20  ;

PREFIX||TYPE||'MAX_ROWS='||NROW||','||'CUR_ROWS='||RROW||','||'DUMP=ALTERSYSTEMD
--------------------------------------------------------------------------------
branch max_rows=17, cur_rows=, dump=alter system dump datafile 7 block 2804     
   leaf max_rows=62, cur_rows=62, dump=alter system dump datafile 7 block 2805  
   leaf max_rows=62, cur_rows=62, dump=alter system dump datafile 7 block 2806  
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 7 block 2807  
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 7 block 2808  
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10969
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10970
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10971
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10972
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10973
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10974
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10975
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 10 block 10976
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 11 block 9866 
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 11 block 9867 
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 11 block 9868 
   leaf max_rows=61, cur_rows=61, dump=alter system dump datafile 11 block 9869 
   leaf max_rows=22, cur_rows=22, dump=alter system dump datafile 11 block 9870 

18 rows selected.

Elapsed: 00:00:00.06

Now I can just copy & paste the dump command to my SQL*Plus window and it’s done!

What I’ve done is making my life simple and beautiful. :)