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

 

 

 
 

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.

rpt_iowait.sql
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
***********************************************************


SNAPDATE FILENAME WAITS
---------------- ----------------------------------- ----------

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:

www.oracle-script.com

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