パフォーマンス劣化はインデックスのせいなのか!? をみっちり検証おら! オラ! 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です。

fig4 図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した後

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

  1. リーフブロック-1に2という値を格納
  2. 新規ブロック追加
  3. リーフブロック-1の一部をリーフブロック0に格納(約半分)

Copyright © ITmedia, Inc. All Rights Reserved.

スポンサーからのお知らせPR

注目のテーマ

AI for エンジニアリング
「サプライチェーン攻撃」対策
1P情シスのための脆弱性管理/対策の現実解
OSSのサプライチェーン管理、取るべきアクションとは
Microsoft & Windows最前線2024
システム開発ノウハウ 【発注ナビ】PR
あなたにおすすめの記事PR

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。