Oracle Average Physical Disk Reads History

0
WITH sysstat AS
 (select sn.begin_interval_time begin_interval_time,
         sn.end_interval_time end_interval_time,
         ss.stat_name stat_name,
         ss.value e_value,
         lag(ss.value, 1) over(order by ss.snap_id) b_value
    from dba_hist_sysstat ss, dba_hist_snapshot sn
   where trunc(sn.begin_interval_time) >= sysdate-7
     and ss.snap_id = sn.snap_id
     and ss.dbid = sn.dbid
     and ss.instance_number = sn.instance_number
     and ss.dbid = (select dbid from v$database)
     and ss.instance_number = (select instance_number from v$instance)
     and ss.stat_name = 'physical reads')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy[hh24_mi-') || to_char(END_INTERVAL_TIME, '-hh24_mi]') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/

Sample Result:

08/05/14[23_00–00_00] physical reads 80
08/06/14[00_00–01_00] physical reads 1600
08/06/14[01_00–02_00] physical reads 40
08/06/14[02_00–03_00] physical reads 3
08/06/14[03_00–04_00] physical reads 2
08/06/14[04_00–05_00] physical reads 30
08/06/14[05_00–06_00] physical reads 30
08/06/14[06_00–07_00] physical reads 700
08/06/14[07_00–08_00] physical reads 100
08/06/14[08_00–09_00] physical reads 900
08/06/14[09_00–10_01] physical reads 2100
08/06/14[10_01–11_00] physical reads 4300
08/06/14[11_00–12_00] physical reads 6100
08/06/14[12_00–13_00] physical reads 3800
08/06/14[13_00–14_00] physical reads 1800
08/06/14[14_00–15_00] physical reads 5900
Share.

About Author

hahahahahaha......nothing here

發表迴響

error: Content is protected !!