浅析oracle常见等待事件之 db file scattered read

浅析oracle常见等待事件之 db file scattered read(转） 

原文地址： http://www.hellodml.com/2011/12/%E6%B5%85%E6%9E%90oracle%E5%B8%B8%E8%A7%81%E7%AD%89%E5%BE%85%E4%BA%8B%E4%BB%B6%E4%B9%8B-db-file-scattered-read/  

Oracle 在执行全表扫描（full table scan，FTS）或全索引扫描（index full scan）时，为了保障性能，尽量一次性读取多个块，这成为multi block I/O。每次执行multi block I/O，都会等待物理I/O结束，此时出现等待事件：db file scattered read。利用 db file scattered read等待事件的P1=file#，P2=初始 block#，P3=要读取的块数信息，可以确认哪些段会发生multi block I/O。

Oracle 按照 db_file_multiblock_read_count（以下简称MBRC）参数值进行 multi block I/O.这个值在不同的OS上都有最大值的界定，可以通过如下方法确认最大值。

使用尽量这个词汇时需要注意，因为oracle在执行FTS时也执行single block I/O，这时即使FTS也会发生db file sequential read等待，注意，这里的sequential与scattered，不是说被读取的表或者索引上的块是连续的还是分散的，而是指读到内存中的时候，是连续的还是分散的 。FTS上使用single block I/O可能读取比MBRC定义的值小的块数的情况如下：

1,达到区的边界线时：如一个区有9个块，一次multi block I/O读取了8个块，则一次以multi block I/O读取之后的剩余一个块通过single block I/O读取。如果剩下的块有两个，就会执行multi block I/O，而且只读取两个块。

2,扫描过程中读取被缓存的块时:如果读取8个块时，若其中第三个块被缓存，oracle将前两个块通过multi block I/O读取，对于第三块执行一次logical I/O,剩下的5个块通过multi block I/O读取。这种情况经常发生时，因引发多次的I/O，可能成为FTS速度下降的原因。

3,存在行链接时：在执行FTS的过程中，如果发现了行链接，oracle为了读取剩下的行而引起的附加I/O，此时执行single block I/O，对于行链接和行迁移的（migrated row)的不同点需要准确理解。行链接时在行的大小比块的大小大的时候发生，因此使用更大的块或者减少pctfree值之外，在没有可消除行链接的方法。行迁移起初记录到一个块中，但随着行的大小不断增大，块内没有空间时发生迁移。这时实际上行移动到另外的块，在原来的行里插入了指明转以后的块和行位置的rowid。行迁移特别是在通过索引扫描表时会给性能带来严重影响，这是因为读取一个行时需要读取两个或两个以上的块。行迁移对FTS的 影响需要稍微留意。FTS是对HWM以下所有的块从头开始顺序读取的工作。在执行FTS过程中，oracle如果遇到行迁移，则不会引发更多的single block I/O，而是继续工作。这是因为知道反正要在扫描过程中需要重新读取。因此如果HWM的位置相同，则不管行迁移存在与否，FTS自身性能几乎不变。当然，如果发生了迁移，就会追加分配区，如果HWM移动的更远，就会给FTS的性能造成影响，消除行迁移是要留心以上部分。

db file scattered read 事件与db file sequential read 事件相同，是oracle中最经常发生的等待事件。因为从数据文件读取块时只能执行multi block I/O或者single block I/O，大家对db file scattered read等待有所顾忌的原因是它与物理I/O相关，还有它与FTS一起出现。最终其还是与FTS合不合适想联系。

下面，我们针对oracle的I/O层，讨论对以db file scattered read等待的解决方法。

一 应用程序层

需要筛选出主要发生db file scattered read等待的SQL语句。如果不必要地执行FTS或者index ful scan，修改sql语句或者创建更合理的索引就可以解决。大量读取数据时，多数情况下FTS性能更好。我们要考虑相应的SQL语句后，来判断FTS有利还是index range scan有利，而不是盲目的创建索引。

二 oracle 内存层

如果buffer cache过小，就会反复需要物理I/O，相应地 db file scattered read等待也会增加。这时free buffer waits等待事件一同出现的几率较高。FTS引起的db filescattered read等待的严重性不仅在于需要I/O，而且在于降低告诉缓冲区的效率。进而影响会话的工作。从这个角度出发，处理FTS的有效方法之一就是使用多重缓冲池。读取一次就不在使用的数据，没有必要保存到告诉缓冲区从而导致影响其他用户额工作。多重缓冲区虽然是有效管理缓冲区的强有力的方法，但是并未被广泛使用。多重缓冲区从3个方面改善高速缓冲区的性能：第一，将经常访问的对象保存于内存，进而将物理I/O最小化。第二，临时性数据所占用的内存被快速的重写，进而将内存的浪费最小化。第三，因为每个缓冲池各使用不同的cache buffers lru chain 锁存器，所以有减少锁存器争用的效果。

有效使用FTS的另一种方法是将db_file_multiblock_read_count参数值提高。这个参数决定执行multi block I/O时一次读取的块数。因此随着这个值的提高，FTS速度也相应会提升。而且db file scattered read 等待也会相应减少。但将该值在全系统上设定过高并不妥当，最好是利用alter session set 命令，只在执行sql语句期间临时提升这个值，因为这个值如果升高，有关FTS的cost会计算的较低，可能导致不可预料的SQL执行计划的变更。

使用较大的块也是提高FTS性能的方法，较大的块在如下两个方面改善FTS的性能，第一，增加一个块所包含的行数，这样组成相同大小的表时使用更少的块数，相应地multi block I/O次数也会减少。第二，如果数据块较大，则发生行链接或者行迁移的概率会降低，附加的I/O也随之降低。大部分OLTP系统上一般只使用标准块大小（8K），但是经常扫描大量数据的DSS上使用更大的块能改善性能。

三 oracle段层

设置合理的表分区可以有效的减少FTS范围，例如为了获得100万数据中的10万个数据而执行FTS时，将10万个数据相应的范围利用partitioning分开，则可以将FTS的范围缩小至十分之一。

四 OS/裸设备层

如果利用SQL的优化或者buffer cache的优化也不能解决问题，就应该怀疑I/O系统本身的性能。将db file scattered read 事件的等待次数和等待时间比较后，如果平均等待时间长，缓慢的I/O系统可能是主要的原因。

利用v$filestat视图，可以分别获得各数据文件关于multi block I/O和single block I/O的活动信息。

select f.file#, f.name,

s.phyrds,s.phyblkrd,s.readtim,–所有的读取工作信息

s.singleblkrds,s.singleblkrdtim,–single block I/O

(s.phyblkrd – s.singleblkrds) as multiblkrd,– multi block I/O次数

(s.readtim – s.singleblkrdtim) as multiblkrdtim,– multi block I/O时间

round(s.singleblkrdtim/decode(s.singleblkrds,0,1,s.singleblkrds),3) as singleblk_avgtim,–single block I/O平均等待时间（毫秒）

round((s.readtim – s.singleblkrdtim)/(s.phyblkrd – s.singleblkrds),3) as multiblk_avgtim — multi block I/O平均等待时间（毫秒）

from v$filestat s, v$datafile f

where s.file#=f.file#

and s.phyblkrd – s.singleblkrds !=0;

如果特定文件上平均执行时间表现的过高，则应该通过该文件所在的I/O系统的性能，以此改善性能。没有关于multi block I/O的最合理的平均等待时间值，但一般应该维持在10微秒左右的平均等待时间。

Metalink相关note：

WAITEVENT: “db file scattered read” Reference Note [ID 34558.1]

Systemwide Waits:

If the TIME spent waiting for multiblock reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at <> whereBLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).

It can also 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 may be worth tracing:

SELECT sid, total_waits, time_waited

FROM v$session_event

WHERE event=’db file scattered read’

and total_waits>0

ORDER BY 3,2

Delete or Update running slow — db file scattered read waits on index range scan [ID 296727.1]

适用范围：9.2.0.6和更高版本。

问题描述：

Sql运行缓慢，通过设置10046事件发现db file scattered read 等待事件，并且db file scattered read 发生在一个索引上。

然而执行计划表明表在使用index range scan，并且没有迹象表明出现全表扫描或者全索引扫描，但是，对索引的index range scan应该会造成db file sequential reads等待事件。

同时，单次db file scattered read所取得的数据块数是11，这个数字这与

db_file_multiblock_read_count并不一样。

原因：

这是由在index range scan操作期间对索引叶子节点的预提取造成的。oracle可以在对索引预提取的同时为了维护所以而进行删除和更新操作。并且，默认的，所有索引开启预提取功能，预提取的数据块数目取决于隐含参数_db_file_noncontig_mblock_read_count ，该参数默认值为11.

_index_prefetch_factor 参数影响关于提取索引期间需要执行的I/O操作数量的优化估计。

解决方案：

索引预提取操作只有很小的几率会对查询造成影响，可以通过设置_db_file_noncontig_mblock_read_count 为0或者1来关闭预提取功能。然而通常不建议这么做。

下面的选项可以尝试用来提高查询的性能：

1 重建当前索引

2 基于where子句对应列创建更优化的索引。

How to Tell if the IO of the Database is Slow [ID 1275596.1] （未全部阅读）

判断I/O相应时间：

下面是常见等待时间以及通常情况下可接受的等待时间。

Troubleshooting I/O-related waits [ID 223117.1] （未阅读）

Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows) [ID 554366.1]

大概：某表的pctfree过低（仅仅设置为5），并且更新操作非常频繁，导致大量行迁移的产生，表中90%的行属于迁移行，这导致了全表扫描一次仅读取一个数据块。

当使用pctfree为20重建表后，行迁移消失，问题得到解决。

适用范围：

Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 11.1.0.6 – Release: 8.1.7 to 11.1

References

NOTE:102989.1 – How to Find and Eliminate Migrated and Chained RowsNOTE:122020.1 – Row Chaining and Row Migration

疑问：与OWI中关于行迁移对db file scattered read介绍有所出入，需要用实验验证。

TROUBLESHOOTING: Advanced Query Tuning [ID 163563.1] （未阅读）