DBA_HIST_SYSTEM_EVENT


SQL> desc dba_hist_system_event
 Name                          Null?    Type
 ----------------------------- -------- ---------------
 SNAP_ID                       NOT NULL NUMBER
 DBID                          NOT NULL NUMBER
 INSTANCE_NUMBER               NOT NULL NUMBER
 EVENT_ID                      NOT NULL NUMBER
 EVENT_NAME                    NOT NULL VARCHAR2(64)
 WAIT_CLASS_ID                          NUMBER
 WAIT_CLASS                             VARCHAR2(64)
 TOTAL_WAITS                            NUMBER
 TOTAL_TIMEOUTS                         NUMBER
 TIME_WAITED_MICRO                      NUMBER
 TOTAL_WAITS_FG                         NUMBER
 TOTAL_TIMEOUTS_FG                      NUMBER
 TIME_WAITED_MICRO_FG                   NUMBER





set pagesize 100
col event_name format a30
col avg_ms format 99999.99
col ct format 999,999,999
select
       btime, event_name,
       (time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0) avg_ms,
       (count_end-count_beg) ct
from (
select
       e.event_name,
       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.wait_class in ( 'User I/O', 'System I/O')
   -- and e.event_name in (  'db file sequential read',
   --                      'db file scattered read',
   --                      'db file parallel read',
   --                      'direct path read',
   --                      'direct path read temp',
   --                      'direct path write',
   --                     'direct path write temp')
   and e.event_name in (  'db file scattered read')
   and e.dbid=&DBID
   and e.dbid=s.dbid
order by e.event_name, begin_interval_time
)
where (count_end-count_beg) > 0
order by event_name,btime
/




Comments