10g – DBA_HIST_SYSSTAT

select a.snap_id snap_id, 
 /* lag( snap_id,1,0) over (partition by dbid order by snap_id desc ) nextsnap_id */
 to_char (b.begin_interval_time, 'DDMMYYYY HH24:MI') snap_time ,
 trunc((lag( a.value,1,0) over (partition by a.dbid order by a.snap_id desc ) - a.value)/ ( 60*20) ) xVALUE
 FROM DBA_HIST_SYSSTAT a ,DBA_HIST_SNAPSHOT b
 WHERE 
 a.snap_id >= 14000-- begin snap
 -- AND a.snap_id <= 15911-- end snap
 AND a.stat_name ='bytes sent via SQL*Net to dblink' 
 AND a.snap_id = b.snap_id
 order by a.snap_id desc;

select a.snap_id snap_id, 
 /* lag( snap_id,1,0) over (partition by dbid order by snap_id desc ) nextsnap_id */
 to_char (b.begin_interval_time, 'DDMMYYYY HH24:MI') snap_time ,
 trunc((lag( a.total_waits,1,0) over (partition by a.dbid order by a.snap_id desc ) - a.total_waits)/(60*20) ) xVALUE
 FROM DBA_HIST_SYSTEM_EVENT a ,DBA_HIST_SNAPSHOT b
 WHERE 
 a.snap_id >= 17952-- begin snap
 AND a.snap_id <= 200000 -- end snap
 AND a.event_name = 'log file sync'
--AND a.event_name = 'db file sequential read'
 AND a.snap_id = b.snap_id
 order by a.snap_id desc;

select a.snap_id snap_id, 
 /* lag( snap_id,1,0) over (partition by dbid order by snap_id desc ) nextsnap_id */
 to_char (b.begin_interval_time, 'DDMMYYYY HH24:MI') snap_time ,
 trunc((lag( a.time_waited_micro,1,0) over (partition by a.dbid order by a.snap_id desc ) - a.time_waited_micro)/(60*20) ) xVALUE
 FROM DBA_HIST_SYSTEM_EVENT a ,DBA_HIST_SNAPSHOT b
 WHERE 
 a.event_name = 'log file sync'
 AND a.snap_id = b.snap_id
 order by a.snap_id desc;

Advertisements