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.