Muhammad Nadeem Chaudhry's Blog


DB File Read Wait Events

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 17, 2012

DB File Sequential Read Wait Event

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as ‘db file sequential read’.

Check the following V$SESSION_WAIT parameter columns:

  • P1 – The absolute data file number
  • P2 – The block being read
  • P3 – The number of blocks (should be 1)

A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2=1 indicates a file header read).

DB File Scattered Read Wait Event

db file scattered read is a wait event. It’s a multiblock read into many discontinuous SGA buffers

This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the 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 a full table scan.

The db file scattered read wait event identifies 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.

Such reads are called scattered read calls, because the blocks are scattered throughout memory.

Solution: Increase the size of Buffer Cache.