event_histogram_from_awr.sql

set linesize 180
set pagesize 200
col BEGIN_INTERVAL_TIME format A30
col instance_number format 99 head Inst
break on  BEGIN_INTERVAL_TIME
/*
define event_name="gcs log flush sync"
define event_name="log file sync"
*/
select snaps.begin_interval_time, snaps.instance_number,snaps.snap_id,hist.event_name , hist.wait_time_milli, hist.wait_count 
  from dba_hist_event_histogram hist, dba_hist_snapshot snaps 
 where snaps.snap_id = hist.snap_id and snaps.instance_number = hist.instance_number and 
   snaps.begin_interval_time > sysdate - 1/12   -- sysdate - 1/12 -> display AWR reports from the last 2 hours
   AND hist.event_name = lower ('&&event_name') 
 order by  snaps.snap_id , snaps.instance_number, wait_time_milli;

Leave a Reply

Your email address will not be published. Required fields are marked *