Donald K. Burleson Oracle Tips

Checking for Oracle file wait conditions

When load balancing the I/O sub-system for an Oracle database, Oracle provides clues as to the files that are experiencing I/O-based contention.  Oracle records wait statistics that tracks the wait_count for all data files.

The basic information for this information is in the V$WAITSTAT view, but STATSPACK users now have the ability to store file wait information and create reports that display waiting files.  The following script can be run to detect those files that have more than 800 wait events per hour.

break on snapdate skip 2

column snapdate format a16

column filename format a40

select

   to_char(snap_time,'yyyy-mm-dd HH24') snapdate,

   old.filename,

   new.wait_count-old.wait_count waits

from

   perfstat.stats$filestatxs old,

   perfstat.stats$filestatxs new,

   perfstat.stats$snapshot   sn

where

   snap_time > sysdate-&1

and

   new.wait_count-old.wait_count > 800

and

   new.snap_id = sn.snap_id

and

   old.filename = new.filename

and

   old.snap_id = sn.snap_id-1

and

   new.wait_count-old.wait_count > 0

;

Here is a sample listing from this script.  This is a valuable tool for the Oracle professional to see when their database is experiencing excessive wait conditions.

***********************************************************
When there is high I/O waits, disk bottlenecks may exist
Run iostats to find the hot disk and shuffle files to
remove the contention

See p. 191 "High Performance Oracle8 Tuning" by Don Burleson

***********************************************************

SNAPDATE         FILENAME                                      WAITS           
---------------- ---------------------------------------- ----------           
2001-01-28 23    /u03/oradata/PROD/applsysd01.dbf               2169           
                 /u04/oradata/PROD/applsysx01.dbf               1722           
                 /u03/oradata/PROD/rbs01.dbf                    2016           
                                                                               
                                                                               
2001-01-30 16    /u03/oradata/PROD/mrpd01.dbf                   1402           
                                                                               
                                                                               
2001-01-31 23    /u03/oradata/PROD/applsysd01.dbf               4319           
                 /u04/oradata/PROD/applsysx01.dbf               3402           

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

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