快速定位临时表空间（PGA）占用过高的 SQL_ID

--指定时间1小时内的占用temp过高的sql

set pages 300 lin 120
col SQL_OPNAME for a20
col MODULE for a40 trunc
col sql_id for a13


select * from (
select user_id,sql_id,sql_exec_id,sql_opname,module,round(sum(TEMP_SPACE_ALLOCATED)/1024/1024/1024,1) g
from dba_hist_active_sess_history
where sample_time>sysdate-1 and sample_time and TEMP_SPACE_ALLOCATED is not null
group by user_id,sql_id,sql_exec_id,sql_opname,module
order by g desc) where rownum<11;

建议将上述内容存为 high_temp_space.sql，调用时：
SQL> @high_temp_space sysdate
也可以将 TEMP_SPACE_ALLOCATED替换为PGA_ALLOCATED，来查看pga占用过高的sql_id.

根据查出来sql_id，可以看出对应的sql：
col sql_text for a80
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;