Check the reason for Wait for Particular SQL

SELECT  distinct sh.sql_id, sh.blocking_session,sh.blocking_session_serial#,
sh.user_id,s.sql_text,sh.module
FROM  V$ACTIVE_SESSION_HISTORY sh, v$sql s
where sh.sql_id=s.sql_id
and blocking_session is not null
and sh.user_id <> 0
and sh.sample_time between to_date('21-05-2014 00:00', 'dd-mm-yyyy hh24:mi')
and to_date('22-05-2014 00:00', 'dd-mm-yyyy hh24:mi');


-- Get the sql_id and use it to check the reason for the wait by checking the event

SELECT NVL(sh.event, 'ON CPU') AS event,
       COUNT(*)*10 AS total_wait_time
FROM   dba_hist_active_sess_history sh
WHERE  sh.sample_time > SYSDATE - 1
and sh.SQL_ID='6aavn12v9td4f'
and sh.sample_time between to_date('21-05-2014 00:00', 'dd-mm-yyyy hh24:mi')
and to_date('22-05-2014 00:00', 'dd-mm-yyyy hh24:mi')
GROUP BY sh.event
ORDER BY total_wait_time DESC;

EVENT                               TOTAL_WAIT_TIME
----------------------------------- ---------------
db file sequential read                         470
ON CPU                                          370
read by other session                            40
enq: TX - index contention                       30

Comments