Sunday 26 January 2014

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





No comments:

Post a Comment