根据AWR快照查看等待事件的变化趋势。不记得是哪里看到的了。非原创。

WITH system_event AS
 (select sn.begin_interval_time begin_interval_time,
         sn.end_interval_time end_interval_time,
         se.event_name event_name,
         se.total_waits e_total_waits,
         lag(se.total_waits, 1) over(order by se.snap_id) b_total_waits,
         se.total_timeouts e_total_timeouts,
         lag(se.total_timeouts, 1) over(order by se.snap_id) b_total_timeouts,
         se.time_waited_micro e_time_waited_micro,
         lag(se.time_waited_micro, 1) over(order by se.snap_id) b_time_waited_micro
    from dba_hist_system_event se, dba_hist_snapshot sn
   where trunc(sn.begin_interval_time) = trunc(sysdate-5)
     and se.snap_id = sn.snap_id
     and se.dbid = sn.dbid
     and se.instance_number = sn.instance_number
     and se.dbid = (select dbid from v$database)
     and se.instance_number = (select instance_number from v$instance)
     and se.event_name = 'db file sequential read') select to_char(se1.BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char (se1.END_INTERVAL_TIME, '_hh24_mi') date_time,se1.event_name,se1.e_total_waits - nvl(se1.b_total_waits,0) waits,(se1.e_time_waited_micro - nvl(se1.b_time_waited_micro,0)) / 1000000 time,((se1.e_time_waited_micro - nvl(se1.b_time_waited_micro,0)) / 1000) / (se1.e_total_waits - nvl(se1.b_total_waits,0)) avg_wait_ms from system_event se1 where(se1.e_total_waits - nvl(se1.b_total_waits,0)) > 0 and nvl(se1.b_total_waits,0) > 0