Troubleshooting I/O Related Waits
(Doc ID 223117.1)
Last updated on JANUARY 21, 2023
Applies to:Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
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:
- AWR (or Statspack) reports show I/O wait events in the "Top 5 Wait/Timed Events" section.
- SQL Tracing with wait events of a database session shows it is limited mainly by I/O wait events.
- Operating System tools show very high utilization or saturation of disks used for storage of database files.
The article should be of use to Database Administrators, Support Engineers,Consultants and Database Performance Analysts.
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
|Scope and Application|
|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'|
|Who to contact for more information?|
|Use Faster Hardware|