Oracle11g/12cでUNDO表領域を確認する方法~簡易版~

Oracle 11g,12cでUNDO表領域の使用率を監視する機会があったので、メモがてら簡易なコマンドを共有したいと思います。

 

UNDO表領域の利用率を確認するコマンド(SQL)

■処理前にDBA_SEGMENTSとDBA_FREE_SPACEを事前確認

SQLPlusで接続

[oracle@hostname ~]$ sqlplus / as sysdba

 

DBA_SEGMENTSを事前確認

SQL> select tablespace_name,sum(bytes)/1024/1024 as MB from dba_segments group by tablespace_name;

 

DBA_FREE_SPACEを事前確認

SQL> select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')as datetime,tablespace_name,'FREE' as status,sum(bytes)/1024/1024 as MB from dba_free_space where tablespace_name like 'UNDOTBS%' group by tablespace_name;

 

■処理中は定期的にUNDO表領域の状態を監視(Unix&Linuxコマンドで実行)

60秒間隔でDBA_UNDO_EXTENTSとDBA_FREE_SPACEを監視しLogfileに出力

 

[oracle@hostname ~]$ while true
> do
> sleep 60
> sqlplus / as sysdba << EOS >> Logfile
> set lines 200
> set colsep ,
> select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')as datetime,tablespace_name,status,sum(bytes)/1024/1024 as MB from dba_undo_extents group by tablespace_name,status;
> select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')as datetime,tablespace_name,'FREE' as status,sum(bytes)/1024/1024 as MB from dba_free_space where tablespace_name like 'UNDOTBS%' group by tablespace_name;
> EOS
> done

 

■一通り処理が終わった後にV$UNDOSTATを確認

[oracle@hostname ~]$ sqlplus / as sysdba
SQL> select to_char(begin_time,'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,to_char(end_time,'MM/DD/YYYY HH24:MI:SS') END_TIME,undotsn,undoblks,txncount,maxconcurrency as maxcon from V$UNDOSTAT where rownum <= 30;

 

各ビューと項目の説明

DBA_SEGMENTS

DBA_SEGMENTSは、データベース内のすべてのセグメントに割り当てられた記憶域を示します。

出典:Oracle Database(11.2)リファレンス

 

説明
TABLESPACE_NAME セグメントが設定されている表領域の名前
BYTES セグメントのバイト単位のサイズ

 

DBA_FREE_SPACE

DBA_FREE_SPACEは、データベース内のすべての表領域の使用可能エクステントを示します。

出典:Oracle Database(11.2)リファレンス

 

説明
TABLESPACE_NAME エクステントが設定されている表領域の名前
BYTES エクステントのサイズ(バイト)

 

DBA_UNDO_EXTENTS

DBA_UNDO_EXTENTSは、データベースのすべてのUNDO表領域内のセグメントを含むエクステントを示します。

出典:Oracle Database(11.2)リファレンス

説明
TABLESPACE_NAME UNDO表領域の名前
STATUS エクステントのUNDOのトランザクション・ステータス:
 – ACTIVE (使用中)
 – EXPIRED (解放可能)
 – UNEXPIRED (未使用だが解放不可)
BYTES エクステントのサイズ(バイト)

 

V$UNDOSTAT

V$UNDOSTATは統計データのヒストグラムを示して、システムがどれだけ効果的に動作しているかを示します。使用可能な統計情報には、UNDO領域の消費量、トランザクションの同時実行性およびインスタンスで実行された問合せの長さなどがあります。このビューを使用すると、現行のワークロードに必要なUNDO領域の量を見積もることができます。Oracleは、このビューを使用して、システム内のUNDO使用率をチューニングします。システムが手動UNDO管理モードになっている場合、このビューはNULL値を戻します

出典:Oracle Database(11.2)リファレンス

 

説明
BEGIN_TIME 時間間隔の開始点を示す
END_TIME 時間間隔の終了点を示す
UNDOTSN 該当する期間の最後にアクティブであったUNDO表領域を示す。このアクティブUNDO表領域の表領域IDは、この列に戻される。期間中に、複数のUNDO表領域がアクティブであった場合、その中で最後にアクティブであったアクティブUNDO表領域がレポートされる。
UNDOBLKS コンシューム済UNDOブロックの合計数を示す。この列を使用してUNDOブロックの使用率を取得し、システム上のワークロード処理に必要なUNDO表領域のサイズを見積もることができる。
TXNCOUNT 期間中に実行されたトランザクションの合計数を示す
MAXCONCURRENCY 期間中に同時実行されたトランザクションの最大数を示す。

 

Oracleが自動的に取得した統計データを使ってこのビューを表示します。ビューの使用には自動UNDO管理をONにしている必要があります。テーブルに挿入されるレコードは一定期間の統計データで、時間のFROM-TOを持っています。自動UNDO管理を有効にすると、デフォルトで10分間隔で取得されます。

 

簡易コマンドが有効なシチュエーション

私は、データ移行作業で大きなトランザクションが発生する際に、UNDO表領域が溢れないように監視する目的で上記のSQLを利用しました。

 

日々の運用でUNDO表領域のチューニングを行いたい場合には、もう少し自動化したりするほうが良いと思います。上記の手順はあくまで一時的にUNDO表領域の監視が必要な場合に簡易的に監視する方法です。

 

通常の運用の中でUNDO表領域のチューニングをする場合、エンタープライズマネージャ(Oracle Enterprise Manager)のUNDOアドバイザを使用するほうが簡単で確実です。

 

 

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です