Who is using TEMP and what are they doing?

TEMP can be a difficult resource to manage in mixed workload environments. You might have sufficient space to run a few big sorts or aggregates; or maybe you have enough space to run lots of concurrent sessions each of which consumes a little bit. But then you get the occasional overlap and there just isn’t enough room for everyone. It’s easy to say “just add more disk” but that’s not always the right answer and sometimes not feasible. If you don’t have the storage space available you can’t add it.

So, when this happens it’s useful to be able to query the TEMP in current use and determine which sessions and activity is consuming it. I like to use a query like this:

SELECT inst_id,
       username,
       sid,
       segment_type,
       TO_CHAR(segment_bytes, '999,999,999,999') segment_bytes,
       TO_CHAR(session_temp_bytes, '999,999,999,999') session_bytes,
       tablespace,
       TO_CHAR(tablespace_bytes, '999,999,999,999') tablespace_bytes,
       sql_id,
       event,
       --sql_text,
       --sql_fulltext,
       parent_sid,
       parent_instid
  FROM (  SELECT s.inst_id,
                 su.username,
                 s.sid,
                 CASE WHEN ROUND(BITAND(s.ownerid, 16711680) / 65536) != 255 THEN ROUND(BITAND(s.ownerid, 65535)) END
                     parent_sid,
                 NULLIF(ROUND(BITAND(s.ownerid, 16711680) / 65536), 255)
                     parent_instid,
                 su.tablespace,
                 su.segtype
                     segment_type,
                 su.blocks * t.block_size
                     segment_bytes,
                 SUM(su.blocks) OVER (PARTITION BY s.inst_id, s.sid) * t.block_size
                     session_temp_bytes,
                 SUM(su.blocks) OVER (PARTITION BY su.tablespace) * t.block_size
                     tablespace_bytes,
                 s.sql_id,
                 s.event,
                 (SELECT sql_text
                    FROM gv$sqlarea sq
                   WHERE sq.inst_id = s.inst_id AND sq.sql_id = s.sql_id)
                     sql_text,
                 (SELECT sql_fulltext
                    FROM gv$sqlarea sq
                   WHERE sq.inst_id = s.inst_id AND sq.sql_id = s.sql_id)
                     sql_fulltext
            FROM gv$sort_usage su, dba_tablespaces t, gv$session s
           WHERE t.tablespace_name = su.tablespace AND su.inst_id = s.inst_id AND su.session_addr = s.saddr
        ORDER BY session_temp_bytes DESC,
                 sid,
                 segment_bytes DESC,
                 segtype);

A few of the features I use the most…

• The bytes consumed within a single segment, an entire session, and the tablespace as a whole are listed.
• In the event of parallel child threads it can be helpful to track the lineage so parent session and instance are parsed out from the
• The sql_text and sql_fulltext columns are commented out. That’s for two reasons, often you don’t need it, so eliminating them makes the output smaller; and the optimizer won’t execute the sub-queries if they are commented out of the final results, thus making the query faster as well.

I hope you find it helpful.
Questions and comments, as always, are welcome.