监控业务SQL消耗TEMP空间的几种常见方法-来自一线监控需求

近经常遇到temp空间不足的问题，因为是重要系统，所以也监控了改表空间的使用率。但是近零点之后告警频繁
就需要分析哪些用户的哪些SQL造成了temp空间被耗尽，对于这个问题的思路有三个方式
1 如果是正在执行的SQL或者执行过的SQL依然在内存可以通过脚本查询。
2 如果是过去已经执行的SQL可以通过dba_hist视图查询，这个视图主要是通过等待事件定位，进而看改SQL的执行计划
分析执行计划是否优。
3 通过创建监控job,定制更加详细的阈值来监控用户和SQL相关信息

下面我们演示改三种方式如何定位SQL。如果是重要系统，我们还是推荐使用第三种方式更严谨。
我们先创建两个测试表
create table a as select * from all_objects;
create table b as select * from all_objects;
exec dbms_stats.gather_table_stats(user,'A');
exec dbms_stats.gather_table_stats(user,'B);
select num_rows,table_name from user_tables where table_name in ('A','B');

1 正在执行的SQL可以通过脚本查询

执行SQL：

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
48

SQL> select * from a,b order by 1,2;

查询消耗temp的SQL以及消耗多少UNdo

col username for a20
col tablespace for a20
col osuser for a20
col sql_text for a50

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

USERNAME SID SERIAL# OSUSER TABLESPACE BLOCKS SQL_TEXT
-------------------- ---------- ---------- -------------------- -------------------- ---------- --------------------------------------------------
TEST 48 31322 oracle TEMP 24192 select * from a,b order by :"SYS_B_0",:"SYS_B_1"

2 通过dba_hist或者v$active_session_history查询

SQL> select sql_id,event,count(*) from v$active_session_history where event like '%temp%' and sample_time >to_date('202101191000','yyyymmddhh24mi') group by sql_id,event order by count(*)

SQL_ID EVENT COUNT(*)
-------------------------- -------------------------------------------------- ----------
c6b0wtrqr9xnu direct path write temp 1
根据需求可以通过ASH查询其他字段信息，比如用户ID，program等待，具体维度自己根据需求添加吧。
后面可以进一步查询执行计划，分析哪里消耗了temp空间，看是否可以调优了。
SQL> select * from table(dbms_xplan.display_cursor('c6b0wtrqr9xnu'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID c6b0wtrqr9xnu, child number 0
-------------------------------------
select * from a,b order by :"SYS_B_0",:"SYS_B_1"

Plan hash value: 2084382175

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 393M(100)| |
| 1 | SORT ORDER BY | | 5045M| 1174G| 1480G| 393M (1)| 04:15:57 |
| 2 | MERGE JOIN CARTESIAN| | 5045M| 1174G| | 25M (1)| 00:16:53 |
| 3 | TABLE ACCESS FULL | A | 71033 | 8671K| | 367 (1)| 00:00:01 |
| 4 | BUFFER SORT | | 71034 | 8671K| | 393M (1)| 04:15:57 |
| 5 | TABLE ACCESS FULL | B | 71034 | 8671K| | 365 (1)| 00:00:01 |
--------------------------------------------------------------------------------------

3 通过job定制抓取消耗TEMP空间的SQL。
参考MOS： How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)
(1) 创建表
CREATE TABLE TEMP_SEG_USAGE(
DATE_TIME DATE,
USERNAME VARCHAR2(30),
SID VARCHAR2(6),
SERIAL# VARCHAR2(6),
OS_USER VARCHAR2(30),
SPACE_USED NUMBER,
SQL_TEXT VARCHAR2(1000));

(2) 创建存储过程
CREATE OR REPLACE PROCEDURE TEMP_SEG_USAGE_INSERT IS
BEGIN
insert into TEMP_SEG_USAGE
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024*1024
ORDER BY b.tablespace, b.blocks;
COMMIT;
END;
/

-- Schedule job for every 5 minute with DBMS_SCHEDULER.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MONITOR_TEMP_USAGE_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN TEMP_SEG_USAGE_INSERT; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
end_date => NULL,
enabled => TRUE,
comments => 'Job created to monitor temp usage.');
END;
/

执行频率以及执行消耗TEMP空间的阈值根据实际自己调整吧。