关于Doc ID 317441.1提供的查看当前使用临时表空间的SQL存在的问题
How Do You Find Who And What SQL Is Using Temp Segments (Doc ID 317441.1)
对于10.1以上的版本,该文档提供如下SQL查询,稍微改造了下。
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks*8/1024 use_temp_mb, 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;
但实际上使用如下语句查询后,存在一些其他会话也在使用temp空间。
set line 300
col username for a25
col tablespace for a10
col sql_id for a13
col prev_sql_id for a13
col SEGTYPE for a10
col contents for a10
select s.sid,
s.serial#,
s.username,
s.sql_id,
s.prev_sql_id,
--u.sql_id,
u.tablespace,
u.contents,
u.segtype,
u.extents,
u.blocks,
round(((u.blocks * P.VALUE) / 1024 / 1024), 2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
AND UPPER(P.NAME) = 'DB_BLOCK_SIZE'
order by MB DESC;
14:38:42 SYS@test2(2217)> set line 300
14:45:17 SYS@test2(2217)> col username for a25
14:45:17 SYS@test2(2217)> col tablespace for a10
14:45:17 SYS@test2(2217)> col sql_id for a13
14:45:17 SYS@test2(2217)> col prev_sql_id for a13
14:45:17 SYS@test2(2217)> col SEGTYPE for a10
14:45:17 SYS@test2(2217)> col contents for a10
14:45:17 SYS@test2(2217)> select s.sid,
14:45:18 2 s.serial#,
14:45:18 3 s.username,
14:45:18 4 s.sql_id,
14:45:18 5 s.prev_sql_id,
14:45:18 6 --u.sql_id,
14:45:18 7 u.tablespace,
14:45:18 8 u.contents,
14:45:18 9 u.segtype,
14:45:18 10 u.extents,
14:45:18 11 u.blocks,
14:45:18 12 round(((u.blocks * P.VALUE) / 1024 / 1024), 2) MB
14:45:18 13 from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P
14:45:18 14 where s.saddr = u.session_addr
14:45:18 15 AND UPPER(P.NAME) = 'DB_BLOCK_SIZE'
14:45:18 16 order by MB DESC;
SID SERIAL# USERNAME SQL_ID PREV_SQL_ID TABLESPACE CONTENTS SEGTYPE EXTENTS BLOCKS MB
---------- ---------- ------------------------- ------------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
1374 3955 XXXXXXX_XXXX 38mq996b6vyf9 TEMP TEMPORARY LOB_DATA 6777 867456 6777
1308 1925 XXXXXXX_XXXX 4s5t8xn6xtksd 4s5t8xn6xtksd TEMP TEMPORARY LOB_DATA 1272 162816 1272
1374 3955 XXXXXXX_XXXX 38mq996b6vyf9 TEMP TEMPORARY LOB_INDEX 261 33408 261
1308 1925 XXXXXXX_XXXX 4s5t8xn6xtksd 4s5t8xn6xtksd TEMP TEMPORARY LOB_INDEX 92 11776 92
2213 40587 XXXXXXX gcshr3q6xnhu6 TEMP TEMPORARY LOB_DATA 2 256 2
2039 60903 XXXXXXX_XXX 4d3s1h918g6kn TEMP TEMPORARY LOB_DATA 2 256 2
995 2333 XXXXXXX_XXX 4d3s1h918g6kn TEMP TEMPORARY LOB_DATA 2 256 2
998 2205 XXXXXXX_XXXXXXXX f7rwpfmt8qbds TEMP TEMPORARY LOB_DATA 1 128 1
879 42597 XXXXXXX_XX 75p9dz19bafvz TEMP TEMPORARY LOB_DATA 1 128 1
1256 2839 XXXXXXX_XXXX cvy6vzrh3m9ug TEMP TEMPORARY LOB_DATA 1 128 1
843 15 DBSNMP 520mkxqpf15q8 TEMP TEMPORARY LOB_DATA 1 128 1
620 27071 DBSNMP 520mkxqpf15q8 TEMP TEMPORARY LOB_DATA 1 128 1
419 11859 XXXXXXX_XXXX f7rwpfmt8qbds TEMP TEMPORARY LOB_DATA 1 128 1
68 1683 DBSNMP 520mkxqpf15q8 TEMP TEMPORARY LOB_DATA 1 128 1
1403 5365 XXXXXXX 43x5v5s6c4hmv TEMP TEMPORARY LOB_DATA 1 128 1
1458 443 XXXXXXX_XXX 6cs5y9sk09bv7 TEMP TEMPORARY LOB_DATA 1 128 1
1521 25765 QA_XXXXXXX_XXX 17wjjj5cghz9r TEMP TEMPORARY LOB_DATA 1 128 1
1625 3 DBSNMP g1n7yg84rqj0y TEMP TEMPORARY LOB_DATA 1 128 1
1952 1091 XXXXXXX_XXXX 1jhknmxn3zwn7 TEMP TEMPORARY LOB_DATA 1 128 1
2234 1095 XXXXXXX_XXXX f7rwpfmt8qbds TEMP TEMPORARY LOB_DATA 1 128 1
2039 60903 XXXXXXX_XXX 4d3s1h918g6kn TEMP TEMPORARY LOB_INDEX 1 128 1
995 2333 XXXXXXX_XXX 4d3s1h918g6kn TEMP TEMPORARY LOB_INDEX 1 128 1
2089 47 SYSTEM 53zazd5rv1ca9 TEMP TEMPORARY LOB_DATA 1 128 1
1018 17 DBSNMP 520mkxqpf15q8 TEMP TEMPORARY LOB_DATA 1 128 1
24 rows selected.
Elapsed: 00:00:00.06
可以发现,比如sid=1374的那一行中,prev_sql_id为38mq996b6vyf9,这条sql经过确认并不会消耗sql语句。
这是因为会话1374曾经执行过消耗了大量临时表空间的SQL,然后后续有执行了其他SQL,因此Doc ID 317441.1提供的SQL就没捕捉到1374会话。
因此,想要找出实际的情况,不推荐使用Doc ID 317441.1提供的SQL,用我上边另外的SQL查询即可。
至于如何找出会话1374实际消耗temp空间的SQL,用v$open_cursor慢慢找吧。。
另外,其他通过dba_hist_sess_history追溯历史时刻temp表空间占用问题的,
这两例,和上边一样均无法找出“隐藏于幕后”的占用临时表空间的语句的会话,
只能找出那个时候正在使用临时表空间的SQL的会话。
这里提供一下该脚本:
select instance_number,to_char(a.sample_time,'yyyy-mm-dd hh24:mi:ss') sample_time,sum(temp_space_allocated)/1024/1024 mb,sql_id
from dba_hist_active_sess_history a
where a.temp_space_allocated is not null and a.sample_time between to_date('2020-10-27 12:49:00','yyyy-mm-dd hh24:mi:ss') and to_date('2020-10-27 12:51:00','yyyy-mm-dd hh24:mi:ss')
group by instance_number,to_char(a.sample_time,'yyyy-mm-dd hh24:mi:ss'),sql_id
order by 1,2;
13:55:30 SYS@test1(1222)> select instance_number,to_char(a.sample_time,'yyyy-mm-dd hh24:mi:ss') sample_time,sum(temp_space_allocated)/1024/1024 mb,sql_id
13:57:01 2 from dba_hist_active_sess_history a
13:57:01 3 where a.temp_space_allocated is not null and a.sample_time between to_date('2020-10-27 12:49:00','yyyy-mm-dd hh24:mi:ss') and to_date('2020-10-27 12:51:00','yyyy-mm-dd hh24:mi:ss')
13:57:01 4 group by instance_number,to_char(a.sample_time,'yyyy-mm-dd hh24:mi:ss'),sql_id
13:57:01 5 order by 1,2;
INSTANCE_NUMBER SAMPLE_TIME MB SQL_ID
--------------- -------------------------------------------------- ---------- -------------------------
1 2020-10-27 12:50:24 1 4b4pkcjgtxbqq
1 2020-10-27 12:50:34 1815 4b4pkcjgtxbqq
1 2020-10-27 12:50:44 3583 4b4pkcjgtxbqq
2 2020-10-27 12:50:01 1 f27uxgwvkdcgv
Elapsed: 00:01:20.12
监控:
如何随着时间的推移监控临时段的使用情况?(文档 ID 364417.1)
其他
How Can Temporary Segment Usage Be Monitored Over Time? (文档 ID 364417.1)