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


Donald K. Burleson

Oracle Tips

Time series I/O Wait Analysis

Every database will have "signatures," which are typically caused by regularly scheduled processing. When these signatures are identified, STATSPACK must be used to extract the SQL and ensure that it is properly optimized. When the detailed event waits data has been acquired, it is a trivial task to roll up the data and create trend reports. 


If the read waits persist, the next step is to manipulate the schedule to execute the colliding SQL at different times.  Some of the workload can be moved to a different time window if there is not sufficient I/O bandwidth to run the full workload all at once. To display a trend by day, a similar query may be run that will average the number of sequential read waits by day of the week.


More importantly, there is the detailed wait information in the dba_hist_waitstat table, so the exact table or index that is experiencing the real time wait can be investigated.  By doing that in conjunction with AWR, the SQL may also be collected in the AWR table such that it is clear what SQL is precipitating the disk wait events.


Plotting real-time waits by hour

Figure 13.9: Plotting real-time waits averages by hour of the day


Figure 13.9 shows a high number of real time db file sequential read waits between 2:00AM and 3:00AM with another spike between 9:00PM and midnight.  This information can go to STATSPACK for use in extracting the SQL that was running during this period.


The DBA can also average the read waits by day of the week as shown in Figure 13.10.  The figure shows that there is an obvious increase in scattered read waits every Tuesday and Thursday and the SQL can be extracted during these periods.


Plotting real-time waits by day

Figure 13.10: Plotting real-time waits averages by day of the week


Ordinarily, this insight would be ineffective because the source of the waits would not be obvious.  Of course, with Oracle release 2 and beyond, the v$segment_statistics view can be used to see some of this information if the statistics_level parameter is set to a value of seven or higher.


Now, the DBA can drill in and see those specific table and indexes that were experiencing the sequential read waits.



                     Segment                        Segment     Wait

Date        Hr.      Name                           Type       Count

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

23-jan-2003 21       SYSPRD.S_COMM_REQ_SRC_U1       INDEX       23

23-jan-2003 21       SYSPRD.S_EVT_ACT               TABLE       44

23-jan-2003 21       SYSPRD.S_EVT_ACT_F51           INDEX       16

23-jan-2003 22       SYSPRD.S_EVT_ACT               TABLE       32


The specific object that experiences the physical read wait must be identified since the goal may be to distribute the object over additional disk spindles.


The details about the objects that experience physical read waits can be easily captured using a real time wait sampling method. Once they are recognized, STATSPACK can be used to find the problematic SQL and begin the tuning.  The tuning of physical read waits involves SQL tuning, object striping across multiple disks, employing the KEEP pool for small objects, rescheduling the SQL to relieve the contention, or increasing the data buffer cache size.


A simple query can be used to plot the user I/O wait time statistic for each AWR snapshot.  From phys_reads.sql, the physical read counts can be extracted from the AWR.


<      phys_reads.sql



break on begin_interval_time skip 2


column phyrds              format 999,999,999

column begin_interval_time format a25








natural join




The output below shows a running total of Oracle physical reads.  The snapshots are collected every half-hour.  Starting from this script, a where clause criteria could easily be added to create a unique time series exception report.


SQL> @phys_reads


BEGIN_INTERVAL_TIME       FILENAME                                      PHYRDS

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

24-FEB-04 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF        164,700

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF        26,082

                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF        472,008

                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF           1,794

                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA          2,123


The next step is to take a look at how these simple scripts can be enhanced to produce powerful exception reports.


In the simple report called hot_write_files.sql, the dba_hist_filestatxs is queried to identify hot write datafiles where the file consumed more than 25% of the total physical writes for the instance.  The query compares the physical writes in the phywrts column of dba_hist_filestatxs with the instance -wide physical writes om statistic# = 55 from dba_hist_sysstat.


This simple yet powerful script allows the Oracle professional to track hot-write datafiles over time, thereby gaining important insights into the status of the I/O sub-system over time.


<      hot_write_files.sql




prompt  This will identify any single file who's write I/O

prompt  is more than 25% of the total write I/O of the database.



set pages 999


break on snap_time skip 2


col filename      format a40

col phywrts       format 999,999,999

col snap_time     format a20



   to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,





natural join



   phywrts > 0


   phywrts * 4 >



   avg(value)               all_phys_writes



  natural join



   stat_name = 'physical writes'


  value > 0


order by

   to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'),

   phywrts desc



The following is sample output.  This is a very useful report because the high write datafiles as well as those times when they are hot are revealed.


SQL> @hot_write_files


This will identify any single file who's write I/O

is more than 25% of the total write I/O of the database.



SNAP_TIME            FILENAME                                      PHYWRTS

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

2004-02-20 23:30     E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF          85,540


2004-02-21 01:00     E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF          88,843


2004-02-21 08:31     E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF          89,463


2004-02-22 02:00     E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF          90,168


2004-02-22 16:30     E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF         143,974

                     E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF         88,973


Time series exception reporting is extremely useful for detecting those times when an Oracle10g database is experiencing stress.  Many Oracle professionals will schedule these types of exception reports for automatic e-mailing every day.



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:




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.