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	期間中に同時実行されたトランザクションの最大数を示す。