v$sort_usage是定位临时表空间暴涨的第一手线索，需关联v$session和v$sql定位sql；若为空则用v$active_session_history查历史高开销sql；再通过dbms_xplan验证执行计划中tempspc及排序/哈希操作。
查 v$sort_usage 关联活跃会话
临时表空间暴涨时，v$sort_usage 是第一手线索——它直接记录了哪些会话正在使用 TEMP 段、用了多少、属于哪种操作。但单看这个视图只能知道“谁在用”，必须和 v$session 关联才能定位到具体 SQL。
执行以下查询（需 DBA 权限）：
SELECT s.sid, s.serial#, s.username, s.osuser, s.program,
su.tablespace, su.segtype, su.blocks * 8 / 1024 size_mb,
q.sql_id, q.sql_text
FROM v$sort_usage su
JOIN v$session s ON su.session_addr = s.saddr
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE su.tablespace = 'TEMP' -- 替换为你的临时表空间名
ORDER BY su.blocks DESC;
-
segtype = 'SORT'表示排序溢出；'HASH'表示哈希连接/聚合溢出；'LOB_TEMPORARY'则多见于大字段处理 -
blocks是 Oracle 数据块数，乘以 8KB（默认块大小）可得近似 MB 数；若值持续增长，说明该 SQL 正在实时写入 TEMP - 注意
v$sql可能为空：SQL 已执行完或被共享池老化淘汰，此时要结合v$active_session_history回溯
用 v$active_session_history 抓历史高开销 SQL
如果 v$sort_usage 返回空，或问题只在夜间批处理时段出现，说明当前无活跃溢出，但历史 SQL 才是元凶。这时 v$active_session_history（ASH）就是关键证据源——它每秒采样一次活动会话，保留约 1 小时（默认），且包含等待事件与执行计划摘要。
运行以下语句查最近 60 分钟内触发 TEMP 溢出最多的 SQL：
SELECT sql_id, sql_plan_hash_value,
COUNT(*) cnt,
ROUND(AVG(time_waited)/1000, 2) avg_wait_sec,
MAX(sample_time) last_seen
FROM v$active_session_history
WHERE event = 'direct path write temp'
OR event LIKE '%temp%'
OR p1text = 'tablespace number'
GROUP BY sql_id, sql_plan_hash_value
ORDER BY cnt DESC FETCH FIRST 5 ROWS ONLY;
-
event = 'direct path write temp'是最明确的磁盘写 TEMP 信号，比sort write更可靠 - 返回的
sql_id可用于进一步查v$sql或dba_hist_sqltext获取完整语句 - 若数据库未启用 ASH（如 Standard Edition），则改用
dba_hist_active_sess_history（需 AWR 快照权限）
确认是否真由排序/哈希操作驱动
看到某条 SQL 占用大量 TEMP，不能直接断定是它写的——有可能只是“背锅”，真正膨胀的是其子查询或物化视图。必须验证执行计划中是否存在磁盘级操作。
对可疑 sql_id 执行：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'ALLSTATS LAST'));
- 重点找
Operation列含SORT、HASH JOIN、WINDOW SORT的行，并检查A-Rows（实际返回行数）是否远超E-Rows（预估行数） - 若某行显示
TempSpc列有数值（单位字节），说明该步骤确实申请了临时空间；值越大，越可能是瓶颈点 - 特别警惕子查询里带
ORDER BY+ 外层LIMIT的组合：Oracle 会先全量排序再截断，哪怕只取 10 行
排除 SYSTEM/SYSAUX 误用与递归 SQL 干扰
有时 TEMP 爆涨并非业务 SQL 导致，而是系统自身行为：比如大量硬解析触发数据字典递归查询，或 DDL 操作隐式使用 SYSTEM 表空间的临时段。这类情况不会出现在 v$sort_usage 的 username 字段里，而常显示为 NULL 或 SYS。
- 检查
v$sort_usage中username IS NULL的记录：大概率是递归 SQL 或后台进程（如 SMON、PMON）在清理或维护 - 运行
SELECT * FROM v$session WHERE username IS NULL AND status = 'ACTIVE';，观察program和sql_id是否指向ORA$AUTOTASK或DBMS_SCHEDULER - 若发现大量
PARSING IN CURSOR类型的递归调用，应检查绑定变量使用率（v$sql中force_matching_signature重复度）和 shared_pool 状态
临时表空间问题最难缠的地方在于：它不报错就无声膨胀，一报错就是连环雪崩。真正有效的排查不是盯着“谁占得多”，而是盯住“谁正在写”“写了什么”“为什么非写不可”。很多看似复杂的 SQL，删掉一个没用的 ORDER BY 或补上一个缺失索引，就能让 TEMP 使用量从 GB 级降到 MB 级——关键在动手前先看清执行计划里的那一行 TempSpc 值。
