Top 5 I/O wait events in oracle AWR report

Top 5 I/O wait events in oracle:

“Db file sequential read” wait event occur on I/O operations on datafiles.

This wait event related to single block read, for index data blocks are table data blocks accessed through an index but can also be seen for datafile header blocks.

Troubleshooting:

Find the top sql statements in physical reads from

1. Statspack

2. AWR report under Sql order by reads or

3. From v$sql

Reasons for db file sequential read:

1.       If index range scans are involved, more blocks than necessary could be being visited if the index is unselective. By forcing and enabling the more selective index, we can access the same table data by visiting the fewer index blocks.

2.       If the indexes are fragmented, then again, we have to visit more blocks because there is a less index data per block.

3.       If the index being used has a large clustering factor, then more table data blocks have to be visited in order to get the rows in each index block.

Db file scattered read:

This wait even indicates that we are waiting for the read operation which reads number of blocks from disk. Most of the cases it indicates there is full table scan or index fast full scan.

It occurs when oracle performs multiblock reads from disk into non-contiguous buffers in the buffer cache. Such reads are issues for up to DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time. These typically happen for full table scans and fast full index scans.

Fast full index scans: When the data in the index is not in particular order.