UNDOに関する検証 その８
＜UNDOに関する検証その８＞
ペンネーム：クレイジーボーダー
今回も、大量処理を実行した時にUNDO表領域のサイズの違いによって影響し
てくるパフォーマンスやスループットについて引き続き検証していく。前回
と同様にUNDO表領域が1MBの場合と1000MBの場合で比較していく。今回は、
ブロック・ダンプ等の結果があるために普段より少々多くなっているのでご
了承下さい。
では、100万件の処理中と処理後の様子をトランザクション表(TRN TBL)から
見てみる。トランザクション表とはトランザクション情報を格納しており、
ロールバック・セグメントのセグメント・ヘッダーに存在している。この情
報は、実際にロールバック・セグメントにUNDO情報を格納する前に、更新さ
れる。
トランザクション表は、ブロック・ダンプから参照できる。この詳細につい
ては、以前のメルマガ「ロールバック・セグメントに関する検証」を参照し
て欲しい。
まず、100万件の処理を実行中に以下のSQL文を発行し、この処理で使用され
ているロールバック・セグメントを見つける。
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
21 _SYSSMU21$ ONLINE 2 129024 129024 0
22 _SYSSMU22$ ONLINE 23 8386560 8386560 1
23 _SYSSMU23$ ONLINE 2 129024 129024 0
24 _SYSSMU24$ ONLINE 2 129024 129024 0
25 _SYSSMU25$ ONLINE 2 129024 129024 0
26 _SYSSMU26$ ONLINE 2 129024 129024 0
27 _SYSSMU27$ ONLINE 2 129024 129024 0
28 _SYSSMU28$ ONLINE 2 129024 129024 0
29 _SYSSMU29$ ONLINE 2 129024 129024 0
30 _SYSSMU30$ ONLINE 2 129024 129024 0
以上の結果から、ロールバック・セグメント（_SYSSMU22$）が使用されてい
るのが分かる。このセグメントを基にブロック・ダンプを取得する。
SQL> SELECT SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '_SYSSMU22
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
--------------- ----------- ------------
_SYSSMU22$ 8 97
SQL> ALTER SYSTEM DUMP DATAFILE 8 BLOCK 97;
ここで、処理中に取得したダンプの中身を比較してみた。例えば、トランザ
クション表はもちろんだが、Extent Control Header、Extent Map や
Retention Tableなど。しかし、それぞれ大きな違いはなかったので、処理後
のダンプを比較してみる。
以下はトランザクション表（TRN TBL）を抜粋したものである。
＜1000MBのUNDO表領域＞
TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num ---------------------------------------------------------------------------------------------------------- 0x00 9 0x00 0x0004 0xffff 0x0000.000e6357 0x03c08fcb 0x0000.000.00000000 0x00008b83 0x00000000 0x01 9 0x00 0x000b 0x0002 0x0000.000e62ec 0x03c085df 0x0000.000.00000000 0x00000001 0x00000000 0x02 9 0x00 0x000b 0x0003 0x0000.000e6315 0x03c085df 0x0000.000.00000000 0x00000001 0x00000000 0x03 9 0x00 0x000b 0x0004 0x0000.000e632d 0x03c085df 0x0000.000.00000000 0x00000001 0x00000000 0x04 9 0x00 0x000b 0x0000 0x0000.000e6345 0x03c085df 0x0000.000.00000000 0x00000001 0x00000000 0x05 9 0x00 0x000a 0x0006 0x0000.000e6230 0x03c078a6 0x0000.000.00000000 0x00000001 0x00000000 0x06 9 0x00 0x000a 0x0007 0x0000.000e6249 0x03c078a6 0x0000.000.00000000 0x00000001 0x00000000 0x07 9 0x00 0x000a 0x0008 0x0000.000e6261 0x03c07df0 0x0000.000.00000000 0x00000001 0x00000000 0x08 9 0x00 0x000a 0x0009 0x0000.000e6296 0x03c08095 0x0000.000.00000000 0x00000001 0x00000000 0x09 9 0x00 0x000a 0x0001 0x0000.000e62b1 0x03c08095 0x0000.000.00000000 0x00000001 0x00000000
＜1MBのUNDO表領域＞
TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num ---------------------------------------------------------------------------------------------------------- 0x00 9 0x00 0x0044 0x0002 0x0000.000e5802 0x02008097 0x0000.000.00000000 0x00000001 0x00000000 0x01 9 0x00 0x003d 0xffff 0x0000.000e58ce 0x02008fcc 0x0000.000.00000000 0x00008b83 0x00000000 0x02 9 0x00 0x0044 0x0003 0x0000.000e581e 0x020085e1 0x0000.000.00000000 0x00000001 0x00000000 0x03 9 0x00 0x0044 0x0004 0x0000.000e5852 0x020085e1 0x0000.000.00000000 0x00000001 0x00000000 0x04 9 0x00 0x0044 0x0005 0x0000.000e586a 0x020085e1 0x0000.000.00000000 0x00000001 0x00000000 0x05 9 0x00 0x0044 0x0001 0x0000.000e5882 0x020085e1 0x0000.000.00000000 0x00000001 0x00000000 0x06 9 0x00 0x0043 0x0007 0x0000.000e5756 0x020078a7 0x0000.000.00000000 0x00000001 0x00000000 0x07 9 0x00 0x0043 0x0008 0x0000.000e5789 0x020078a7 0x0000.000.00000000 0x00000001 0x00000000 0x08 9 0x00 0x0043 0x0009 0x0000.000e57a3 0x02007df1 0x0000.000.00000000 0x00000001 0x00000000 0x09 9 0x00 0x0043 0x0000 0x0000.000e57bd 0x02008097 0x0000.000.00000000 0x00000001 0x00000000
この二つを比較すると明らかにSCN(System Change Number:システム変更番号
やundoブロックのdba（data block address）等は違うが、一箇所だけ同じ値
がある。それは、dbaが0x03c08fcbのnubの値（UNDO表領域が1000MB）とdbaが
0x02008fcc（UNDO表領域が1MB）の値（0x00008b83）である。
nubとは、使用されたロールバック・セグメントのブロック数を表している。
これらが今回使用したロールバック・セグメントの情報であれば、ブロック
数という小さなレベルでも同じなので、パフォーマンスの違いは、前回仮定
したファイルの拡張が影響してくると思われる。まずここで述べたレコード
がV$ROLLSTATから得たアクティブなロールバック・セグメントであるかを確
認する。
このdbaは16進数なので10進数に変換し、以下のSQL文を実行する。
16進数の0x02008fccは、10進数では33591244。
16進数の0x03c08fcbは、10進数では62951371。
SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID= DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(33591244) AND DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(33591244) BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS ; SEGMENT_NAME SEGMENT_TYPE -------------------- ------------------ _SYSSMU22$ TYPE2 UNDO
同様な方法でもう1つのほうも実行すると、V$ROLLSTATから得たアクティブな
ロールバック・セグメントであることが分かった。それでは動的ビューの
V$FILESTATからファイル情報を見てみる。この時、初期化パラメータ
（TIMED_STATISTICS）がTRUEになっていることを確認してから実行する。
FALSEの場合、いくつかの項目が０になってしまうため注意が必要である。
以下は、それぞれのケースで処理が終了した後に実行したV$FILESTATの結果
を一部抜粋した(見づらいため修正あり)。ちなみに、表領域（UNDOTBS_04）
が1000MBで作成したもので、表領域（UNDOTBS_03）は1MBで作成したもので
ある。
FILE_ID FILE_NAME TABLESPACE_NAME MBYTES PHYRDS PHYWRTS WRITETIM LSTIOTIM MAXIORTM MAXIOWTM
15 undo04.dbf UNDOTBS_04 1000 19 332405 141029609 1 5 8554
8 undo03.dbf UNDOTBS_03 74.1875 26 72227 245472056 4153 723 14184
以下に、全てではないが、一部の項目の意味を示す。
PHYRDS → 実行された物理読込みの回数 PHYWRTS → 実行された物理書込みの回数 WRITETIM → 書込みに費やされた時間（1/100秒単位） LSTIOTIM → 最新のI/Oに費やされた時間（1/100秒単位） MAXIORTM → 単一読込みの実行に費やされた最大時間（1/100秒単位） MAXIOWTM → 単一書込みの実行に費やされた最大時間（1/100秒単位）
ここで注目したいのがPHYWRTSとWRITETIMの値である。UNDO表領域が1MBの
UNDOTBS_03は、PHYWRTSは 72227 である。1000MBのUNDOTBS_04は 33240 で
1MBの半分以下の値である。言い換えれば、UNDO表領域が小さい方は大きいも
のよりも2倍以上の物理書込みの回数を実行したことになる。
また、WRITETIMの値もUNDO表領域の小さいUNDOTBS_03の方（245472056）が大
きいサイズのもの（141029609）の1.5倍以上の書込み時間がかかったことが
わかる。
したがって、大量処理終了後のエクステント数、ブロック数、サイズ等が、
結果的に同じあっても、UNDO表領域のサイズによってパフォーマンスが変わ
ってくる。すなわち、UNDO表領域のサイズが、バッチ処理などのトランザク
ションに必要なサイズより小さい場合、UNDO表領域で使用しているデータフ
ァイルの拡張等のために、書込みの処理がネックになってしまい、UNDO表領
域が大きいものよりも負荷になっているのである。
パフォーマンスの観点から見れば、ロールバック・セグメントを使用した手
動管理と同様に、バッチなどの大量処理に対しては、大きなロールバック・
セグメント、この場合、大きなUNDO表領域を使用した方が早いのである。
これらの結果を基に考えると、UNDO表領域を自動管理するにしても、以下の
ようなことを考慮する必要がある。
・ファイルシステムの制限、UNDO領域使用率、トランザクションのタイプ、
負荷の時間、セッション数等の明確化
・様々なトランザクションに対応するために、ファイルシステムの制限に問
題がなければ、複数の違うサイズのUNDO表領域の作成
・実行されるトランザクションの種類を見極め、動的にUNDO表領域
(初期化パラメータ：UNDO_TABLESPACE)を変更
・手動管理を行なうように、定期的な監視を行い、パフォーマンス等の確認
今回で、UNDO表領域に関する検証は最終回です。
約2ヶ月間、お付き合いしていただきましてありがとうございました。
少しは、読者の皆様のお役に立てたでしょうか？
ご質問・感想等ありましたら、メールでお聞かせくだされば幸いです。
引き続き、「おら！ オラ！ Oracle – どっぷり検証生活 – 」のご購読を、
よろしくお願い致します。
以上、スノボーシーズンの終わりを感じる茅ヶ崎にて