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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

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

 

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

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

 

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

)  

;

 

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 software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.