[Oracle]TEMP表領域使用率と使用セッションを確認するSQL
TEMP表領域使用率と使用セッションを確認するSQLをいつくか記載します。
※Oracle 19cで確認してます。
マルチテナント環境用です。※CON_IDカラムを省けは非CDB環境でも実行可能です。
基本、接続しているコンテナの情報を表示します。
12.2から登場したローカル一時表領域には対応していません(というか確認してません。いずれ…)
TEMP表領域の使用率確認 (RAC対応)
SQL: 確認したいPDBに接続して実行します。
COL "USED[MB]" FORMAT 999990.9
COL "FSIZE[MB]" FORMAT 999990.9
COL "MAXSIZE[MB]" FORMAT 999990.9
SELECT
DT.TABLESPACE_NAME,
NVL("USED[MB]",0 ) "USED[MB]", -- 使用中のサイズ
"FSIZE[MB]", -- 現在のファイルサイズ
"MAXSIZE[MB]" -- 自動拡張の最大ファイルサイズ
FROM
(SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024/1024 "FSIZE[MB]",
--autoextensible=noの場合 現在のサイズ(bytes)をmaxbytesに設定
SUM(CASE AUTOEXTENSIBLE WHEN UPPER('YES') THEN MAXBYTES ELSE BYTES END)/1024/1024 "MAXSIZE[MB]"
FROM
DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) DT
LEFT OUTER JOIN
(SELECT
TABLESPACE_NAME,
SUM(BYTES_USED)/1024/1024 "USED[MB]"
FROM (SELECT DISTINCT * from GV$TEMP_EXTENT_POOL )
GROUP BY TABLESPACE_NAME ) TE
ON DT.TABLESPACE_NAME = TE.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME
;
実行例:
TABLESPACE_NAME USED[MB] FSIZE[MB] MAXSIZE[MB]
------------------------------ --------- --------- -----------
TEMP 4.0 36.0 32768.0
TEMP2 0.0 100.0 32768.0
クエリの説明
- GV$TEMP_EXTENT_POOLをdistinctで重複行排除しているのは複数レコードが表示されるというBugの回避のためです(19.3では直っていない)。
- BIGFILE,SMALLFILEどちらでも対応しています。
- シングルインスタンス環境(Non RAC)ではGV$TEMP_EXTENT_POOL ⇒ V$TEMP_EXTENT_POOLにすることで使用可能です。
- TABLESPACE_NAME,GV$TEMP_EXTENT_POOLを左外部結合していのは未使用のTEMP表領域はGV$TEMP_EXTENT_POOLに載らないためです。
TEMPを使用中のセッション確認 (RAC対応)
SQL: セッション単位にTEMP使用量を抽出。カラムや並びは好み。
SET LINESIZE 350
SET PAGESIZE 100
COL CON_NAME FORMAT A30
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=TU.CON_ID) CON_NAME,
S.USERNAME,
TU.TABLESPACE,
TU.BLOCKS * (SELECT VALUE FROM V$PARAMETER WHERE NAME = LOWER('DB_BLOCK_SIZE'))/1024/1024 "USED[MB]",
S.SID,
S.SERIAL#,
S.SQL_ID,
S.SQL_EXEC_START,
(SELECT SQL_TEXT FROM GV$SQL WHERE SQL_ID=S.SQL_ID AND ROWNUM =1) SQL_TEXT -- SQL_TEXT
FROM
(SELECT
INST_ID,
CON_ID,
TABLESPACE,
SESSION_ADDR,
SUM(BLOCKS) BLOCKS
FROM GV$TEMPSEG_USAGE
GROUP BY
INST_ID,
CON_ID,
TABLESPACE,
SESSION_ADDR )TU,
GV$SESSION S
WHERE 1=1
AND TU.SESSION_ADDR = S.SADDR
AND TU.INST_ID = S.INST_ID
AND TU.CON_ID = S.CON_ID
ORDER BY
S.INST_ID,
"USED[MB]"
;
実行例
SQL> /
INST_ID CON_NAME USERNAME TABLESPACE USED[MB] SID SERIAL# SQL_ID SQL_EXEC_START SQL_TEXT
---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------- ------------------- ----------------------------------------
1 PDBHANPEN01 SYS TEMP 13 60 8876 0wgu17jc7ct9g 2021/10/06 21:20:52 select count(2) from dba_tables,dba_tab
les,dba_tables
2 PDBHANPEN01 SYS TEMP 24 287 30993 ax6y79dw7d6ft 2021/10/06 21:18:43 select count(1) from dba_tables,dba_tabl
es,dba_tables
SQL>
クエリの説明
- CDB$ROOTに接続すると全てのコンテナの情報が、それ以外のコンテナに接続すると接続中のコンテナの情報が取得できます。
- GV$TEMPSEG_USAGEのBLOCKSを本問い合わせでSUMするとGROUP BYがごちゃごちゃしちゃうので副問い合わせであらかじめSUMした状態で結合しています。
- SQL_TEXTは小カーソル毎にレコードが格納されているため一つのSQL_IDに複数行格納されている場合があります。そのためROWNUMを指定し1行のみ取得するようにしています。
TEMPを使用したの過去のセッション確認 (RAC対応)
SQL: 過去の話し。お好みで。
SET LINESIZE 350
SET PAGESIZE 100
COL CON_NAME FORMAT A8
COL SQL_TEXT FORMAT A40
COL USERNAME FORMAT A30
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT
SAMPLE_TIME,
INST_ID,
(SELECT NAME FROM V$CONTAINERS WHERE CON_ID=ASH.CON_ID) CON_NAME,
(SELECT USERNAME FROM CDB_USERS WHERE USER_ID=ASH.USER_ID AND CON_ID=ASH.CON_ID) USERNAME,
SESSION_ID,
SESSION_SERIAL#,
TEMP_SPACE_ALLOCATED,
SQL_ID,
(SELECT SQL_TEXT FROM GV$SQL WHERE SQL_ID=ASH.SQL_ID AND ROWNUM =1) SQL_TEXT
FROM
GV$ACTIVE_SESSION_HISTORY ASH
WHERE 1=1
AND SAMPLE_TIME > SYSDATE -1/24 --現在から1時間以内のセッション
AND TEMP_SPACE_ALLOCATED > 1024*1024 --1MB以上使用したセッション
ORDER BY SAMPLE_TIME
;
実行例:
SQL> /
SAMPLE_TIME INST_ID CON_NAME USERNAME SESSION_ID SESSION_SERIAL# TEMP_SPACE_ALLOCATED SQL_ID SQL_TEXT
--------------------------------------------------------------------------- ---------- -------- ------------------------------ ---------- --------------- -------------------- ------------- ----------------------------------------
20-04-28 05:20:48.809 1 PDBUFO1 SYS 40 28168 3145728 audkzzu4jr7ta SELECT S.INST_ID, (SELECT NAME FRO
M V$CONTAINERS WHERE CON_ID=TU.CON_ID) C
ON_NAME, S.USERNAME, TU.TABLESPACE
, TU.BLOCKS * (SELECT VALUE FROM V$PA
RAMETER WHERE NAME = LOWER('DB_BLOCK_SIZ
E'))/1024/1024 "USED[MB]", S.SID,
S.SERIAL#, S.SQL_ID, S.SQL_EXEC_ST
ART, (SELECT SQL_TEXT FROM GV$SQL WHE
RE SQL_ID=S.SQL_ID AND ROWNUM =1) SQL_TE
XT -- SQL_TEXT FROM (SELECT INS
T_ID, CON_ID, TABLESPACE,
SESSION_ADDR, SUM(BLOCKS) BLOC
KS FROM GV$TEMPSEG_USAGE GROUP BY
INST_ID, CON_ID, TABLE
SPACE, SESSION_ADDR )TU, GV$SES
SION S WHERE 1=1 AND TU.SESSION_ADDR
= S.SADDR AND TU.INST_ID = S.INST_ID
AND TU.CON_ID = S.CON_ID ORDER BY
S.INST_ID, "USED[MB]"
SQL>
クエリの説明
- CDB$ROOTに接続すると全てのコンテナの情報が、それ以外のコンテナに接続すると接続中のコンテナの情報が取得できます。
- SQL_TEXTは小カーソル毎にレコードが格納されているため一つのSQL_IDに複数行格納されている場合があります。そのためROWNUMを指定し1行のみ取得するようにしています。
