Finding query with high temp space usage using ASH views

We are having challenges with queries that spend most of their time on these waits:

enq: TS – contention

This wait indicates that a query on one instance of a RAC system is causing its instance to add temp space to its sort segment and remove space from another instance’s sort segment.  For some reason this process is extremely slow and totally bogs down any query waiting on this type of wait.  I worked an SR with Oracle on this issue and Oracle development claims this is not a bug.  I’ve duplicated the problem on both 10.2 and 11.2 RAC.

Anyway, so now we periodically see queries spending most of their time on this wait and this occurs when some other query is eating up all the temp space or has done so recently.  So how do I go back in time and figure out what query was using temp space?  If I wanted to see the session ids of the current users of temp space on a RAC system I would just join gv$tempseg_usage to gv$session like this:

select se.inst_id,se.sid,sum(tu.blocks)*8192
gv$tempseg_usage tu,
gv$session se
tu.inst_id=se.inst_id and
tu.session_addr=se.saddr and
group by se.inst_id,se.sid
order by se.inst_id,se.sid;

This assumes an 8K blocksize.  But to go back in time you can use the TEMP_SPACE_ALLOCATED column of V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY to identify a query that consumed a lot of temp space.

Here is an example:

select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig 
sample_time > sysdate-2 and 
TEMP_SPACE_ALLOCATED > (50*1024*1024*1024) 
group by sql_id order by sql_id;

This gives the sql_id and maximum allocated temp space of any queries that ran in the past two days and exceeded 50 gigabytes of temp space.

This is a great feature of these ASH views.  Now we can go back in time and find the queries that were using all the temp space and tune them.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

8 Responses to Finding query with high temp space usage using ASH views

  1. Raj says:

    Hi Bobby,

    First of all thank for posting this 🙂

    We are facing the Temp issue frequently as after temp filled up we can see the alert log .

    We couldn’t catch which SID and SQLText cause this issue .

    Note : Current active session we can able to see who is culprit but It was happened three hours back mean how we will find out that culprit .

    We tried the above query but couldn’t. Please if you know the exact query share me :

    Many thanks in advance for your help.


    • Bobby says:

      The query only works on 11.2 or later. If you have an older database you get this error: ORA-00904: “TEMP_SPACE_ALLOCATED”: invalid identifier. Also, if you don’t get any rows returned but don’t get an error try reducing this number: (50*1024*1024*1024) – that is 50 gigabytes and you may want something smaller. I.e. 2 gigabytes would be (2*1024*1024*1024) . Thanks for your comment.

  2. Chuck Davis says:

    Thanks for the post. This helped us track down the session and statement that gobble up all of temp during a particular window of time.

  3. Venkat says:

    Thanks for the post Bobby. I have a question. If we need to find out how much temp space is taken by a query. For example.. I write a new query and wanted to test how much temp space it is going to use…

    I know we can use your query above with time.. but is there any way…Like joining your query with V$sql where sql_text like ‘my new query’? Please suggest

    • Bobby says:


      Thank you for your comment but I don’t really have a query like that. I know that with a problematic query I’ve monitored the available space in the sort segment but I can’t find a script for that. The thing is that depending on where the query is in the execution of its plan temp space usage can go up or down. I did a quick google search and this page seems to have some scripts that you can use to monitor the temp space usage of an active session:

      – Bobby

  4. Jyothish says:


    Thank you very much for your query.

Leave a Reply