Re: Index Fast Full Scan and associated wait

EscVector wrote:
> I'm bottlenecked on db file sequential read. What wait should I expect
> to see if the plan shows Index Fast Full Scan? Is it db file
> sequential read or scattered read since the Fast Full Scan is treated
> like a tablescan and uses multiblock reads?

Paraphrased from various sources:
Oracle issues a scattered read to read data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan of an index in addition to the more common full table scan. Db file scattered read wait events indicate that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other.

A sequential read is a single-block read, where a user process is reading a buffer into the SGA buffer cache and waiting for a physical I/O. Single block I/Os are usually the result of index scans. Rarely, full table scans could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.

An index fast full scan should appear as db file scattered read. The second paragraph above seems to indicate that full table scans can wait on a db file sequential read event, if most of the required blocks are already in the buffer cache, and no contiguous blocks are required. I suspect that the same is true for index fast full scans.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jan 11 2007 - 10:23:31 CST