top of page
Historical SQL Wait Events

select x.session_id,x.SESSION_SERIAL#,x.sql_id||'::'||x.SQL_PLAN_HASH_VALUE,
min(x.sample_time), max(x.sample_time),x.event
,count(*) , sum(x.time_waited)/1000000 from
(select snap_id,SESSION_ID,SESSION_SERIAL#,sql_id,SQL_PLAN_HASH_VALUE,
event,sample_time,time_waited,p1text,p1,p2text,p2,p3text,p3 from dba_hist_active_sess_history where
sql_id = '87gaftwrm2h68'
) x,
(select snap_id from dba_hist_snapshot
        where begin_interval_time >= sysdate - 1
group by snap_id ) s
where s.snap_id = x.snap_id
group by x.session_id,x.SESSION_SERIAL#,x.sql_id||'::'||x.SQL_PLAN_HASH_VALUE,x.event
order by x.session_id,x.SESSION_SERIAL#,x.sql_id||'::'||x.SQL_PLAN_HASH_VALUE,x.event

Download Speedway to plot the historical wait event information as per below.

Complete Database Performance Management
bottom of page