パフォーマンス劣化はインデックスのせいなのか!? をみっちり検証：おら! オラ! Oracle再検証＠IT出張所（1）（3/4 ページ）

» 2009年04月07日 00時00分公開

[内山義夫，株式会社インサイトテクノロジー]

検証

　まずは、今回の確認作業のためのテーブルとインデックスを作成します。

SQL> CREATE TABLE TEST_LEAF(ID NUMBER,NAME VARCHAR2(10));  //テーブルの作成
Table created.
SQL> CREATE INDEX IDX_TEST_LEAF ON TEST_LEAF(ID);  //インデックスの作成
Index created.
SQL> BEGIN  //FOR文を使って値を追加していくPL/SQL
  2     FOR i IN 1..10000 LOOP
  3        INSERT INTO TEST_LEAF VALUES(i,'TEST'||TO_CHAR(i));
  4     END LOOP;
  5     COMMIT;
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM TEST_LEAF;  //リーフブロックの中身を確認
  COUNT(*)
----------
     10000

テーブルとインデックスの作成

　TEST_LEAFというテーブルに10000件のデータが格納されました。インデックスはIDの項目に作成されています。

　値を挿入する前に、作成されたインデックスにデータがどのように格納されているかのぞいてみることにしましょう。

　インデックスの中をのぞくには、以下コマンドを使用します。

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL XXX';

　「LEVEL XXX」には取得したいインデックスのオブジェクトIDを入れて実行します。

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'IDX_TEST_LEAF';  
 OBJECT_ID
----------
     70389
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 70389';
Session altered.

インデックスの中身を確認するための書式（トレースファイルへの出力）

　これで、インデックスの中身がトレースファイルに出力されました。トレースファイルは初期化パラメータのdiagnostic_dest（11g以前ではuser_dump_dest）に指定された場所に作成されます。

001：Trace file /opt/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_6379.trc
001：Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
002：With the Partitioning, OLAP, Data Mining and Real Application Testing options
003：ORACLE_HOME = /opt/app/oracle/product/11.1.0
004：System name:    Linux
005：Node name:      localhost.localdomain
006：Release:        2.6.9-67.ELsmp
007：Version:        #1 SMP Wed Nov 7 13:58:04 EST 2007
008：Machine:        i686
009：Instance name: ora11g
010：Redo thread mounted by this instance: 1
011：Oracle process number: 18
012：Unix process pid: 6379, image: oracle@localhost.localdomain (TNS V1-V3)
013：
014：
015：*** 2009-02-12 08:22:18.436
016：*** SESSION ID:(170.7) 2009-02-12 08:22:18.436
017：*** CLIENT ID:() 2009-02-12 08:22:18.436
018：*** SERVICE NAME:(SYS$USERS) 2009-02-12 08:22:18.436
019：*** MODULE NAME:(SQL*Plus) 2009-02-12 08:22:18.436
020：*** ACTION NAME:() 2009-02-12 08:22:18.436
021：
022：----- begin tree dump  //これ以降が実際のインデックスファイルの中身
023：branch: 0x100d285 16831109 (0: nrow: 82, level: 1)  //ブランチ情報
024：   leaf: 0x100d293 16831123 (-1: nrow: 130 rrow: 130)  //ここからリーフブロックの情報
025：   leaf: 0x100d28d 16831117 (0: nrow: 123 rrow: 123)
026：   leaf: 0x100d28e 16831118 (1: nrow: 123 rrow: 123)
027：   leaf: 0x100d28f 16831119 (2: nrow: 123 rrow: 123)
028：   leaf: 0x100d290 16831120 (3: nrow: 123 rrow: 123)
029：   leaf: 0x100d286 16831110 (4: nrow: 123 rrow: 123)
030：   leaf: 0x100d287 16831111 (5: nrow: 123 rrow: 123)
031：   leaf: 0x100d288 16831112 (6: nrow: 123 rrow: 123)
032：   leaf: 0x100d289 16831113 (7: nrow: 123 rrow: 123)
033：   leaf: 0x100d28b 16831115 (8: nrow: 123 rrow: 123)
034：   leaf: 0x100d28c 16831116 (9: nrow: 123 rrow: 123)
035：   leaf: 0x100d28a 16831114 (10: nrow: 123 rrow: 123)
036：   leaf: 0x100d294 16831124 (11: nrow: 123 rrow: 123)
037：   leaf: 0x100d295 16831125 (12: nrow: 123 rrow: 123)
038：   leaf: 0x100d296 16831126 (13: nrow: 123 rrow: 123)
039：   leaf: 0x100d297 16831127 (14: nrow: 123 rrow: 123)
040：   leaf: 0x100d298 16831128 (15: nrow: 123 rrow: 123)
041：   leaf: 0x100d299 16831129 (16: nrow: 123 rrow: 123)
042：   leaf: 0x100d29a 16831130 (17: nrow: 123 rrow: 123)
043：   leaf: 0x100d29b 16831131 (18: nrow: 123 rrow: 123)
044：   leaf: 0x100d29c 16831132 (19: nrow: 123 rrow: 123)
045：   leaf: 0x100d29d 16831133 (20: nrow: 123 rrow: 123)
046：   leaf: 0x100d29e 16831134 (21: nrow: 123 rrow: 123)
047：   leaf: 0x100d29f 16831135 (22: nrow: 123 rrow: 123)
048：   leaf: 0x100d2a0 16831136 (23: nrow: 123 rrow: 123)
049：   leaf: 0x100d291 16831121 (24: nrow: 123 rrow: 123)
050：   leaf: 0x100d292 16831122 (25: nrow: 123 rrow: 123)
051：   leaf: 0x100d2d3 16831187 (26: nrow: 123 rrow: 123)
052：   leaf: 0x100d2c7 16831175 (27: nrow: 123 rrow: 123)
053：   leaf: 0x100d2c8 16831176 (28: nrow: 123 rrow: 123)
054：   leaf: 0x100d2c9 16831177 (29: nrow: 123 rrow: 123)
055：   leaf: 0x100d2ca 16831178 (30: nrow: 123 rrow: 123)
056：   leaf: 0x100d2cb 16831179 (31: nrow: 123 rrow: 123)
057：   leaf: 0x100d2cc 16831180 (32: nrow: 123 rrow: 123)
058：   leaf: 0x100d2cd 16831181 (33: nrow: 123 rrow: 123)
059：   leaf: 0x100d2ce 16831182 (34: nrow: 123 rrow: 123)
060：   leaf: 0x100d2cf 16831183 (35: nrow: 123 rrow: 123)
061：   leaf: 0x100d2d0 16831184 (36: nrow: 123 rrow: 123)
062：   leaf: 0x100d2c3 16831171 (37: nrow: 123 rrow: 123)
063：   leaf: 0x100d2c4 16831172 (38: nrow: 123 rrow: 123)
064：   leaf: 0x100d2c5 16831173 (39: nrow: 123 rrow: 123)
065：   leaf: 0x100d2c6 16831174 (40: nrow: 123 rrow: 123)
066：   leaf: 0x100d2d4 16831188 (41: nrow: 123 rrow: 123)
067：   leaf: 0x100d2d5 16831189 (42: nrow: 123 rrow: 123)
068：   leaf: 0x100d2d6 16831190 (43: nrow: 123 rrow: 123)
069：   leaf: 0x100d2d7 16831191 (44: nrow: 123 rrow: 123)
070：   leaf: 0x100d2d8 16831192 (45: nrow: 123 rrow: 123)
071：   leaf: 0x100d2d9 16831193 (46: nrow: 123 rrow: 123)
072：   leaf: 0x100d2da 16831194 (47: nrow: 123 rrow: 123)
073：   leaf: 0x100d2db 16831195 (48: nrow: 123 rrow: 123)
074：   leaf: 0x100d2dc 16831196 (49: nrow: 123 rrow: 123)
075：   leaf: 0x100d2dd 16831197 (50: nrow: 123 rrow: 123)
076：   leaf: 0x100d2de 16831198 (51: nrow: 123 rrow: 123)
077：   leaf: 0x100d2df 16831199 (52: nrow: 123 rrow: 123)
078：   leaf: 0x100d2e0 16831200 (53: nrow: 123 rrow: 123)
079：   leaf: 0x100d2d1 16831185 (54: nrow: 123 rrow: 123)
080：   leaf: 0x100d2d2 16831186 (55: nrow: 123 rrow: 123)
081：   leaf: 0x100d313 16831251 (56: nrow: 123 rrow: 123)
082：   leaf: 0x100d307 16831239 (57: nrow: 123 rrow: 123)
083：   leaf: 0x100d308 16831240 (58: nrow: 123 rrow: 123)
084：   leaf: 0x100d309 16831241 (59: nrow: 123 rrow: 123)
085：   leaf: 0x100d30a 16831242 (60: nrow: 123 rrow: 123)
086：   leaf: 0x100d30b 16831243 (61: nrow: 123 rrow: 123)
087：   leaf: 0x100d30c 16831244 (62: nrow: 123 rrow: 123)
088：   leaf: 0x100d30d 16831245 (63: nrow: 123 rrow: 123)
089：   leaf: 0x100d30e 16831246 (64: nrow: 123 rrow: 123)
090：   leaf: 0x100d30f 16831247 (65: nrow: 123 rrow: 123)
091：   leaf: 0x100d310 16831248 (66: nrow: 123 rrow: 123)
092：   leaf: 0x100d303 16831235 (67: nrow: 123 rrow: 123)
093：   leaf: 0x100d304 16831236 (68: nrow: 123 rrow: 123)
094：   leaf: 0x100d305 16831237 (69: nrow: 123 rrow: 123)
095：   leaf: 0x100d306 16831238 (70: nrow: 123 rrow: 123)
096：   leaf: 0x100d314 16831252 (71: nrow: 123 rrow: 123)
097：   leaf: 0x100d315 16831253 (72: nrow: 123 rrow: 123)
098：   leaf: 0x100d316 16831254 (73: nrow: 123 rrow: 123)
099：   leaf: 0x100d317 16831255 (74: nrow: 123 rrow: 123)
100：   leaf: 0x100d318 16831256 (75: nrow: 123 rrow: 123)
101：   leaf: 0x100d319 16831257 (76: nrow: 123 rrow: 123)
102：   leaf: 0x100d31a 16831258 (77: nrow: 123 rrow: 123)
103：   leaf: 0x100d31b 16831259 (78: nrow: 123 rrow: 123)
104：   leaf: 0x100d31c 16831260 (79: nrow: 123 rrow: 123)
105：   leaf: 0x100d31d 16831261 (80: nrow: 30 rrow: 30)
106：----- end tree dump

出力されたトレースファイルの内容を確認　[全文を別ページで開く]

　begin tree dumpとend tree dumpの間にインデックスのデータに関する情報が出力されています。

　1行目はブランチに関する情報で、2行目から最終行まではリーフブロックに関する情報になります。

　1行目の0x100d285 16831109はブロックのアドレスで、それぞれ16進と10進で記載されています。括弧内の0はブランチのレベルごとに連番にふられる番号で、nrowがブランチに格納されている行数、levelはブランチのレベルを表しています。

　2行目以降ではlevelがrrowに代わっていますが、このrrowは実際にブロックに格納されている行数を表しています。このnrowとrrowは、nrowが箱でrrowは中身のようなもので、リーフブロック-1には130行分入る箱の中に130行格納されていることを表しています。ちなみに、リーフブロック-1だけ少し多く格納されていますが、これは、リーフブロック-1にサイズの小さい値が格納されているためです。

　今回のIDX_TEST_LEAFインデックス内部を図にしたものが図4です。

図4　IDX_TEST_LEAFインデックスの内部

値をINSERTして確認する

　それでは、実際に2の値をINSERTして再度DUMPを取得してみましょう。インデックスはどのように変わるのでしょうか？

SQL> INSERT INTO TEST_LEAF VALUES(2,'TEST'||TO_CHAR(2));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 70389';
Session altered.

*** 2009-02-12 08:24:11.439
KGX cleanup...
KGX Atomic Operation Log 0x2b5b83b4
 Mutex 0x29aa2d08(170, 0) idn 2fde77c3 oper EXAM
 Cursor Parent uid 170 efd 5 whr 26 slp 0
 oper=OPERATION_DEFAULT pt1=(nil) pt2=(nil) pt3=(nil)
 pt4=(nil) u41=0 stt=0
*** 2009-02-12 08:24:22.570
----- begin tree dump
branch: 0x100d285 16831109 (0: nrow: 83, level: 1)
   leaf: 0x100d293 16831123 (-1: nrow: 71 rrow: 71)
   leaf: 0x100d31e 16831262 (0: nrow: 60 rrow: 60)
   leaf: 0x100d28d 16831117 (1: nrow: 123 rrow: 123)
   leaf: 0x100d28e 16831118 (2: nrow: 123 rrow: 123)
   leaf: 0x100d28f 16831119 (3: nrow: 123 rrow: 123)
   leaf: 0x100d290 16831120 (4: nrow: 123 rrow: 123)
   leaf: 0x100d286 16831110 (5: nrow: 123 rrow: 123)
   leaf: 0x100d287 16831111 (6: nrow: 123 rrow: 123)
   leaf: 0x100d288 16831112 (7: nrow: 123 rrow: 123)
   leaf: 0x100d289 16831113 (8: nrow: 123 rrow: 123)
   leaf: 0x100d28b 16831115 (9: nrow: 123 rrow: 123)
   leaf: 0x100d28c 16831116 (10: nrow: 123 rrow: 123)
   leaf: 0x100d28a 16831114 (11: nrow: 123 rrow: 123)
   leaf: 0x100d294 16831124 (12: nrow: 123 rrow: 123)
   leaf: 0x100d295 16831125 (13: nrow: 123 rrow: 123)
   leaf: 0x100d296 16831126 (14: nrow: 123 rrow: 123)
   leaf: 0x100d297 16831127 (15: nrow: 123 rrow: 123)
   leaf: 0x100d298 16831128 (16: nrow: 123 rrow: 123)
   leaf: 0x100d299 16831129 (17: nrow: 123 rrow: 123)
   leaf: 0x100d29a 16831130 (18: nrow: 123 rrow: 123)
   leaf: 0x100d29b 16831131 (19: nrow: 123 rrow: 123)
   leaf: 0x100d29c 16831132 (20: nrow: 123 rrow: 123)
   leaf: 0x100d29d 16831133 (21: nrow: 123 rrow: 123)
   leaf: 0x100d29e 16831134 (22: nrow: 123 rrow: 123)
   leaf: 0x100d29f 16831135 (23: nrow: 123 rrow: 123)
   leaf: 0x100d2a0 16831136 (24: nrow: 123 rrow: 123)
   leaf: 0x100d291 16831121 (25: nrow: 123 rrow: 123)
   leaf: 0x100d292 16831122 (26: nrow: 123 rrow: 123)
   leaf: 0x100d2d3 16831187 (27: nrow: 123 rrow: 123)
   leaf: 0x100d2c7 16831175 (28: nrow: 123 rrow: 123)
   leaf: 0x100d2c8 16831176 (29: nrow: 123 rrow: 123)
   leaf: 0x100d2c9 16831177 (30: nrow: 123 rrow: 123)
   leaf: 0x100d2ca 16831178 (31: nrow: 123 rrow: 123)
   leaf: 0x100d2cb 16831179 (32: nrow: 123 rrow: 123)
   leaf: 0x100d2cc 16831180 (33: nrow: 123 rrow: 123)
   leaf: 0x100d2cd 16831181 (34: nrow: 123 rrow: 123)
   leaf: 0x100d2ce 16831182 (35: nrow: 123 rrow: 123)
   leaf: 0x100d2cf 16831183 (36: nrow: 123 rrow: 123)
   leaf: 0x100d2d0 16831184 (37: nrow: 123 rrow: 123)
   leaf: 0x100d2c3 16831171 (38: nrow: 123 rrow: 123)
   leaf: 0x100d2c4 16831172 (39: nrow: 123 rrow: 123)
   leaf: 0x100d2c5 16831173 (40: nrow: 123 rrow: 123)
   leaf: 0x100d2c6 16831174 (41: nrow: 123 rrow: 123)
   leaf: 0x100d2d4 16831188 (42: nrow: 123 rrow: 123)
   leaf: 0x100d2d5 16831189 (43: nrow: 123 rrow: 123)
   leaf: 0x100d2d6 16831190 (44: nrow: 123 rrow: 123)
   leaf: 0x100d2d7 16831191 (45: nrow: 123 rrow: 123)
   leaf: 0x100d2d8 16831192 (46: nrow: 123 rrow: 123)
   leaf: 0x100d2d9 16831193 (47: nrow: 123 rrow: 123)
   leaf: 0x100d2da 16831194 (48: nrow: 123 rrow: 123)
   leaf: 0x100d2db 16831195 (49: nrow: 123 rrow: 123)
   leaf: 0x100d2dc 16831196 (50: nrow: 123 rrow: 123)
   leaf: 0x100d2dd 16831197 (51: nrow: 123 rrow: 123)
   leaf: 0x100d2de 16831198 (52: nrow: 123 rrow: 123)
   leaf: 0x100d2df 16831199 (53: nrow: 123 rrow: 123)
   leaf: 0x100d2e0 16831200 (54: nrow: 123 rrow: 123)
   leaf: 0x100d2d1 16831185 (55: nrow: 123 rrow: 123)
   leaf: 0x100d2d2 16831186 (56: nrow: 123 rrow: 123)
   leaf: 0x100d313 16831251 (57: nrow: 123 rrow: 123)
   leaf: 0x100d307 16831239 (58: nrow: 123 rrow: 123)
   leaf: 0x100d308 16831240 (59: nrow: 123 rrow: 123)
   leaf: 0x100d309 16831241 (60: nrow: 123 rrow: 123)
   leaf: 0x100d30a 16831242 (61: nrow: 123 rrow: 123)
   leaf: 0x100d30b 16831243 (62: nrow: 123 rrow: 123)
   leaf: 0x100d30c 16831244 (63: nrow: 123 rrow: 123)
   leaf: 0x100d30d 16831245 (64: nrow: 123 rrow: 123)
   leaf: 0x100d30e 16831246 (65: nrow: 123 rrow: 123)
   leaf: 0x100d30f 16831247 (66: nrow: 123 rrow: 123)
   leaf: 0x100d310 16831248 (67: nrow: 123 rrow: 123)
   leaf: 0x100d303 16831235 (68: nrow: 123 rrow: 123)
   leaf: 0x100d304 16831236 (69: nrow: 123 rrow: 123)
   leaf: 0x100d305 16831237 (70: nrow: 123 rrow: 123)
   leaf: 0x100d306 16831238 (71: nrow: 123 rrow: 123)
   leaf: 0x100d314 16831252 (72: nrow: 123 rrow: 123)
   leaf: 0x100d315 16831253 (73: nrow: 123 rrow: 123)
   leaf: 0x100d316 16831254 (74: nrow: 123 rrow: 123)
   leaf: 0x100d317 16831255 (75: nrow: 123 rrow: 123)
   leaf: 0x100d318 16831256 (76: nrow: 123 rrow: 123)
   leaf: 0x100d319 16831257 (77: nrow: 123 rrow: 123)
   leaf: 0x100d31a 16831258 (78: nrow: 123 rrow: 123)
   leaf: 0x100d31b 16831259 (79: nrow: 123 rrow: 123)
   leaf: 0x100d31c 16831260 (80: nrow: 123 rrow: 123)
   leaf: 0x100d31d 16831261 (81: nrow: 30 rrow: 30)
----- end tree dump

2をINSERTして再DUMPする　[全文を別ページで開く]

　先ほどのトレースファイルに追加される形でインデックスの情報が出力されます。

　これを見ると、リーフブロック-1の後ろに新たなブロックが挿入されていることが分かります。

----- begin tree dump
branch: 0x100d285 16831109 (0: nrow: 82, level: 1)
   leaf: 0x100d293 16831123 (-1: nrow: 130 rrow: 130)
   leaf: 0x100d28d 16831117 (0: nrow: 123 rrow: 123)
   leaf: 0x100d28e 16831118 (1: nrow: 123 rrow: 123)
             ::         ::
                  <略>
             ::         ::

2の値をINSERTする前

----- begin tree dump
branch: 0x100d285 16831109 (0: nrow: 83, level: 1)
   leaf: 0x100d293 16831123 (-1: nrow: 71 rrow: 71)
   leaf: 0x100d31e 16831262 (0: nrow: 60 rrow: 60)　 ←新しいブロックが挿入 
   leaf: 0x100d28d 16831117 (1: nrow: 123 rrow: 123)
   leaf: 0x100d28e 16831118 (2: nrow: 123 rrow: 123)
             ::         ::
                  <略>
             ::         ::

2の値をINSERTした後

　つまり、以下のような流れになっている可能性が高そうです。