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

为什么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

Linux公社的RSS地址： https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址： https://www.linuxidc.com/Linux/2018-08/153358.htm