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

时间:2021-08-26 07:53:24

为什么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微妙左右的平均等待时间。

说明:总结于网络

http://blog.csdn.net/zq9017197/article/details/7925338