UNDOに関する検証　その５

＜UNDOに関する検証その５＞
ペンネーム：クレイジーボーダー

前回は、UNDO表領域を変更する際の注意点を説明した。
さて、今回はUNDO情報を保存するのに必要な領域の算出方法からその問題点、
また逆に、ディスクのサイズ制限があるなどのUNDO領域割当てにおいて、仕様
要求がある時の保存期間の指定や問題について検証していきたい。

UNDO領域の必要なサイズを算出する前に、実際にファイルのサイズ制限のため
に使用できる領域がない場合、どのようにUNDO表領域が動作するのか試してみ
る。もちろん、この際はUNDO_RETENTIONの値は関係ない。

UNDO領域を作成する際に、AUTO EXTEND属性を含めず、ファイルのサイズを小
さめに作成する。その後、UNDO領域を使うような処理（インポート）を行なっ
た。

＜インポート結果一部抜粋＞

. importing TPC's objects into MAG2
. . importing table                     "CUSTOMER"
IMP-00058: ORACLE error 30036 encountered
ORA-30036: unable to extend segment by 32 in undo tablespace
           'UNDOTBS02'
IMP-00028: partial import of previous table rolled back: 4129 rows
           rolled back
. . importing table                      "HISTORY"

当然の結果だが、選択したUNDO領域には使用可能な領域がないというオラクル
のエラー（ORA-30036）が表示された。この結果は後に説明するが、V$UNDOSTAT
のカラム（NOSPACEERRCNT）から参照できるようになっている。

この対処としては、UNDO表領域を増やすか、commitの頻度を増やすしか方法は
ない。

さて、こういったUNDO領域のサイズに制限がある場合、いかに適度なUNDO表領
域のサイズを算出するかが重要になってくる。以下の式により必要な領域サイ
ズが算出される。

UNDOの保存に必要な領域 = (UR * UPS + OVERHEAD) * DBS

個々の項目内容は次の通りである。
１．(UR) UNDO_RETENTION(秒)
２．(UPS) 1秒間に使用する(作られる)UNDOブロックの数
３．(OVERHEAD) メタデータ使用時(トランザクション表等)のオーバーヘッド
４．(DBS) DB_BLOCK_SIZE

２番目の値を求めるには、動的パフォーマンス・ビュー（V$UNDOSTAT）を使用
する必要がある。このビューは、UNDO領域の監視とチューニングを行うための
統計情報が含まれている。ビュー内の各行には、インスタンス内で10分ごとに
収集された統計が表示され、現行の作業負荷に必要なUNDO領域の量を見積もる
ことが可能である。また、このビューには7日サイクルによる合計1008行が含
まれる。

以下を実行すると、１秒間に使用するUNDOブロックの数が計算される。

SQL> SELECT (SUM(UNDOBLKS))/SUM((END_TIME-BEGIN_TIME)*86400)
     FROM V$UNDOSTAT;

BEGIN_TIMEとEND_TIMEがDATEデータのために、秒に変更するため
86400(60*60*24)を掛けている。

では、1秒間に使用する(作られる)UNDOブロックの数を大体100ぐらいにし、
overheadは24とする。また、保存期間はフラッシュバック等を行ないたい為
3時間（10800）にする。

この条件でUNDO表領域を求めると
UNDOの保存に必要な領域 = (UR * UPS + OVERHEAD) * DBS

SQL> SELECT (((UR * UPS + 24) * DBS)/1024)/1024 AS "UNDO SIZE(MB)"
     FROM
    (SELECT VALUE AS UR
     FROM V$PARAMETER
     WHERE NAME = 'UNDO_RETENTION'),
    (SELECT (SUM(UNDOBLKS)/SUM((END_TIME - BEGIN_TIME) * 86400))
     AS UPS
     FROM V$UNDOSTAT),
    (SELECT VALUE AS DBS
     FROM V$PARAMETER
     WHERE NAME = 'DB_BLOCK_SIZE');

UNDO情報の保存に必要な領域は、約16GBになる。思っていた以上に大きなサイ
ズが必要という結果となった。

すなわち、負荷が高いシステムで、1秒間に使用する(作られる)UNDOブロック
の数が高い環境はもちろんだが、UNDO情報を保存する期間を延ばす場合、UNDO
表領域のサイズが非常に影響し、領域の問題となってくる。注意が必要である。

次回も、この動的パフォーマンス・ビュー（V$UNDOSTAT）に関して検証
していく予定である。

以上、アウェイ幕張から変わりホーム茅ヶ崎にて

UNDOに関する検証 その５