Scenario: You have changed your back-end storage hardware (SAN array or SATA disk storage) and you want to see a historical overview of sequential read times of your database.
It’s possible to query the Oracle AWR data (provided you have paid for the license), to provide a historical list of sequential read times acccording to the snapshots taken by AWR.
You are obviously limited to the amount of data retained by AWR and the frequency of the AWR snapshots.
set linesize 400 pagesize 400
SELECT event_start.snap_id,
to_char(snap.begin_interval_time,'DD-MM-YY HH24:MI') as begin_time,
to_char(snap.end_interval_time,'HH24:MI') as end_time,
round(decode(
(event_end.total_waits - nvl(event_start.total_waits, 0)),0, to_number(NULL),
((event_end.time_waited_micro - nvl(event_start.time_waited_micro,0))/1000) / (event_end.total_waits - nvl(event_start.total_waits,0))
),0) avgwait,
event_end.event_name event_name,
(event_end.time_waited_micro - nvl(event_start.time_waited_micro,0)/1000000) total_ms,
event_end.total_waits
FROM dba_hist_system_event event_start,
dba_hist_system_event event_end,
dba_hist_snapshot snap
WHERE event_end.snap_id = event_start.snap_id + 1
AND event_end.event_name = 'db file sequential read'
AND event_start.event_name = event_end.event_name
AND event_start.snap_id = snap.snap_id
AND event_start.dbid = snap.dbid
AND event_start.instance_number = snap.instance_number
AND snap.begin_interval_time > SYSDATE - 14 -- max 14 days history.
-- AND to_char(snap.begin_interval_time,'HH24') IN ('09','10','11','12','13','14','15','16','17')
-- AND to_char(snap.begin_interval_time,'MI') = '50'
ORDER BY event_start.snap_id;
NOTE: You can restrict the snapshot intervals used to provide “hourly” values by uncommenting the additional two lines.
