UNDOに関する検証 その8

<UNDOに関する検証その8>
ペンネーム:クレイジーボーダー

今回も、大量処理を実行した時に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の場合、いくつかの項目が0になってしまうため注意が必要である。

以下は、それぞれのケースで処理が終了した後に実行した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 – どっぷり検証生活 – 」のご購読を、
よろしくお願い致します。

以上、スノボーシーズンの終わりを感じる茅ヶ崎にて