Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

file 56 lines (47 sloc) 1.388 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56


/* wait event latency averaged over each hour

output looks like

BTIME AVG_MS
--------------- ----------
20-JUL-11 06:00 5.854
20-JUL-11 07:00 4.116
20-JUL-11 08:00 21.158
20-JUL-11 09:02 5.591
20-JUL-11 10:00 4.116
20-JUL-11 11:00 6.248
20-JUL-11 12:00 23.634
20-JUL-11 13:00 22.529
20-JUL-11 14:00 21.62
20-JUL-11 15:00 18.038
20-JUL-11 16:00 23.127

*/


define v_dbid=NULL;
select &v_dbid from dual;
col f_dbid new_value v_dbid
select &database_id f_dbid from dual;
select &v_dbid from dual;
select nvl(&v_dbid,dbid) f_dbid from v$database;
select &v_dbid from dual;


select
       btime,
       round((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),3) avg_ms
from (
select
       to_char(s.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI') btime,
       total_waits count_end,
       time_waited_micro/1000 time_ms_end,
       Lag (e.time_waited_micro/1000)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,
       Lag (e.total_waits)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg
from
       DBA_HIST_SYSTEM_EVENT e,
       DBA_HIST_SNAPSHOT s
where
         s.snap_id=e.snap_id
   and e.event_name like '%&like_event%'
   and e.dbid=&v_dbid
   and s.dbid=&v_dbid
order by begin_interval_time
)
order by btime
/
Something went wrong with that request. Please try again.