Oracle 11g 查看TEMP实际占用

郑全 · 发表于 2024-11-6 17:48:52

收到一些库有会话占用了大量temp表空间的告警，通常的做法是查询v$sort_usage找到对应sql_id

select *
from (select username,
session_addr,
sql_id,
contents,
segtype,
blocks * 8 / 1024 / 1024 mb
from v$sort_usage
order by blocks desc)
where rownum <= 20;

复制代码

但有时会发现找出来的sql特别简单，怎么看都不像能占用几十几百G temp表空间的样子。
为什么从v$sort_usage找到的sql_id有时是错的？应该怎么找正确的sql_id？

关于第一个问题，查找了网上的一些文章，发现v$sort_usage的sql_id并不对应v$session的sql_id，它对应的是v$session的prev_sql_id有一个V$FIXED_VIEW_DEFINITION视图可以查看固定视图定义，查询可以看到v$sort_usage定义如下

select * from V$FIXED_VIEW_DEFINITION where view_name='V$SORT_USAGE';
--输出如下
select USERNAME , "USER" , SESSION_ADDR , SESSION_NUM , SQLADDR , SQLHASH, SQL_ID, TABLESPACE , CONTENTS , SEGTYPE , SEGFILE# , SEGBLK# ,EXTENTS , BLOCKS , SEGRFNO# from GV$SORT_USAGE where inst_id = USERENV('Instance');

复制代码

可以看到它来自GV$SORT_USAGE，再查看GV$SORT_USAGE的定义

select * from V$FIXED_VIEW_DEFINITION where view_name='GV$SORT_USAGE';
--输出如下
select x$ktsso.inst_id,
username,
username,
ktssoses,
ktssosno,
prev_sql_addr,
prev_hash_value,
prev_sql_id, ----------- 注意这个
ktssotsn,
decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'),
decode(ktssosegt,
1,
'SORT',
2,
'HASH',
3,
'DATA',
4,
'INDEX',
5,
'LOB_DATA',
6,
'LOB_INDEX',
'UNDEFINED'),
ktssofno,
ktssobno,
ktssoexts,
ktssoblks,
ktssorfno
from x$ktsso, v$session
where ktssoses = v$session.saddr
and ktssosno = v$session.serial#;
-- 12.2.0.1
SELECT so.inst_id,
username,
username,
ktssoses,
ktssosno,
prev_sql_addr,
prev_hash_value,
prev_sql_id,
ts.name,
DECODE (ktssotbst, 1, 'TEMPORARY', 'PERMANENT'),
DECODE (ktssosegt,
1, 'SORT',
2, 'HASH',
3, 'DATA',
4, 'INDEX',
5, 'LOB_DATA',
6, 'LOB_INDEX',
7, 'TEMP_UNDO',
'UNDEFINED'),
ktssofno,
ktssobno,
ktssoexts,
ktssoblks,
ktssorfno,
ktssotsnum,
so.con_id,
ktssosqlid
FROM x$ktsso so, v$session, v$tablespace ts
WHERE so.ktssotsnum = ts.ts#
AND so.con_id = ts.con_id
AND ktssoses = v$session.saddr
AND ktssosno = v$session.serial#

复制代码

可以看到v$sort_usage的sql_id对应的是v$session的prev_sql_id。如果当前sql是最新正在执行的语句，查出来的结果是对的；如果过后执行了其它sql，看到就是错误的语句。
真正查询消耗高temp表空间的sql语句应该为

SELECT S.sid,
S.serial# sid_serial,
S.username,
S.osuser,
P.spid,
S.module,
S.program,
SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid,
S.serial#,
S.username,
S.osuser,
P.spid,
S.module,
S.program,
TBS.block_size,
T.tablespace
HAVING SUM(blocks) > 10000 -- 80MB
ORDER BY sid_serial;
--将sid代入下面sql查出真正的sql_id
select ktssosqlid from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial# and v$session.sid=2111;

复制代码

或者直接利用下面的sql

select k.inst_id "INST_ID",
ktssoses "SADDR",
sid,
ktssosno "SERIAL#",
username "USERNAME",
osuser "OSUSER",
ktssosqlid "SQL_ID",
ktssotsn "TABLESPACE",
decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",
--注意在12c的v$sort_usage定义中TABLESPACE和CONTENTS已经发生变化了。
decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',
5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",
ktssofno "SEGFILE#",
ktssobno "SEGBLK#",
ktssoexts "EXTENTS",
ktssoblks "BLOCKS",
round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",
ktssorfno "SEGRFNO#"
from x$ktsso k, v$session s,
(select value from v$parameter where name='db_block_size') p
where ktssoses = s.saddr
and ktssosno = s.serial#;

复制代码

v$sort_usage中的SEGTYPE列含义

SORT：SQL排序使用的临时段，包括order by、group by、union、distinct、窗口函数、建索引等产生的排序。
DATA：临时表(Global Temporary Table)存储数据使用的段。
INDEX：临时表上建的索引使用的段。
HASH：hash算法所使用的临时段。
LOB_DATA和LOB_INDEX：临时LOB使用的临时段。
另外可以通过以下语句查看一段时间前占用temp表空间高的sql

select *
from (select t.sample_time,
s.PARSING_SCHEMA_NAME,
t.sql_id,
t.sql_child_number as sql_child,
round(t.temp_space_allocated / 1024 / 1024 / 1024, 2) || ' G' as temp_used,
round(t.temp_space_allocated /
(select sum(decode(d.autoextensible, 'YES', d.maxbytes, d.bytes))
from dba_temp_files d),
2) * 100 || ' %' as temp_pct,
t.program,
t.module,
s.SQL_TEXT
from v$active_session_history t, v$sql s
where t.sample_time > to_date('2019-07-15 22:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.sample_time < to_date('2019-07-15 23:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.temp_space_allocated is not null
and t.sql_id = s.SQL_ID
order by t.temp_space_allocated desc)
where rownum < 50
order by temp_used desc;

复制代码

帐号		自动登录	找回密码
密码			注册

[参考文档] Oracle 11g 查看TEMP实际占用