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.