Index Tree Dumpをもっと便利に使用しよう。

索引の性能問題を分析する時はいつもリーフブロックをダンプすべき必要性を感じます。例えば、索引の3番目のリーフブロックをダンプして、この内容を分析しようと思うと仮定してみましょう。

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

Index Tree Dumpの結果は次のようです。

----- 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

この情報をもとに特定のリーフブロックをダンプするためには、1）DBA(Data Block Address)をCopy&Pasteし、2）そのDBAをファイル番号とブロック番号で変化し、3）SQL*Plusで"alter system dump datafile f# block b#"命令文を実行しなければなりません。

このような退屈な作業は私が一番きらいなものです。いい知らせは、下のように自動化したスクリプトを使ってもう少し便利に作業をすることが可能だということです。

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

上の結果を使えば、ただダンプ命令文(alter system dump datafile 11 block 9870)をSQL*PlusにCopy&Pasteするだけで必要な作業が終わります。

簡単なトリックだけで退屈に見える作業が知能的で面白い作業に変わるいい例です。