AWR Analysis for IO Reporting

Oracle gathers a wealth of information in the Automatic Workload Repository (AWR). We can query this data to get very important IO access time information.

  • Create a working table because depending of the size of the AWR, the queries could take a long time and cause load on the database.

drop table mdecker.filestatistics;
create table mdecker.filestatistics as select t.snap_id, t.begin_interval_time, filename, file#, tsname, phyrds, singleblkrds, readtim, singleblkrdtim, phyblkrd from
dba_hist_filestatxs f ,
dba_hist_snapshot t
where
t.snap_id = f.snap_id and (
begin_interval_time between to_date('05.11.2007 09:00','DD.MM.YYYY HH24:MI') and
to_date('09.11.2007 17:00','DD.MM.YYYY HH24:MI')
OR
begin_interval_time between to_date('12.11.2007 09:00','DD.MM.YYYY HH24:MI') and
to_date('16.11.2007 17:00','DD.MM.YYYY HH24:MI') );

  • Create a summary table

create table mdecker.filestatistics_summary as
select snap_id, begin_interval_time, sum(phyrds) phyrds, sum(singleblkrds) singleblkrds from mdecker.filestatistics
group by snap_id, begin_interval_time
order by snap_id

  • Query the summary table

select snap_id,
y.begin_interval_time,
phyrds as prev_phyrds,
lead(phyrds) over(order by snap_id) current_physrds,
lead(phyrds) over(order by snap_id) - phyrds,
begin_interval_time as prev_interval_time,
lead(begin_interval_time) over(order by snap_id) current_time,
lead(begin_interval_time) over(order by snap_id) - begin_interval_time,
extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time) as sec,
extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time) as min,
(extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time)*60)+extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time),
(lead(phyrds) over(order by snap_id) - phyrds)/
((extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time)*60)+extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time))
from filestatistics_summary y

Leave Comment