| |
 |
|
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.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
