Lack of Temp space causing performance issues

select  sid,serial#,state,seconds_in_wait,event,event#,blocking_session,program FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and program = 'SQL Developer'


select session_id,object_name, object_type,CURRENT_OBJ#, event, sum(time_waited) from v$active_session_history, dba_objects
where session_id in
( select  sid FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and blocking_session is null )
and object_id = CURRENT_OBJ#
group by object_name, object_type,CURRENT_OBJ#, event,session_id
order by 6 desc


select * from all_objects
where object_id =87968




 SELECT p1 file#, p2 block#, p3 class#,EVENT
 FROM v$session_wait where sid in (
 select  sid FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and blocking_session is null)
and upper(event) like '%DIRECT%READ%TEMP%'
order by event


Session wait is important because it tells you what your session is blocked on









 SELECT relative_fno, owner, segment_name, segment_type
 FROM dba_extents
 WHERE file_id in 
  ( SELECT p1
 FROM v$session_wait where sid in (
 select  sid FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and blocking_session is null)
and upper(event) like '%DIRECT%READ%TEMP%'
 ) and 1632256 between block_id and block_id+1632256-1



 select DISTINCT  from dba_extents
 WHERE SEGMENT_NAME = 'TEMP'


 where UPPER(SEGMENT_NAME) LIKE '%TEMP%'


 where file_id = 1509








select * from V$TEMPSEG_USAGE-- gives which queries are using temp segemnt


select * from  v$sort_usage


1. Ran the trend chart query in stage1
2. Checked the v$active_session_history table for what is the event that the query is waiting on
3. 'direct path read temp' --- This is the even that the query is waiting for
4. Screenshot for total time waited on this event below
5. What it means ??--- Query is pulling data from temp segment to PGA. (by passing SGA buffer cache). Most of the time query is waiting for pulling data from temp, which means i/o is not able to supply data to PGA at a good rate
6. Solution – Check for i/o -- is the server using RAID (mostly yes) .So why is it not able to supply the data at the rate required from temp


Number of records that need to be pulled by Quality trend chart for doing calculation is 9,993153- ( approx 10 million). There is a condition in the query for pivoting which is multiplying 10 million by 24 giving us 239 million.


To perform operation on 239 million query takes some time.




Below query gives the space in TEMP in GB being used by the query


select
   a.username,
   sum(srt.blocks * 8 / 1024) "Used Space in MB",
   sum(srt.blocks * 8 / 1024)/1024 "Used Space in GB"
from
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
   and sid in ( select  sid FROM v$SESSION WHERE OSUSER='ORAKK')
   group by a.username

















select * from V$TEMPSEG_USAGE


Get the block id from the tempseg_usage table and see who is using it


 


How to check on what event how much time is being taken. Useful to check how much time oracle takes to write to database


select  
   se.event,
   sum(se.total_waits),
   sum(se.total_timeouts),
   sum(se.time_waited/100) time_waited
from        
   v$session_event se,
   v$session       sess
where
   sess.sid = se.sid
   and sess.sid in  ( select  sid FROM v$SESSION
WHERE OSUSER='ABC'
AND USERNAME ='ORAKK'
and blocking_session is null )
group by se.event
order by 4 desc