在Oracle中，如何监控TEMP和UNDO表空间并将耗费TEMP和UNDO空间非常大的SQL语句记录下来？
总体思路是采用JOB定时将耗费TEMP和UNDO的SQL语句记录在表中，这样日后查询将是非常方便的。代码如下所示：
CREATE TABLE XT_TMP_TS_LHR
( INST_ID NUMBER,
SID NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2(30),
STATUS VARCHAR2(8),
ACTION VARCHAR2(64),
MACHINE VARCHAR2(64),
MODULE VARCHAR2(64),
OSUSER VARCHAR2(30),
TERMINAL VARCHAR2(30),
PROGRAM VARCHAR2(48),
SQL_ID VARCHAR2(13),
TABLESPACE VARCHAR2(31),
SIZE_M NUMBER,
TEMP_TS_SIZE_M NUMBER,
C_USED_PERCENT NUMBER,
SEGTYPE VARCHAR2(9),
SQL_TEXT VARCHAR2(4000),
IN_DATE DATE
)
NOLOGGING;
CREATE OR REPLACE PROCEDURE P_TMP_TS_LHR AS
BEGIN
INSERT INTO XT_TMP_TS_LHR
SELECT V.INST_ID,
V.SID,
V.SERIAL#,
V.USERNAME,
V.STATUS,
V.ACTION,
V.MACHINE,
V.MODULE,
V.OSUSER,
V.TERMINAL,
V.PROGRAM,
V.SQL_ID,
SU.TABLESPACE,
(SU.BLOCKS *
TO_NUMBER((SELECT RTRIM(VALUE)
FROM V$PARAMETER P
WHERE P.NAME = 'db_block_size'))) 1024 1024 AS SIZE_M,
(SELECT ROUND(SUM(BYTES) / (1024 * 1024), 3) FROM V$TEMPFILE) TEMP_TABLESPACE_SIZE_M,
ROUND((SU.BLOCKS *
TO_NUMBER((SELECT RTRIM(VALUE)
FROM V$PARAMETER P
WHERE P.NAME = 'db_block_size'))) * 100 /
(SELECT SUM(BYTES)
FROM V$TEMPFILE),
3) C_USED_PERCENT,
SU.SEGTYPE,
(SELECT A.SQL_TEXT
FROM GV$SQLAREA A
WHERE A.SQL_ID = NVL(V.SQL_ID, SU.SQL_ID)
AND A.INST_ID = V.INST_ID
AND ROWNUM = 1) SQL_TEXT,
SYSDATE
FROM GV$SORT_USAGE SU, --GV$TEMPSEG_USAGE
GV$SESSION V
WHERE SU.SESSION_ADDR = V.SADDR
AND SU.INST_ID = V.INST_ID;
COMMIT;
END P_TMP_TS_LHR;
/
创建JOB每隔5分钟监控一次：
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_PRO_CHECK_TEMP_LHR',
JOB_TYPE => 'STORED_PROCEDURE',
JOB_ACTION => 'P_TMP_TS_LHR',
REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=5',
ENABLED => TRUE,
START_DATE => SYSDATE,
COMMENTS => '每5分钟检查一次TEMP表空间的大小');
END;
/
监控UNDO或其它表空间的方法一样，这里不再赘述。需要说明的是，脚本中可以设置阈值，例如，可以设置当SQL消耗超过1G的TEMP表空间时才将该SQL记录下来。
下面的SQL语句可以监控到UNDO的使用情况：
SELECT S.INST_ID,
S.SID,
S.SERIAL#,
(SELECT PR.SPID
FROM GV$PROCESS PR
WHERE S.PADDR = PR.ADDR
AND PR.INST_ID = S.INST_ID) SPID,
S.OSUSER,
S.USERNAME,
(SELECT A.SQL_TEXT
FROM GV$SQLAREA A
WHERE A.SQL_ID = NVL(S.SQL_ID,S.PREV_SQL_ID)
AND A.INST_ID = s.INST_ID
AND ROWNUM = 1) SQL_TEXT ,
S.SQL_ID,
S.LOGON_TIME,
ROUND(T.USED_UBLK *
TO_NUMBER((SELECT A.VALUE
FROM V$PARAMETER A
WHERE A.NAME = 'db_block_size'))) UNDO_,
(S.MODULE || '--' || S.ACTION || '--' || S.PROGRAM || '----' ||
S.CLIENT_IDENTIFIER || '--' || S.CLIENT_INFO || '--' ||
S.SERVICE_NAME) CLIENT_INFO,
S.EVENT
FROM GV$SESSION S,
GV$TRANSACTION T,
V$ROLLNAME R,
GV$ROLLSTAT G
WHERE T.ADDR = S.TADDR
AND T.XIDUSN = R.USN
AND R.USN = G.USN
AND S.INST_ID = T.INST_ID
AND S.INST_ID = G.INST_ID ;
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：230161599
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
