Oracle Database - db file sequential read Wait Event

> Database > Oracle Database

1 - About

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.

From SQL Developer, in the Monitoring Session Tools:

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) .

Advertising

3 - Possibly causes

3.1 - Poorly tuned SQL

Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads.

3.2 - Slow I/O system

Cross-check I/O system and V$FILESTAT for poor read time.

4 - Action

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 below 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)

5 - Documentation / Reference

Advertising