続 X$BH に関する検証 その４
＜続 X$BH に関する検証 ～その４～＞
ペンネーム ちょびひげ
今回は前回予定していた内容を変更して、前回の検証で謎として残っている
なぜ全件検索でのUPDATE処理の場合だけにCRブロックが作成されるのか？
という疑問に関してもう少し詳しく検証して行きたい。
前回はCRブロックを参照する必要があるのではなく、UNDO情報を作成する為
にCRブロックが必要になるのではないか？と推測した。そこで、実際にそれ
ぞれの処理で作成されるUNDOエントリの量を調べたところ驚くべき結果が出
たので見て頂きたい。
前提条件は以下の通りである。
*************************************************************
◆環境
Linux 2.4.2-2
Oracle9i EE Release 9.2.0.1.0
◆テーブル構成
SQL> desc test Name Type --------- ------------------ ID1 NUMBER ID2 NUMBER TEXT VARCHAR2(2000)
※1 ID1にINDEX（TEST_IDX）を付与
※2 テーブルには10000(1万)件のデータが入っている
*************************************************************
まずは、全件検索でのUPDATE処理時に作成されるUNDOエントリの数である。
どれだけのUNDOエントリが作成されるのかはv$taransaction表で確認するこ
とが出来る。
*************************************************************
【全件検索での更新処理】
※ヒント句を入れて全件検索を行わせている
SQL> update /*+ FULL(test) */ test set id1=1 where id1 > 0; 10000行が更新されました。
【作成されたUNDOエントリを確認】
SQL> select USED_UBLK ,USED_UREC
from v$transaction;
XIDUSN USED_UBLK USED_UREC
---------- ---------- ----------
8 329 30011 ← これがUNDOエントリの数
カラム 説明 ---------- ------------------ XIDUSN UNDOセグメント番号 USED_UBLK 使用ブロック数 USED_UREC UNDOエントリの数
結果を見ると、約3万件のUNDOエントリを作成している。テーブルの更新前情
報、インデックスの更新前情報、インデックスの更新後情報で3件なので、
10000行 * 3件 = 30000 （UNDOエントリ）
で妥当な数字であろう。
では次にインデックス検索での更新を行ってみる。
*************************************************************
【インデックス検索での更新処理】
※id1にあるインデックスが使用されている
SQL> update test set id1=1 where id1>0;
10000行が更新されました。
XIDUSN UBAFIL UBABLK USED_UBLK USED_UREC
---------- ---------- ---------- ---------- ----------
3 2 3834 154 10148 ← 注目！
！！！なんとUNDOエントリの件数が3分の1の約1万件に減っている。使用した
UNDOブロック数も約半分である。
そこで、実際にUNDOブロックのダンプより、UNDOエントリの情報を見たとこ
ろ以下のような決定的な違いが見られた。
※UNDOブロックのダンプに関しての詳細はバックナンバー＜ロールバックセ
グメントに関する検証＞でおこなっているが、今回の検証でも詳しく見て
いく予定である。
*************************************************************
＜＜レコードの更新前の情報（一部省略）＞＞
*----------------------------- * Rec #0x10 slt: 0x15 objn: 33468(0x000082bc) objd: 33498 tblspc: 0(0x00000000) KDO undo record: col 0: [ 3] c2 64 64
＜＜インデックスの更新前の情報（一部省略）＞＞
*----------------------------- * Rec #0x11 slt: 0x15 objn: 33507(0x000082e3) objd: 33507 tblspc: 0(0x00000000) index undo for leaf key operations restore leaf row (clear leaf delete flags) key :(11): 03 c2 64 64 06 00 40 f9 73 00 61
＜＜インデックスの更新後の情報（一部省略）＞＞
*----------------------------- * Rec #0x12 slt: 0x15 objn: 33507(0x000082e3) objd: 33507 tblspc: 0(0x00000000) index undo for leaf key operations purge leaf row key :(10): 02 c1 02 06 00 40 f9 73 00 61
この繰り返し
*************************************************************
以下の3つのエントリが1万回繰り返され、ちょうど3万件となる。
[テーブルの更新前のUNDOエントリ] ←----
↓ |
[インデックスの更新前のUNDOエントリ] | （1万回LOOP）
↓ |
[インデックスの更新後のUNDOエントリ] →
インデックス検索での更新の場合のUNDOエントリは以下の通りである。
*************************************************************
＜＜テーブルの更新前のUNDOエントリが1万件＞＞
＜＜インデックスの更新前のUNDOエントリ 約70件＞＞
＜＜インデックスの更新後のUNDOエントリ 約70件＞＞
*************************************************************
【インデックスの更新前のUNDOエントリの一部を抜粋】
複数（この場合は255）のキーが1エントリに入っている
*----------------------------- * Rec #0x2 slt: 0x17 objn: 33507(0x000082e3) objd: 33507 tblspc: 0(0x00000000) * Layer: 10 (Index) opc: 22 rci 0x01 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- index undo for leaf key operations restore leaf row (clear leaf delete flags) ←リーフのDELETEフラグを元に戻す number of keys: 255 ←一括更新のキーの数 key sizes: 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 key :(2802): 03 c2 43 39 06 00 40 f9 5a 00 bc 03 c2 43 3a 06 00 40 f9 5a 00 bd 03 c2 43 ・ ・
【インデックスの更新後のUNDOエントリの一部を抜粋】
複数（この場合は255）のキーが1エントリに入っている
*----------------------------- * Rec #0x3 slt: 0x17 objn: 33507(0x000082e3) objd: 33507 tblspc: 0(0x00000000) index undo for leaf key operations purge leaf row ← リーフの値をpurge（元に戻す）する。 number of keys: 255 ← 一括更新のキーの数 key sizes: 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 ・ ・ key :(2550): 02 c1 02 06 00 40 f9 43 00 11 02 c1 02 06 00 40 f9 43 00 12 02 c1 02 06 00 ・ ・
以下の順番でUNDOエントリが作成されている。
※インデックスは複数行に対して1UNDOエントリで作成している
[テーブルの更新前のUNDOエントリ] ←----
↓ |
[インデックスの更新前のUNDOエントリ] | （1万回LOOP）
↓ |
[インデックスの更新後のUNDOエントリ] →
初めにテーブルに対するUNDOエントリが全て作られ、次にインデックスの更
新前、更新後のUNDOエントリが作成されている。また、インデックスに関し
ては、数百件のリーフ値に対して、1エントリしか作成されない為に、全件検
索時よりもはるかに効率が良い（使用UNDO領域が少ない）。
ここまでの内容から推測するに、全件検索での更新処理では一旦、CRブロッ
クをトランザクション専用のイメージとして作成した後、その情報を元にカ
レント・ブロックに対して一行ずつ更新を行い、そのたびにインデックスの
更新を行う必要があるのではないだろうか。
INSERT文やUPDATE文のチューニングを行う際は、作成されるUNDO情報の内容
も考慮する必要がある。今回検証を行っているようなSQLは本番環境ではほと
んどないであろうが、実行時間を計測したところ、インデックス検索による
更新処理の方が全件検索に比べて2倍早いという結果を得た。
以上、茅ヶ崎にて