Temp usage by session

by Granville Bonyata on March 30, 2018

A useful query for getting temp usage by session:

SELECT s.process, s.machine, s.username,
       s.inst_id, s.sid, s.serial#,
       s.sql_id, s.event,s.seconds_in_wait,
       s.module, s.action, temp.temp_gb,
       s.blocking_session, s.blocking_instance, 
       s.program, s.logon_time 
  FROM
(
SELECT inst_id,
       session_adr,
       session_num, 
       ts#, username, sql_id, 
       sum(blocks*8192)/(1024*1024*1024) temp_gb
  FROM gv$sort_usage  
 GROUP BY inst_id, session_addr, session_num, ts#, username, sql_id
) temp,
  gv$session s
WHERE s.inst_id = temp.inst_id
  AND s.saddr = temp.session_addr
ORDER BY temp_gb desc
/

Previous post:

Next post: