Db file sequential read of Oracle waiting for event

db file sequential read:

Official website explains:

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 because of extent boundaries, or buffers 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 file number

P2: The block being read

P3: The number of blocks (should be 1)

On a healthy system, physical read waits should be the biggest waits after the idle waits. However, also consider whether there are db file sequential reads on a large data warehouse that should be seeing mostly full table scans with parallel query.

Figure 10-1 depicts the differences between the following wait events:

db file sequential read (single block read into one SGA buffer)

db file scattered read (multiblock read into many discontinuous SGA buffers)

direct read (single or multiblock read into the PGA, bypassing the SGA)

Figure 10-1 Scattered Read, Sequential Read, and Direct Path Read

Explanation:

db sequential read This event indicates that the user process is reading a buffer into the SGA buffer cache and waiting for a physical I / O call to return. Sequential reads are single block reads.

Single-block I / O is usually the result of using indexes.

A single block call that may be truncated due to a range boundary or buffer that exists in the buffer cache. These waits will also appear as db file sequential reads.

DB file sequential read optimization method:

Increase the buffer cache size in SGA from the beginning of reading to avoid reading from hard disk every time;

Optimize sql statements to reduce unnecessary block reads;