Spotlight on Oracle – DB file waits (SOO61)

DB file waits

Wait conditions starting with the phrase DB FILE (for example, db file parallel write, db file scattered read, db file sequential read, and db file single write) all occur when an Oracle session issues an I/O request against an Oracle datafile. The session uses the operating system’s read system call, and waits while the I/O subsystem performs the I/O.

Database-file writes are only performed by the database writer. The db file write waits are never experienced by user sessions. However, user sessions do read data from database files directly, and almost always experience db file read waits.

Unless your entire database is cached in memory, waiting for db file I/O is inevitable. The presence of db file waits does not indicate that anything is wrong within the database. In most healthy databases, db file waits account for about 80-90% of all non-idle wait times.

DB file waits can be reduced by:

• Optimizing disk I/O, striping datafiles (see Improving database I/O).
• Reducing I/O requirements by increasing the size of the buffer cache.
• Reducing the I/O requirements of SQL statements through SQL tuning (see Isolating and tuning problem SQL).