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 from gv$tempseg_usage tu, gv$session se where tu.inst_id=se.inst_id and tu.session_addr=se.saddr and tu.session_num=se.serial# 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 from DBA_HIST_ACTIVE_SESS_HISTORY where 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.