Posts Tagged ‘index tree dump’
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. :)