UNDOに関する検証 その７
＜UNDOに関する検証その７＞
ペンネーム：クレイジーボーダー
先週は、フラッシュバックを行なうためのデータを保存する期間という観点
から、UNDO情報を保存するのに必要な領域を統計情報を基に算出した。
今回はバッチ処理などの大量の更新処理を実行した時のUNDOの動作・サイズ
を検証していきたい。Oracle9i以前ならば、トランザクション毎に大きいサ
イズのロールバック・セグメントを使用し、エクステントする回数を減らす
ことにより、パフォーマンスの向上が期待できた。
しかし、9iでは自動管理のためこの作業を必要としない。どのように機能し
ているのだろうか？例えば100万件の処理を実行した際、手動管理と自動管理
ではどのような差がでるのだろうか？今回はその違いを検証していきたい。
SCOTTのEMP表を基にしたテーブルに対して、100万件の処理を実行する。自動
管理でのUNDO表領域のエクステントの負荷等はわかりにくいため、意図的に
ファイルサイズを小さいものと、大きいのものと二通り実行する。これらは
AUTOEXTEND属性である。最後に手動管理の三つのケースより検証を行う。
ケース1:自動管理 – 1MBのサイズで作成したUNDO表領域を使用する。
実行する前のロールバック・セグメントの状態を確認する。
SQL> SELECT N.USN, N.NAME, S.STATUS, S.EXTENTS, S.RSSIZE, S.HWMSIZE,
S.XACTS
FROM V$ROLLNAME N, V$ROLLSTAT S
WHERE N.USN = S.USN;
＜処理実行前＞
USN NAME STATUS EXTENTS RSSIZE HWMSIZE XACTS
0 SYSTEM ONLINE 8 407552 407552 0
66 _SYSSMU66$ ONLINE 2 129024 129024 0
67 _SYSSMU67$ ONLINE 2 129024 129024 0
68 _SYSSMU68$ ONLINE 2 129024 129024 0
69 _SYSSMU69$ ONLINE 2 129024 129024 0
70 _SYSSMU70$ ONLINE 2 129024 129024 0
71 _SYSSMU71$ ONLINE 2 129024 129024 0
72 _SYSSMU72$ ONLINE 2 129024 129024 0
73 _SYSSMU73$ ONLINE 2 129024 129024 0
74 _SYSSMU74$ ONLINE 2 129024 129024 0
75 _SYSSMU75$ ONLINE 2 129024 129024 0
実際に100万件の処理を実行してみると、全処理に31分程かかった。終了後の
ロールバック・セグメントの状態は、１つのロールバック・セグメント
(_SYSSMU74$)のみが処理時間前と違っていた。
＜処理実行後＞
一部抜粋:
USN NAME STATUS EXTENTS RSSIZE HWMSIZE XACTS 0 SYSTEM ONLINE 8 407552 407552 0 74 _SYSSMU74$ ONLINE 80 75495424 75495424 0 75 _SYSSMU75$ ONLINE 2 129024 129024 0
ケース2:自動管理－1000MBのサイズで作成したUNDO表領域を使用する。
＜処理実行前＞
処理実行前はケース1と同様に、全てのロールバック・セグメントはEXTENTS
が2で、RSSIZEとHWMSIZEは126K(129024)の状態であった。
一部抜粋：
USN NAME STATUS EXTENTS RSSIZE HWMSIZE XACTS 0 SYSTEM ONLINE 8 407552 407552 0 64 _SYSSMU64$ ONLINE 2 129024 129024 0 65 _SYSSMU65$ ONLINE 2 129024 129024 0
100万件の処理を実行すると、なんと25分程で処理が終了した！6分弱も早く
なったのである。約22パーセントのパフォーマンス向上である。終了後のロ
ールバック・セグメントの状態は、ケース1同様、一つのロールバック・セグ
メントのみ値（状態）が変わっていた。
＜処理実行後＞
USN NAME STATUS EXTENTS RSSIZE HWMSIZE XACTS 0 SYSTEM ONLINE 8 407552 407552 0 64 _SYSSMU64$ ONLINE 80 75495424 75495424 0 65 _SYSSMU65$ ONLINE 2 129024 129024 0
RSSIZE、EXTENTSやHWMSIZEの値はケース1と同じにもかかわらず全処理時間が
早くなった。なぜなんだろうか？
ここからは机上での予想に過ぎないが、考えられる一つとして、ケース1では
エクステント数だけの増加だけではなく、ファイルサイズの拡張という処理
が入っている。これが原因の可能性はあるが、とりあえず、ロールバック・
セグメントを使用した場合と比較してみる。
ケース３:手動管理－INITIAL, NEXTを100MBにしたロールバック・セグメント
を使用する。
エクステントしないように、ロールバック・セグメントを大きめに作成する。
ロールバック・セグメントは以下の状態にある。
＜処理実行前＞
USN NAME STATUS EXTENTS RSSIZE HWMSIZE XACTS 0 SYSTEM ONLINE 8 407552 407552 0 76 RBS05 ONLINE 2 209713152 209713152 0
100万件の処理を実行すると、ロールバック・セグメントを利用した全処理時
間は、ケース2と同様に25分程度であった。エクステントしないように作成し
たので、ロールバック・セグメントの状態は、処理前と同じである。
＜処理実行後＞
USN NAME STATUS EXTENTS RSSIZE HWMSIZE XACTS 0 SYSTEM ONLINE 8 407552 407552 0 76 RBS05 ONLINE 2 209713152 209713152 0
上記三つのケースをまとめると、次のようなことが考えられる。バッチ処理
等の大量処理を実行する場合、使用するUNDO表領域内のロールバック・セグ
メントのエクステント数やサイズは同じである。しかしUNDO表領域のサイズ
(ファイルサイズ)によって処理時間が変わる。
UNDO表領域の拡張を必要としないサイズで表領域を作成しておくと、大きな
サイズのロールバック・セグメントを使用した手動管理と同じくらいの処理
時間になる。すなわち、UNDOブロックの使用率・エクステント数など他の要
因が絡むので一概には言えないが、今回のケースを基に考えると、UNDO表領
域自体が拡張するサイズで作成されている場合、手動管理で実行し特定のト
ランザクションには大きめのロールバック・セグメントを使用した方がパフ
ォーマンス向上につながる可能性がある。
ただし、今回は同じ自動管理で実行した時の違いを明確にできなかったので
検証が完結に至らなかった。この点についてはお詫びしたい。来週できれば
ダンプ等を利用し、この点を検証したいと思う。
以上、花粉の嵐の茅ヶ崎より