Common Wait Events---db file scattered read

最新推荐文章于 2024-03-19 11:16:37 发布

翻译最新推荐文章于 2024-03-19 11:16:37 发布 · 381 阅读

文章标签：

#file #buffer #initialization #parameters #session #performance

Oracle SQL Tuning 专栏收录该内容

16 篇文章

订阅专栏

本文深入解析了Oracle数据库中的dbfilescatteredread事件，详细介绍了该事件的触发条件、参数意义及性能关注点。通过SQL查询V$EVENT_NAME表，了解事件的具体参数，包括读取的文件号、起始数据块号和读取的块数量。重点讨论了事件在全表扫描和全索引快速扫描中的应用，并强调了在多块读操作上时间过长可能需要调查的原因。

db file scattered read

The db file scattered read event is posted by the session when it issues an I/O request to read multiple data blocks. The blocks read from the datafiles are

scattered into the buffer cache. These blocks need not remain contiguous in the buffer cache. The event typically occurs during full table scans or index

fast full scans. The initialization parameter,DB_FILE_MULTIBLOCK_READ_COUNT determines the maximum number of data blocks to read.

当session发出一个I/O请求去读取多个数据块时就会记录一个db file scattered read 事件，这些块从数据文件中分散读到buffer cache中，这些块被读到buffer cache中依然不

需要连续，这个事件一般发生在全表扫描或全索引快速扫描时，初始参数DB_FILE_MULTIBLOCK_READ决定了一次要读取的最大数据块数。

Waiting on datafile I/O completion is normal in any Oracle database. The presence of this wait event
does not necessarily indicate a performance problem. However, if the time spent waiting for
multiblock reads is significant compared to other waits, you must investigate the reason for it.
Wait Parameters

在数据库中等待I/O完成都是正常的等待，在性能问题中是不必太观注的事件，然而，如果相比其他的等待在多块读上花费了大量的时间则必须找清楚原因。

来看一下这个事件各个参数的含义是什么：

SQL> SELECT * FROM V$EVENT_NAME e WHERE e.NAME LIKE 'db file scattered read%';
 
    EVENT#   EVENT_ID NAME                     PARAMETER1    PARAMETER2    PARAMETER3   WAIT_CLASS_ID WAIT_CLASS#  WAIT_CLASS
---------- ---------- -----------------------  ------------  -----------   ------------ ------------- -----------  -----------
       116  506183215 db file scattered read   file#         block#        blocks       1740759767              8  User I/O