There are 2 main I/O related wait events in Oracle: “db file scattered read” and “db file sequential read”. In this post we will understand the “db file scattered read” wait event (to read about “db file sequential read” go to the db file sequential read post).

A server process is waiting on “db file scattered read” wait event after it performs a multiblock I/O operation and it is waiting for the operating system to complete it.

A multiblock I/O operation occurs when the server process asks for a “chunk” of blocks and not for a single block. The amount of blocks in the “chunk” is determined by the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter. For example, if the database block size is 8k and DB_FILE_MULTIBLOCK_READ_COUNT is 8, a single multiblock I/O operation will read 8 blocks, which is 64KB of data. The maximum size of a multiblock I/O operation is determined by the operating system and storage.

When talking about performance, there are several things we need to remember:

• A server process can perform multiblock I/O operations only in certain cases like full table scans and fast full index scans. It is important to remember that multiblock I/O and DB_FILE_MULTIBLOCK_READ_COUNT are irrelevant to other operations such as index  range/unique scans.
• Setting the DB_FILE_MULTIBLOCK_READ_COUNT to a high value will result in less I/O operations (as long as it is not exceeding the operating system and storage limitation).
• Setting the DB_FILE_MULTIBLOCK_READ_COUNT to a high value can also result in unwanted full table scans, since a higher value lowers the optimizer cost of full table scans.
• Starting in 10gR2, Oracle will determine the best value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter if it is not set. If the parameter is set, Oracle will not automatically set its value.

To read about analyzing wait events in an AWR report, see my post analyzing oracle awr reports – top 5 events.