Troubleshooting I/O Related Waits
(Doc ID 223117.1)
Last updated on AUGUST 30, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 7.3.4.0 and laterOracle Database - Standard Edition - Version 7.3.4.0 and later
Oracle Database - Personal Edition - Version 7.3.4.0 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:
- 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.
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 |