db file scattered read等待事件

Oracle中db file scattered read等待事件发生在当一个会话在等待一个多数据块的IO请求完成。其典型的发生在当有全表扫描和索引快速扫描INDEX FAST FULL SCAN时。Oracle一次性读取DB_FILE_MULTIBLOCK_READ_COUNT对应的连续数据块，并将它们分散到buffer cache中的buffer中。 换句话说从IO读取上和物理存储的顺序上这些数据块应当是连续的，一个块排在一个块后面，但从buffer cache角度看这些数据块对应的buffer并不连续在一起。

具体的行为当然也和操作系统版本以及oracle版本相关，以及不同的存储设备特性都会有关系。

该db file scattered read等待的相关参数：

• p1 file#  这是db file scattered read读取的数据块所在的数据文件文件号，从Oracle 8开始是绝对数据文件号ABSOLUTE file number (AFN)。
• p2 block# 这是db file scattered read读取的起始数据块号
• p3 blocks 这是db file scattered read读取的块的数量，最大为DB_FILE_MULTIBLOCK_READ_COUNT，从Oracle 10.2开始该参数会自动调优。

db file scattered read的等待时间 是直到所有数据块的IO请求都被满足。注意操作系统可能基于文件系统缓存来满足这些IO请求，对于Oracle来说 起始它并不知道这些IO请求是从文件系统缓存返回的，还是真的左了磁盘的读取操作或存储响应返回的，它只负责做read() pread()这些操作系统系统调用而已。

Waits on this event indicate the statement is performing a full table scan or an index range scan. This is often reduced by adding an index or making the index more efficient.

Solutions

Tune the SQL statement so that it uses an index rather than a full table scan if warranted. If the table is small, a full table scan could be more efficient that using an index so test the differences. Use the Ignite Objects tab to determine the most costly full table scan if there are more than one table in the query.

Increase the buffer cache so that more blocks are already in memory rather having to be read from disk. The query will still need to read the same number of blocks so tuning is the first recommendation, but if you cannot tune the statement, a query reading blocks from memory is much faster than from disk.

Slow disks could be causing Oracle to spend time reading the data into the buffer cache. Review the ‘DB Multi Block Disk Read Time’ metric in Ignite to determine disk speeds from Oracle’s perspective. If the time to read data is above 30ms, that could indicate slow disks.

Update table and index statistics if they are stale so that Oracle understands the benfits of existing indexes.

Systemwide Waits:

IO is a normal activity so you are really interested in unnecessary or slow IO activity.If the TIME spent waiting for multiblock reads is significant then determine which segments/objects Oracle is performing the reads against. See the “Tablespace IO”, and “File IO” sections of the AWR (or STATSPACK) reports, along with ADDM and ASH output. These should show which tablespaces / files are servicing the most IO requests, and give an indication of the speed of the IO subsystem. Tablespaces / files involved in “db file scattered read” waits will have “Av Blks/Rd” > 1.

The files where the reads are occuring can also be found by looking at V$FILESTAT where BLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).

See the “Top SQL by Disk Reads” sections of AWR reports for clues about any SQL causing high I/O. If statistics gathering is enabled then V$SQL_PLAN can also give clues about SQL statements using FULL scans.

It can sometimes be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions are incurring waits:

  SELECT sid, total_waits, time_waited
    FROM v$session_event
   WHERE event='db file scattered read'
     and total_waits>0
   ORDER BY 3,2
  ;

One can also look at:

• Statements with high DISK_READS in V$SQL – shown in the “Top SQL by Disk Reads” section of AWR.
• Sessions with high “table scans blocks gotten” in V$SESSTAT

Reducing Waits / Wait times:

Ideally you do not want to repeatedly perform full scans in online portions of an application when there is a faster more selective way to get at the data – in this case query tuning should be used to optimize the online SQL.
In non online portions of an application table scanning is much more likely to be required. The main steps for tuning IO waits are described inNote:223117.1. Some specific points for “db file scattered read” waits include:

• Tuning of SQL usually gives the largest gains
• Consider partitioning to reduce the amount of data you need to scan
• Are affected objects sparsely populated? If so consider shrinking them
• Consider Advanced Compression to reduce the number of blocks that need to be visited
• Careful use of multiple buffer pools and the CACHE option might help.

Troubleshooting

See the following documents for help troubleshooting issues relating to “db file scattered read” waits:

Document:1475785.1 Resolving Issues Where Application Queries are Waiting Too Often for ‘db file scattered read’ Operations
Document:1476092.1 Resolving Issues Where ‘db file scattered read’ Waits are Seen Due to IO Performance Problems
Document:223117.1 Troubleshooting I/O Related Waits
Document:1275596.1 How to Tell if the I/O of the Database is Slow

Known Issues / Bugs:

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

NB	Bug	Fixed	Description
P	13400729	11.2.0.4, 12.1.0.1	increased elapsed time on “db file scattered read” in IBM AIX
	6452766	10.2.0.4.3, 10.2.0.5, 11.2.0.1	10046 trace does not always show the correct “obj#” value in the trace
	5376783	10.2.0.4, 11.1.0.6	DBMS_SPACE.OBJECT_GROWTH_TREND can perform excessive IO

• ‘*’ indicates that an alert exists for that issue.
• ‘+’ indicates a particularly notable issue / bug.

• See Note:1944526.1 for details of other symbols used