OraFAQ Forum: Performance Tuning » Digging out the Wait events.

Home » RDBMS Server » Performance Tuning » Digging out the Wait events.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Digging out the Wait events. [message #598574]

Tue, 15 October 2013 14:48

VIP2013
Messages: 91
Registered: June 2013

Member

I am using 11.2.0.3.0 version of oracle. As told by the DBAs that two days back the system realised high wait_class i.e 'Application' wait, and its a lot high than regular value as per our system and we need to dig it down, to avoid any future issue. now using below query , i found that the high wait time due to wait class 'Application' is actually belongs to particular event 'SQL*Net break/reset to client', and the sample time was '9 AM' morning.

select time, 
           round(max(case when event = 'SQL*Net break/reset to client' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end) ,2) SQL_break_reset_client, 
           round(max(case when event = 'Wait for Table Lock' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) Wait_for_Table_Lock ,
           round(max(case when event = 'enq: KO - fast object checkpoint' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) enq_KO_fast_object_checkpoint ,
           round(max(case when event = 'enq: RO - fast object reuse' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) enq_RO_fast_object_reuse ,
           round(max(case when event = 'enq: TM - contention' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) enq_TM_contention ,
           round(max(case when event = 'enq: TX - row lock contention' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) enq_TX_row_lock_contention ,
          round( max(case when event = 'enq: UL - contention' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2)  enq_UL_contention 
   from 
   ( 
       select sn.begin_interval_time- time, 
               e.event_name event, 
               e.wait_class wait_class,
               e.total_waits - lag(e.total_waits) over(partition by e.event_name order by e.snap_id) total_waits_delta, 
               e.time_waited_micro - lag(e.time_waited_micro) over(partition by e.event_name order by e.snap_id) time_waited_delta 
       from DBA_HIST_SYSTEM_EVENT e, 
             DBA_HIST_SNAPSHOT sn 
       where e.snap_id = sn.snap_id 
       AND sn.end_interval_time >= TO_DATE (TRIM ('14-Oct-2013 8:30:00'), 'dd-mon-yyyy hh24:mi:ss')
     AND sn.begin_interval_time <= TO_DATE (TRIM ('14-oct-2013 9:00:00 '), 'dd-mon-yyyy hh24:mi:ss')
      	and wait_class in ('Application')   
   ) 
   group by time 
   order by time desc;

Now i want to track it down further to the 'session/sql query/application' level resulting into such high value of wait time. so i queried, dba_hist_active_session_history, for the same sample duration (8.30 to 9 am) having event 'SQL*Net break/reset to client', and got two sessions(123,154) and their serial# , but there i got one sql_id(3ahgrey10ogh i.e a 'SELECT' query) specific to one session(123) but for other(125) no sqlid, and also SUM (wait_time + time_waited) is showing '0'.

Then i just removed the sampletime filter from the below query and observed that the same 'same session(123)+session serial#' was activated since 4 days back and was experiencing same waitevent 'SQL*Net break/reset to client', (it was having normal wait event 'db file sequential read' for sometime then after it went for this event ). So now, i just stuck and could not able to move further, could you please help me to dig down the issue, that is finding actual culprit.

SELECT user_id,
         PROGRAM,
         machine,
         session_id,
         SESSION_SERIAL#,
         sample_time-7/24,
         session_state,
         event,
         wait_time / 1000 wait_time_sec,
         time_waited / 1000 time_waited_sec,
         sql_id,
         blocking_session,
         BLOCKING_SESSION_SERIAL#,
         BLOCKING_SESSION_STATUS,
         BLOCKING_HANGCHAIN_INFO,
         BLOCKING_HANGCHAIN_INFO,
         (SELECT sql_text
            FROM dba_hist_sqltext
           WHERE sql_id = DBA_HIST_ACTIVE_SESS_HISTORY.sql_id AND ROWNUM < 2),
         (SELECT object_name
            FROM dba_objects
           WHERE object_id = DBA_HIST_ACTIVE_SESS_HISTORY.current_obj#
                 AND ROWNUM < 2),
         sql_child_number CH#
    FROM DBA_HIST_ACTIVE_SESS_HISTORY
   WHERE  session_id = 123
         AND session_serial# = 4563
ORDER BY sample_time ;