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

 

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.

 

                                                                Block

                     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

 

select

   begin_interval_time,

   filename,

   phyrds

from

   dba_hist_filestatxs  

natural join

   dba_hist_snapshot

;

 

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 11.00.32.000 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

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.

prompt

 

set pages 999

 

break on snap_time skip 2

 

col filename      format a40

col phywrts       format 999,999,999

col snap_time     format a20

 

select

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

   filename,

   phywrts

from

   dba_hist_filestatxs

natural join

   dba_hist_snapshot

where

   phywrts > 0

and

   phywrts * 4 >

(

select

   avg(value)               all_phys_writes

from

   dba_hist_sysstat

  natural join

   dba_hist_snapshot

where

   stat_name = 'physical writes'

and

  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.

 

SEE CODE DEPOT FOR FULL SCRIPTS


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:

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

 


”call

  
 

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.