dba_hist_active_sess_history

SQLs doing sorts and consuming TEMP between two snapshots in an instance

select SQL_ID, TEMP_SPACE_ALLOCATED/1024/1024/1024,SQL_PLAN_HASH_VALUE
from dba_hist_active_sess_history
where snap_id &gt;=snpid and snap_id &lt;=snapid and TEMP_SPACE_ALLOCATED &gt; 0
order by TEMP_SPACE_ALLOCATED;

ON CPU SQLs between two snaps

set linesize 200
col start_time for a25
col end_time for a25
col SQL_execstart for a15
set pagesize 50
col SAMPLE_TIME for a25
select session_id,sql_id,SQL_CHILD_NUMBER,to_char(sql_exec_start,'DDMON Hh24:MI:SS') SQL_execstart,SQL_EXEC_ID,SAMPLE_TIME,SESSION_STATE,time_waited,IN_PARSE,IN_HARD_PARSE,IN_BIND,IN_CURSOR_CLOSE,IN_PLSQL_COMPILATION,IN_PLSQL_RPC,CURRENT_OBJ#
from dba_hist_active_sess_history
WHERE session_state='ON CPU' and SNAP_ID >=start_snap and SNAP_ID <=end_snap
order by SAMPLE_TIME;