続 X$BH に関する検証 その4

<続 X$BH に関する検証 ~その4~>
ペンネーム ちょびひげ

今回は前回予定していた内容を変更して、前回の検証で謎として残っている
なぜ全件検索での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エントリを作成]

             ↓

[インデックスに対する更新後情報の全UNDOエントリを作成]

初めにテーブルに対するUNDOエントリが全て作られ、次にインデックスの更
新前、更新後のUNDOエントリが作成されている。また、インデックスに関し
ては、数百件のリーフ値に対して、1エントリしか作成されない為に、全件検
索時よりもはるかに効率が良い(使用UNDO領域が少ない)。

ここまでの内容から推測するに、全件検索での更新処理では一旦、CRブロッ
クをトランザクション専用のイメージとして作成した後、その情報を元にカ
レント・ブロックに対して一行ずつ更新を行い、そのたびにインデックスの
更新を行う必要があるのではないだろうか。

INSERT文やUPDATE文のチューニングを行う際は、作成されるUNDO情報の内容
も考慮する必要がある。今回検証を行っているようなSQLは本番環境ではほと
んどないであろうが、実行時間を計測したところ、インデックス検索による
更新処理の方が全件検索に比べて2倍早いという結果を得た。

以上、茅ヶ崎にて