Oracle Database - 'db file scattered read' wait event

Card Puncher Data Processing

About

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.

Db File Scattered Read

Multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'.

VSESSION_WAIT parameter

Check the following VSESSION_WAIT parameter columns:

  • P1 - The absolute file number
  • P2 - The block being read
  • P3 - The number of blocks (should be greater than 1)

Differences between read events

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)

Oracle Database Scattered Sequential Direct Path Read

Actions

On a healthy system, physical read waits should be the biggest waits after the idle waits. However, also consider whether there are:

  • direct read waits (signifying full table scans with parallel query)
  • or db file scattered read waits on an operational (OLTP) system that should be doing small indexed accesses.

Other things that could indicate excessive I/O load on the system include the following:

  • Poor buffer cache hit ratio
  • These wait events accruing most of the wait time for a user experiencing poor response time

Support

I'm waiting with this event

  • During a delete or update, verify that you don't have any constraint on other tables
  • Bad statistics can also lead to bad explain plan with this kind of event.





Discover More
Db File Sequential Read
Oracle Database - 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. From SQL Developer,...
Db File Sequential Read
Oracle Database - Wait Event

A session can hang for a lot of reason that are called wait event. lock From SQL Developer, in the Monitoring Session Tools: Every wait event belongs to a class of wait event. The following list...
Oracle Database Direct Path Read Temp With Parallel Slave
Oracle Database - direct path read temp and direct path read wait event

“direct path read temp” and “direct path read” event are wait events. When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event....



Share this page:
Follow us:
Task Runner