My Oracle Support Banner

Troubleshooting I/O Related Waits (Doc ID 223117.1)

Last updated on JANUARY 16, 2019

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Purpose

Purpose

This article provides guidelines for tuning an Oracle database when the main source of contention is I/O-related.

Scope and Application

The techniques described here can be followed when:

The article should be of use to Database Administrators, Support Engineers,Consultants and Database Performance Analysts.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
 Purpose
 Scope and Application
Troubleshooting Steps
 Troubleshooting I/O-related waits
 Determining the Real Significance of I/O Wait Events
 Misinterpreting the Impact of Wait Events: Examples
  Example 1: Statspack before Oracle9i Release 2
 Example 2: AWR after Oracle10i Release 2
 General Approaches for Handling I/O Problems
 Reduce the general IO traffic being generated by the database
 Reduce the I/O requirements of the database by tuning instance parameters
 Make the remaining IO as efficient as possible
 Balancing the database I/O by usage of Oracle ASM (Automatic Storage Manager)
 Balancing the database I/O by usage of Striping, RAID, SAN or NAS
 Redistribute database I/O by manual placement of database files across different filesystems, controllers and physical devices
 Managing Runaway IO Using Resource Manager
 Avoid External IO contention
 Use Faster Hardware
 Leverage New Technology
 Datafile I/O related Wait Events
 'db file sequential read'
 'db file scattered read'
 'db file parallel read'
 Direct Path Reads and Writes
 Temporary Tablespace I/O related Wait Events
 'direct path write temp'
 'direct path read temp'
 Controlfile I/O related Wait Events
 'control file parallel write'
 'control file sequential read' and 'control file single write'
 Redo Logging I/O-Related Wait Events
 'log file parallel write'
 'log file sync'
 'log file sequential read' and 'log file single write'
 'switch logfile command' ,'log file switch completion' and 'log file switch (clearing log file)'
 'log file switch (checkpoint incomplete)'
 'log switch/archive' and 'log file switch (archiving needed)'
 Buffer Cache I/O-Related Wait Events
 'db file parallel write' , 'db file single write',  'write complete waits',  'free buffer waits'
 Footnote
 Who to contact for more information?
 Community Discussions
 Use Faster Hardware
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.