[Oracle]UNDO表領域使用率と使用セッションを確認するSQL
UNDO表領域使用率と使用セッションを確認するSQLをいつくか記載します。あとおまけ。
※Oracle 19cで確認してます。
マルチテナント環境用です。※CON_IDカラムを省けは非CDB環境でも実行可能です。
ローカルUNDO/共有UNDOモードどちらの場合でも可です。
基本、接続しているコンテナ(PDB)の情報を表示します。
UNDO表領域の使用率確認(RAC対応)
SQL: ACTIVE , UNEXPIREDステータスのUNDO合計サイズを使用サイズとして、表領域最大サイズからの割合を抽出
COL "BYTE[MB]" FOR 999999.9
COL "MAX[MB]" FOR 999999.9
COL "USED[%]" FOR 990.9
SELECT
DUE.TABLESPACE_NAME, -- UNDO表領域名
DUE."BYTE[MB]", -- 使用サイズ
DDF."MAX[MB]", -- 表領域最大サイズ
DUE."BYTE[MB]" / DDF."MAX[MB]" * 100 AS "USED[%]" -- 使用率(%)
FROM
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 AS "BYTE[MB]"
FROM DBA_UNDO_EXTENTS
WHERE STATUS IN ('ACTIVE','UNEXPIRED')
GROUP BY TABLESPACE_NAME
) DUE
INNER JOIN
(SELECT TABLESPACE_NAME,SUM(MAXBYTES)/1024/1024 AS "MAX[MB]"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'UNDO%'
GROUP BY TABLESPACE_NAME
) DDF
ON DUE.TABLESPACE_NAME = DDF.TABLESPACE_NAME
ORDER BY DDF.TABLESPACE_NAME
;
実行例:
SQL>/
TABLESPACE_NAME BYTE[MB] MAX[MB] USED[%]
------------------------------ --------- --------- -------
UNDOTBS1 24.5 32768.0 0.1
UNDOTBS2 113.3 32768.0 0.3
SQL>
ステータスについて
- ACTIVE ⇒ 未コミットのUNDO データ 。ロールバックに必要になるため、再利用不可 な領域 。
- UNEXPIRED ⇒ コミット済み、UNDO保存期間内のUNDO データ 。再利用不可な領域 。ただし領域不足になった場合に再利用される。
- EXPIRED ⇒ コミット済み、UNDO保存期間切れのUNDO データ 。 再利用可能な領域。
UNDOステータス毎の使用サイズ (RAC対応)
SQL: UNDOステータス毎の使用サイズを抽出
COL "USED[MB]" FOR 999999.9
SELECT TABLESPACE_NAME,STATUS,SUM(BYTES)/1024/1024 AS "USED[MB]"
FROM DBA_UNDO_EXTENTS
WHERE 1=1
AND TABLESPACE_NAME LIKE 'UNDO%'
AND STATUS IN ('ACTIVE','UNEXPIRED','EXPIRED')
GROUP BY TABLESPACE_NAME,STATUS
ORDER BY TABLESPACE_NAME,STATUS;
実行例
SQL>/
TABLESPACE_NAME STATUS USED[MB]
------------------------------ --------- ----------
UNDOTBS1 EXPIRED 32.1
UNDOTBS1 UNEXPIRED 23.0
UNDOTBS2 ACTIVE 7.1
UNDOTBS2 EXPIRED 9.8
UNDOTBS2 UNEXPIRED 14.3
SQL>
UNDOを使用中のセッション確認 (RAC対応)
SQL: セッション単位にUNDO使用量を抽出。カラムや並びは好み。
SET LINESIZE 350
SET PAGESIZE 100
COL CON_NAME FORMAT A8
COL HOST FORMAT A10
COL PROGRAM FORMAT A20
COL SQL_TEXT FORMAT A40
COL USERNAME FORMAT A30;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT
S.INST_ID,
(SELECT NAME FROM V$CONTAINERS WHERE CON_ID=T.CON_ID) CON_NAME, -- UNDOを使用しているコンテナ名
S.USERNAME,
SUBSTR(S.MACHINE,1,10) HOST, -- クライアントホスト(10文字切り)
S.PROGRAM, -- クライアントプログラム
T.USED_UBLK * (SELECT VALUE FROM V$PARAMETER WHERE NAME = LOWER('DB_BLOCK_SIZE'))/1024/1024 "USED_UNDO[MB]", -- UNDO使用サイズ[MB]
S.SID,
S.SERIAL#,
S.LOGON_TIME SESSION_START, -- ログイン時刻
T.START_DATE TRANSACTION_START, -- トランザクション開始時刻
S.SQL_ID, -- 実行中のSQL_ID
S.SQL_EXEC_START, -- SQLが開始した時刻
(SELECT SQL_TEXT FROM GV$SQL WHERE SQL_ID=S.SQL_ID AND ROWNUM =1) SQL_TEXT -- SQL_TEXT
FROM
GV$SESSION S,
GV$TRANSACTION T
WHERE 1=1
AND S.INST_ID = T.INST_ID
AND S.SADDR = T.SES_ADDR
ORDER BY "USED_UNDO[MB]" -- UNDO使用量順にソート
;
実行例 :
SQL> /
INST_ID CON_NAME USERNAME HOST PROGRAM USED_UNDO[MB] SID SERIAL# SESSION_START TRANSACTION_START SQL_ID SQL_EXEC_START SQL_TEXT
---------- -------- ------------------------------ ---------------------------------------- ------------------------------------------------ ------------- ---------- ---------- ------------------- ------------------- ------------- ------------------- ----------------------------------------
1 PDBUFO1 SYS rac-z1 sqlplus@rac-z1 (TNS V1-V3) .0078125 42 38893 2020/04/27 10:29:19 2020/04/27 10:30:52 2y2745kp2hxyr 2020/04/27 22:02:57 SELECT S.INST_ID, (SELECT NAME FRO
M V$CONTAINERS WHERE CON_ID=T.CON_ID) CO
N_NAME, -- UNDOを使用している
コンテナ名 S.USERNAME, SUBSTR(S.MA
CHINE,1,10) HOST,
-- クライアントホ
スト(10文字切り) S.PROGRAM,
-- クライアントプログラム
T.USED_UBLK * (SELECT VALUE FROM V$PA
RAMETER WHERE NAME = LOWER('DB_BLOCK_SIZ
E'))/1024/1024 "USED_UNDO[MB]", -- UNDO
使用サイズ[MB] S.SID, S.SERIAL#,
S.LOGON_TIME SESSION_START,
--
ログイン時刻 T.START_DATE TRANSACTION
_START,
-- トランザクション開始時刻
S.SQL_ID,
--
実行中のSQL_ID S.SQL_EXEC_START,
-- SQLが開始した時刻 (SE
LECT SQL_TEX
SQL>
- 現在接続しているコンテナの情報だけ取得できます。またあまりないとは思いますが、UNDOを使用したセッションが別のコンテナに移動してしまった場合取得から漏れます。CDB$ROOTコンテナで実行した場合は全てのコンテナの情報が取得できるのでこちらがおすすめです。
- CON_IDはGV$TRANSACTIONから取得した「UNDOを使用しているCON_ID」。alter session set containerでコンテナを移動している場合、現状のセッションがいるコンテナと異なるため。念のため。
- SQL_IDは現在実行中のSQLであってUNDOを使用したSQLとは限らないので注意が必要です。
- SQL_TEXTは小カーソル毎にレコードが格納されているため一つのSQL_IDに複数行格納されている場合があります。そのためROWNUMを指定し1行のみ取得するようにしています。
- UNDOを使用している同一セッションで実行すると実行SQLにこのSQLが表示されます(↑実行例はこのパターン)。あしからず。
UNDOモードの確認(RAC対応)
SQL
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';
実行例
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TRUE
SQL>
- TRUE ⇒ ローカルUNDOモード
- FALSE ⇒ 共有UNDOモード
UNDO保存期間(設定値)の確認 (RAC対応)
SQL: 初期化パラメーターUNDO_RETENTIONの確認(単位は秒)
COL NAME FORMAT A15
COL VALUE FORMAT A15
COL CON_NAME FORMAT A8
SELECT
(SELECT NAME FROM V$CONTAINERS WHERE CON_ID = P.CON_ID) CON_NAME,
INST_ID,
NAME,
VALUE
FROM
GV$PARAMETER P
WHERE
NAME = LOWER('UNDO_RETENTION')
ORDER BY INST_ID;
実行例
SQL> /
CON_NAME INST_ID NAME VALUE
-------- ---------- --------------- ---------------
PDBUFO1 1 undo_retention 1200
PDBUFO1 2 undo_retention 900
SQL>
- UNDO_RETENTIONはPDBで変更可能なパラメーターです。接続しているPDBの情報を取得しています。
- 初期化パラメーターを確認するビューはいくつかありますが、v$parameterは現在のセッションで有効になっているパラメーターを確認できるビュー。そのため接続してるコンテナの情報が取得できます。
- UNDO_RETENTIONはUNDO保存期間の下限値とした働き、実際の保存期間は自動チューニングされた下記のTUNED_UNDORETENTIONを見る必要があります。
UNDO保存期間(実際値)の確認 (RAC対応)
SQL ： V$UNDOSTAT.TUNED_UNDORETENTIONの確認(単位は秒)
COL CON_NAME FORMAT A8
SELECT
(SELECT NAME FROM V$CONTAINERS WHERE CON_ID=GVU1.CON_ID) CON_NAME,
GVU1.INST_ID,
TUNED_UNDORETENTION
FROM GV$UNDOSTAT GVU1,
( SELECT INST_ID,MAX(END_TIME) END_TIME FROM GV$UNDOSTAT GROUP BY INST_ID ) GVU2
WHERE 1=1
AND GVU1.INST_ID = GVU2.INST_ID
AND GVU1.END_TIME = GVU2.END_TIME
ORDER BY INST_ID;
実行例
SQL> /
CON_NAME INST_ID TUNED_UNDORETENTION
-------- ---------- -------------------
CDB$ROOT 1 1946
CDB$ROOT 2 900
SQL>
- 接続しているコンテナの情報を取得しています。
- V$UNDOSTATは10分間隔の統計値が確認できます。上記SQLは最新の統計値を取得しています(現在の値)。
