Oracle AWR disk file statistics

Call (800) 766-1884 for Oracle support & training

Donald K. Burleson

Oracle Tips

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 13.12.

Figure 13.12: Disk activity over time

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

-- *************************************************

-- 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

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

-- *************************************************

-- 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

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

Figure 13.13 below shows a representation of a daily disk delta report.

Figure 13.13: 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. These scripts and many others are available from oracle-script.com.

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 13.14 below.

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

Figure 13.14: 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

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')

)

;

Figure 13.15: 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.

Figure 13.15: A daily disk write I/O trend signature

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.

Conclusion

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.

SEE CODE DEPOT FOR FULL SCRIPTS

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

”call

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Oracle® is the registered trademark of Oracle Corporation.