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 |
