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