Monitoring Disk I/O with AWR

One of the great features of AWR is that it can directly monitor disk input and output (I/O). The following is a great technique that can be used for extending the capabilities of Oracle's STATSPACK performance utility to report statistics on I/O activity at the disk and file level in a UNIX environment.

Statistics ordinarily captured by an AWR snapshot are related only to the read and write activity at the Oracle data file level. Normally, AWR cannot show I/O at the disk or mount point level, which can be valuable information in determining hyperactivity on particular files or disks.

Instead of using standard utilities to generate a report for a single time period, utilities can be modified to collect I/O data over consistent intervals, storing the results in Oracle tables for easy access and reporting. The following is an outline of requirements.

The dba_hist_filestatxs table contains I/O data collected by snapshots taken at consistent intervals. I/O data captured includes the actual number of physical reads, physical writes, block reads, block writes, and the time required for each operation. Disk activity over time is represented in Figure

Holistic data, which yields the status internal to Oracle and external with the various UNIX and Linux commands, can be gathered and analyzed using just STATSPACK and system utilities.

The data collected by STATSPACK can be accessed with normal scripts such as the snapfileio_10g.sql listed below:

snapfileio_10g.sql

-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
 
rem
rem NAME: snapfileio.sql
 
rem FUNCTION: Reports on the file io status of all of the
rem FUNCTION: datafiles in the database for a single snapshot.
 
 
column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
column Percent format 999.999 heading 'Percent|Of IO'
column brratio format 999.99 heading 'Block|Read|Ratio'
column bwratio format 999.99 heading 'Block|Write|Ratio'
column phyrds heading 'Physical | Reads'
column phywrts heading 'Physical | Writes'
column phyblkrd heading 'Physical|Block|Reads'
column phyblkwrt heading 'Physical|Block|Writes'
column filename format a45 heading 'File|Name'
column file# format 9999 heading 'File'
 
set feedback off verify off lines 132 pages 60 sqlbl on trims on
 
select
     nvl(sum(a.phyrds+a.phywrts),0) sum_io1
from
     dba_hist_filestatxs a where snap_id=&&snap;
 
select nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from
        dba_hist_tempstatxs b where snap_id=&&snap;
       
select &st1+&st2 sum_io from dual;
 
rem
@title132 'Snap&&snap File I/O Statistics Report'
 
spool rep_out\&db\fileio&&snap
 
select
     a.filename, a.phyrds, a.phywrts,
     (100*(a.phyrds+a.phywrts)/&divide_by) Percent,
     a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio,
      (a.phyblkwrt/greatest(a.phywrts,1)) bwratio
from
     dba_hist_filestatxs a
where
     a.snap_id=&&snap
union
select
     c.filename, c.phyrds, c.phywrts,
     (100*(c.phyrds+c.phywrts)/&divide_by) Percent,
     c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio,
      (c.phyblkwrt/greatest(c.phywrts,1)) bwratio
from
     dba_hist_tempstatxs c
where
SEE CODE DEPOT FOR FULL SCRIPTS
     1
/
 
spool off
pause Press enter to continue
set feedback on verify on lines 80 pages 22
clear columns
ttitle off
undef snap

Of course, a single AWR reading suffers from the same limitations that a single read of the v$ or gv$ dynamic performance views.  It only gives the cumulative data from when the database was started to the time that the snapshot was taken. A better methodology is shown in snapdeltafileio_awr.sql.

snapdeltafileio_awr.sql

-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
 
rem
rem NAME: snapdeltafileio.sql
rem
rem FUNCTION: Reports on the file io status of all of
rem FUNCTION: the datafiles in the database across
rem FUNCTION: two snapshots.
rem HISTORY:
rem WHO             WHAT           WHEN
rem Mike Ault              Created        11/19/03
rem
 
column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
column Percent format 999.999 heading 'Percent|Of IO'
column brratio format 999.99 heading 'Block|Read|Ratio'
column bwratio format 999.99 heading 'Block|Write|Ratio'
column phyrds heading 'Physical | Reads'
column phywrts heading 'Physical | Writes'
column phyblkrd heading 'Physical|Block|Reads'
column phyblkwrt heading 'Physical|Block|Writes'
column filename format a45 heading 'File|Name'
column file# format 9999 heading 'File'
set feedback off verify off lines 132 pages 60 sqlbl on trims on
 
select
     nvl(sum((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts)),0) sum_io1
from
     dba_hist_filestatxs a, dba_hist_filestatxs b
where
        a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id
        and a.filename=b.filename;
 
select
     nvl(sum((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts)),0) sum_io2
from
     dba_hist_tempstatxs a, dba_hist_tempstatxs b
where
        a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id
        and a.filename=b.filename;
 
select &st1+&st2 sum_io from dual;
 
rem
@title132 'Snap &&first_snap_id to &&sec_snap_id File I/O Statistics Report'
spool rep_out\&db\fileio'&&first_snap_id'_to_'&&sec_snap_id'
 
select
     a.filename, b.phyrds -a.phyrds phyrds, b.phywrts-a.phywrts phywrts,
     (100*((b.phyrds-a.phyrds)+(b.phywrts-a.phywrts))/&divide_by) Percent,
     b.phyblkrd- a.phyblkrd phyblkrd, b.phyblkwrt-a.phyblkwrt phyblgwrt,
        ((b.phyblkrd-a.phyblkrd)/greatest((b.phyrds-a.phyrds),1)) brratio,
        ((b.phyblkwrt-a.phyblkwrt)/greatest((b.phywrts-a.phywrts),1)) bwratio
from
     dba_hist_filestatxs a, dba_hist_filestatxs b
where
        a.snap_id=&&first_snap_id and b.snap_id=&&sec_snap_id
        and a.filename=b.filename
union
select
     c.filename, d.phyrds-c.phyrds phyrds, d.phywrts-c.phywrts phywrts,
     (100*((d.phyrds-c.phyrds)+(d.phywrts-c.phywrts))/&divide_by) Percent,
     d.phyblkrd-c.phyblkrd phyblkrd, d.phyblkwrt-c.phyblkwrt phyblgwrt,
        ((d.phyblkrd-c.phyblkrd)/greatest((d.phyrds-c.phyrds),1)) brratio,
        ((d.phyblkwrt-c.phyblkwrt)/greatest((d.phywrts-c.phywrts),1)) bwratio
from
     dba_hist_tempstatxs c, dba_hist_tempstatxs d
where
SEE CODE DEPOT FOR FULL SCRIPTS
order by
     1
/
spool off
pause Press enter to continue
set feedback on verify on lines 80 pages 22
clear columns
ttitle off
undef first_snap_id
undef sec_snap_id

 below shows a representation of a daily disk delta report.

A daily disk delta report in Ion

The report accepts two snapshot IDs and uses them to calculate the delta between the I/O readings. This I/O delta information is vital to help pinpoint real I/O problems for a given time period.

Combined with iostat and vmstat readings from the same time period, one can get a complete picture of the I/O profile of the database. A similar technique can be used for I/O timing and other useful delta statistics. 

It was previously noted that the rpt_10g_sysstat_hr.sql shows the signature for any Oracle system statistic, averaged by hour of the day, and this is great for plotting disk activity.

The following result shows an average for every hour of the day.  This information can then be easily pasted into an MS Excel spreadsheet and plotted with the chart wizard as shown in Figure 

SQL> @rpt_10g_sysstat_hr
 
This will query the dba_hist_sysstat view to
display average values by hour of the day
 
Enter Statistics Name:  physical reads
 
SNAP_TIME              AVG_VALUE
------------------- ------------
00                       120,861
01                       132,492
02                       134,136
03                       137,460
04                       138,944
05                       140,496
06                       141,937
07                       143,191
08                       145,313
09                       135,881
10                       137,031
11                       138,331
12                       139,388
13                       140,753
14                       128,621
15                       101,683
16                       116,985
17                       118,386
18                       119,463
19                       120,868
20                       121,976
21                       112,906
22                       114,708
23                       116,340

An hourly disk read I/O trend signature:

As shown in rpt_10g_sysstat_dy.sql, the script can easily be changed to aggregate the data by day of the week instead of hour of the day.

   rpt_10g_sysstat_dy.sql

prompt  Copyright 2004 by Donald K. Burleson
prompt
prompt
prompt  This will query the dba_hist_sysstat view to
prompt  display average values by hour of the day
prompt
 
set pages 999
 
break on snap_time skip 2
 
accept stat_name char prompt 'Enter Statistics Name:  ';
 
 
col snap_time   format a19
col avg_value   format 999,999,999
 
select
   decode(snap_time1,1,'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday',7,'Sunday') snap_time,
   avg_value
from (  
select
   to_char(begin_interval_time,'d') snap_time1,
   avg(value)                          avg_value
from
   dba_hist_sysstat
  natural join
   dba_hist_snapshot
where
SEE CODE DEPOT FOR FULL SCRIPTS
order by
   to_char(begin_interval_time,'d')
)  
;

A daily disk reads I/O trend signature

In Figure 13.15, the daily aggregation of disk read I/O shows that the database experiences the most physical read I/O activity on Saturday. This allows the isolation of routines and applications which are performed mainly on Saturday in order to check them for possible I/O tuning.

In Figure 13.15, the database experiences the most physical write I/O activity on Friday and Saturday. This allows the isolation of routines and applications which are performed mainly on Friday and Saturday in order to check them for possible I/O tuning.

This chapter will conclude with a review and summary of the major points regarding disk I/O tuning.

Disks have evolved over the past 40 years but remain an archaic component of Oracle.  Disk array manufacturers are now homogenizing disk arrays to the point where they can get I/O rates to match the disk capacity. This results in the spread of the I/O across many more platters than ever before, but it makes tracking Oracle I/O problems more difficult.

The main points of this chapter include:

•  Databases used to be largely I/O bound, but this is changing as a result of large data buffer caches.  Many databases in Oracle10g have shifted to being CPU bound.
• Solid-state disk is making inroads with Oracle and many systems are now using SSD instead of disk.
• Verify that the database is I/O bound before undertaking the tuning of the I/O sub-system.  Check the top 5 wait events for the database.
• Oracle v$ views and the AWR tables provide time series I/O tracking information so that read and write I/O signatures can be plotted, aggregated by hour of the day and day of the week.
• The next step is to dive into the Oracle instance and look at how the AWR and ASH tables can give insight into the data for sizing the main SGA regions.