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