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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Oracle Tips by Burleson Consulting

Checking for Oracle file wait conditions

As we know, Oracle provides clues about data files that are experiencing a disproportional amount of I/O activity and this information is critical when load balancing the I/O sub-system for any Oracle database. As a review, we can compare I/O values for individual data files in the stats$filestatxs table with the system-wide I/O values in the stats$sysstat table to identify any data files that experience a disproportional amount of I/O activity. Oracle also records wait statistics that tracks the wait_count for all data files in the V$ views and also inside STATSPACK.

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

to_char(snap_time,'yyyy-mm-dd HH24') snapdate,
new.wait_count-old.wait_count waits
perfstat.stats$filestatxs old,
perfstat.stats$filestatxs new,
perfstat.stats$snapshot sn
snap_time > sysdate-&1
new.wait_count-old.wait_count > 800
new.snap_id = sn.snap_id
old.filename = new.filename
old.snap_id = sn.snap_id-1
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

---------------- ----------------------------------- ----------

2001-01-28 23 /u03/oradata/PROD/applsysd01.dbf 169
/u04/oradata/PROD/applsysx01.dbf 722
/u03/oradata/PROD/rbs01.dbf 3016

2001-01-30 16 /u03/oradata/PROD/mrpd01.dbf 402

2001-01-31 23 /u03/oradata/PROD/applsysd01.dbf 319
/u04/oradata/PROD/applsysx01.dbf 402

As we see most of the details about disk I/O are hidden from view, but Oracle does provide a window into detail about those time where UNIX could not complete an immediate I/O.

The above is an excerpt from the "Oracle UNIX Administration Handbook" by Oracle press, authored by Donald K. Burleson.


Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA




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. 

Hit Counter