In a previous post, I have discussed steps and methods to calculate I/O wait from an Oracle AWR report. The key and sometimes challenge task is to identify wait events which are related to the storage. While many waits may be caused by I/O related performance issues, the following waits are the main ones on which Oracle foreground session will be waiting:

• db file sequential read
• db file scattered read
• direct path read
• direct path read temp
• direct path write temp
• free buffer wait
• log file sync
• read by other session

In the next few posts, I will explain these waits. This important information is taken from the Oracle performance white paper written by Bartal Vindzberg (availability of this white paper will be posted in a future blog article). 

I shall start with the wait event that is most associated with slow storage causing end-user delays, the Db file sequential read.  This event represents a wait for a physical read of a single Oracle block from the disk. It is usually caused by reading an index block or accessing a table via a rowid (after it was obtained from an index block). Although the name may suggest that sequential I/O access path is being used, actually this event indicates random I/O access pattern. The following SQL statement illustrates a table that is being accessed via an index resulting in reads of a single block (one to access the index and another one to access the table):

 

The following diagram illustrates an Oracle shadow process that reads one block from the disk and places it in the buffer cache in the SGA (System Global Area):

 

Db file sequential read is almost always associated with index access path that reads a single block into the buffer cache. If a query performs multiple reads of single blocks (which results in several Db file sequential read waits) , the blocks most likely will not be adjacent on disk, resulting in Random I/Os.

Db file sequential read is the common wait event associated with disk I/O.

A performance of application that suffers from high percentage of Db file sequential read will almost always improve its performance with  low latency storage.

Tags: application performance, Database Performance, I/O bottlenecks, I/O Performance, I/O wait, Oracle AWR, Storage Performance