Oracle之 db file sequential read等待事件优化思想

共计 4318 个字符，预计需要花费 11 分钟才能阅读完成。

为什么 db file sequential read 事件在 full table scan 操作中显现，为什么在多块读中为什么会有单块读存在？

extent 的大小 : 当扩展区中的最后一组块仅是 1 个块，Oracle 使用单块读来提取这个块。这正常来说不是一个问题，除非你扩展区尺寸太小。以下是一个 event 10046 的 trace 文件，显示在全表扫描操作中包围的 db file sequential read 事件。表块尺寸是 8K，MBRC 是 8 个块，扩展区尺寸是 72K（9 个块）。如果表是大的，对表的全表扫描将导致许多 db file sequential read 事件。如果是这种情况，全表扫描操作将完成的较快，如果表以一个较大的扩展区尺寸重建的话。

cached block: 在 multiblock 读的一组中的 1 个或多个块已经在 buffer cache 中了，因此 oracle 把 fetch 分割成 2 个或多个读，它可以有单块或多块 I / O 组成。例如，如果 MBRC 是 8，块 3 和块 7 是在 buffer cache 中，oracle 将提出 3 个读呼叫――第一个是块 1 和块 2，第二个是块 4 和块 6，第三个是块 8。因此第三个 fetch 是单数据块，等待事件就是 db file sequential read。然而，对于前 2 个读呼叫，这等待事件是 db file scattered read，因为块的数量是超过 1 的。因此，被缓存的块能导致全表扫描操作来执行比所需更多的读。

chained or migrated rows: 这就是一个问题，当 sql 语句的执行计划请求一个全表扫描的时候，如果你看到很多对该表的 db file sequential read 等待。这象征了表有许多链接或移植的行。Oracle 使用单块读 i / o 来访问每一个链接的或移植的行。检查在 DBA_TABLES 视图中的表的 CHAIN_CNT。当然，CHAIN_CNT 是 LAST_ANALYZED 日期开始的。被移植的行能通过重组表来纠正（譬如 export 和 import，或 alter table move）。

补充：
行迁移 成因：当发出 update 导致记录行长增加，block 的剩余空间不足以存放这条记录，就会产生行迁移，发生行迁移时 rowid 不会改变，原来的 block 中会用一个指针存放这条记录在新的 block 中的地址，发生行迁移会对性能产生影响，因为读这条记录会读两个 BLOCK。

行链接 成因：当一个 BLOCK 不足以存放下一条记录的时候，就会发生行连接，这个时候 oracle 会把这条记录分成几个部分，分别存放在几个 block 中，然后把这几个 block chain 起来。行连接同样会影响性能，因为读一条记录至少会读两个 BLOCK.

index entry creation : 它不是一个问题，当你在 sql 语句执行计划呼叫一个全表扫描的时候，如果你看见许多针对 index 的 db file sequential read 等待。在以下例子中，TABLE_A 有一个索引，db file sequential read 等待是读 index 块到 SGA 来充满来自 TABLE_B 数据的结果。注意 db file sequential read 等待与 db file scattered read 统计数据上比较的数量。这暗示你不能再假设你将从执行计划上就能看到是哪个瓶颈。许多 DBAs 希望看到许多 db file scattered read 事件。另一个一文不值的观点就是 db file sequential read 等待事件不会应用于 insert 语句。一般的误解就是它仅应用到 update 和 delete 语句。

解决对策：

1、应用程序层
需要筛选出主要发生 db file scattered read 等待的 sql 语句。如果不必要的执行 FTS 或 Index Full San，修改 sql 语句或创建更合理的索引就可以解决。大量读取数据时多数情况下 FTS 性能更好。不是盲目的创建索引，而是要考虑相应的 sql 语句后，判断 FTS 有利，还是 Index Full San 有利。

2、oracle 内存层
如果高速缓存区过小，就会反复需要物理 I /O，相应的 db file scattered read 等待也会增加。这时 free buffer waits 等待事件一同出现的几率较高。FTS 引起的 db file scattered read 等待的严重性不仅在于需要 I /O，而且在于降低高速缓存区的效率，进而影响会话工作。从这种角度出发，处理 FTS 的有效方法之一就是使用多重缓冲池。读取一次后不再使用的数据，有必要保存到高速缓存区从而导致影响其他用户的工作吗？多重缓冲池虽然是有效管理高速缓存区的强有力的方法，但是遗憾的是没有被广泛使用。多重缓冲池从三个方面改善高速缓存区的性能。第一，将经常访问的对象保存与内存，进而将物理 I / O 最小化。第二，临时性数据所占用的内存被快速的重新使用，进而将内存的浪费最小化。第三，因为每个缓冲池各使用不同的 cache buffers lru chain 锁存器，所以有减少锁存器争用的效果。指定 DEFAULT 将适用默认的缓冲池。这个选项适用于没有分配给 KEEP 缓冲池和 RECYCLE 缓冲池的其它数据库对象。通常将经常访问的对象放入 KEEP 缓冲池中，指定 KEEP 将把数据块放入 KEEP 缓冲池中。维护一个适当尺寸的 KEEP 缓冲池可以使 Oracle 在内存中保留数据库对象而避免 I / O 操作。通常将偶尔访问的大表放入 RECYCLE 缓冲池中，指定 RECYCLE 将把数据块放入 RECYCLE 缓冲池中。一个适当尺寸的 RECYCLE 缓冲池可以减少默认缓冲池为 RECYCLE 缓冲池的数据库对象的数量，以避免它们占用不必要的缓冲空间。
有效使用 FTS 的另一种方法是将 db_file_multiblock_read_count 参数值提高。这个参数决定执行 Multi Block I/ O 时一次读取的块数。因此这个值高，FTS 速度相应也会提升，而且 db file scattered read 等待也会相应减少。将这个值在全系统级上设定得高，并不太妥当。最好是利用 alter session set … 命令，只在执行 sql 语句期间提升这个值。因为这个值如果升高，有关 FTS 的费用会算的较低，可能会导致 sql 执行计划的变更。
较大的块也是提高 FTS 性能的方法。较大的块在如下两个方面改善 FTS 的性能。第一，增加一个块所包含的行数，这样相同大小的表时使用更少的块数，相应的 Multi Block I/ O 次数也会减少。第二，块的大小较大，则发生行链接或行迁移的概率会降低，附加的 I / O 也随之降低。大部分 OLTP 系统上一般只是用标准块大小（8K）。但是经常扫描大量数据的 OLAP 上使用更大的块能改善性能。

3、oracle 段层

3.1、需要检查，通过合理执行 partition 能否减少 FTS 范围。例如为获得 100 万个数据中 10 万个数据而执行 FTS 时，将 10 万个数据相应的范围利用 partition 分开，则可以将 FTS 的范围缩小至 1 /10。

3.2、查看表的行迁移、行链接、pctfree，如果行迁移数量过高，pctfree 过低，则增大 pctfree，重建表。如果行链接过高，则加大 BLOCK 块
— 查看行迁移 / 行链接
SQL> analyze table emp list chained rows;
SQL> select count(*) from chained_rows where table_name=’EMPLOYEES_TEMP’;

行迁移加大表的 pctfree，重建表。

行链接只有通过加大 BLOCK 块的方式才可以避免，如下：
create tablespace dba_16k
blocksize 16K
datafile ‘/home/oracle/dba_16k.DBF’ size 100M
autoextend on
extent management local
segment space management auto;
alter table EMPLOYEES_TEMP move tablespace dba_16k;
alter index idx_emp_id rebuild ;
delete from chained_rows ;
commit;
analyze table EMPLOYEES_BK list chained rows into chained_rows;
select count(*) from chained_rows where table_name=’EMPLOYEES_TEMP’;

4、OS/ 裸设备层
如果利用 sql 的优化或高速缓存区的优化也不能解决问题，就应该怀疑 I / O 系统本身的性能。将 db file scattered read 事件的等待次数和等待时间比较后，如果平均等待时间长，缓慢的 I / O 系统成为原因的可能性高。之前也讨论过，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 平均等待时间（cs）
round((s.readtim – s.singleblkrdtim) /
 nullif((s.phyblkrd – s.singleblkrds), 0),
 3) as multiblk_avgtim –Multi Block I/O 平均等待时间（cs）
from v$filestat s, v$datafile f
 where s.file# = f.file#;

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

说明：总结于网络

更多 Oracle 相关信息见Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

：