Oracle 等待事件之 db file scattered read

db file scattered read

官網解釋：

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. This is why the corresponding wait event is called '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'.

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 greater than 1)

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

解釋：db file scattered read 等待事件：是由于多數據塊讀操作產生的，當我們檢索數據時從磁盤上讀取數據到內存中，一次I/0讀取多個數據塊，而數據塊在內存中是分散分布并不是連續的，當數據塊讀取到內存中的這個過程就會產生“db file scattered read” 事件。

    多個數據塊讀場景：

       （1） FTS（Full Table Scans) 全表掃描

       （2） IFFS（Index Fast Full Scans) 索引快速全掃描： 把索引鏈切割成很多份，多塊并行讀取。