oracle在执行全表扫描(Full Table Scan,FTS)或全索引扫描(Index Full San)时,为保障性能,尽量一次读取多个块,这称为Multi Block I/O。每次执行Multi Block I/O,都会等待物理I/O结束,此时等待db file scattered read事件。
SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'db file scattered read';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- ------------------------------ --------------- --------------- ---------------
144 db file scattered read file# block# blocks
这个等待事件包含三个参数:
file# :要读取的数据块所在数据文件的文件号。
block#:读取的起始数据块号。
blocks:需要读取的数据块数目。
这样就可以找到那个对象:
select event,p1,p2,p3,row_wait_obj# from gv$session where event = 'db file scattered read';
select object_name,object_type from dba_objects where object_id=row_wait_obj#;
Oracle按照db_file_multiblock_read_count(以下简称MBRC)参数值进行Multi Block I/O。这个值每个OS都有最大的界定,可以通过如下方法确认最大值。
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
SQL> alter system set db_file_multiblock_read_count=100000; --试图变更为超大值
系统已更改。
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 4096
--确认4096是一次可以读取的最多块数
oracle在执行FTS时也进行Single Block I/O。这时即便是FTS也会发生db file sequential read等待。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。
对于行链接和行迁移的不同点需要准确的理解:
行链接是在行的大小比块的大小大的时候发生。因此使用更大的块或减少PCTFREE值之外,再没有可以消除行链接的方法。行迁移起初记录到一个块里,但随着行的大小不断增大,块内没有空间时发生迁移。这时实际上移动到另外的块,在原来的行里插入了指明转移后块和行位置的ROWID。行迁移特别是在通过索引扫描表时会给性能带来严重影响,这是因为读取一个行时需要读取两个或两个以上的块。行迁移对FTS带了的影响需要稍微留意。FTS是对HWM以下的所有块从头开始顺序读取的工作。在执行FTS过程中,oracle如果遇到行迁移,则不会引发更多的Single Block I/O,而是继续工作。这是因为知道反正要在扫描过程中需要重新读取。因此如果HWM的位置相同,则不管行迁移存在与否,FTS自身性能几乎不变。当然如果发生了迁移,就会追加分配区,如果HWM移动得更远,就会给FTS的性能造成影响。消除行迁移时要留心以上部分。
在DBA_TABLES.CHAIN_CNT列上记录发生链接或迁移的行数。
SQL> select TABLE_NAME,CHAIN_CNT from user_tables;
TABLE_NAME CHAIN_CNT
------------------------------ ----------
T_COL_ROW 0
T_ROW_COL 0
TEST 0
已选择3行。
行链接是在行的大小比块的大小大的时候发生。所以除修改表的定义的或降低PCTFREE值重新创建表,或者使用更大的块之外别无他法。对行链接利用exp/imp重组表没有任何意义。但是将PCTFREE值设定得过小,可能会引起其他的性能问题,因此需要对此进行考虑。虽说发生了行链接,但不一定发生附加的I/O。select ... 里的所有列如果都在第一个访问的块里面,一次执行I/O就可以得到所需结果,所以应该养成在select语句里不取没有必要列的习惯。
行迁移与行链接不同,最初是正常插入到一个块内,但之后因块里的剩余空间用尽时,执行update导致超过剩余空间的情况下发生。这时整个行将搬到新的块,最初的位置则记录当前移动的ROWID位置。欲解决这个问题,在创建表初始应合理设置PCTFREE值。赋予足够PCTFREE值,因update发生迁移的概率就会降低,幸亏行迁移可以通过重建表来消除。消除行迁移的方法如下:
先导出(exp)后导入(imp)
执行alter table xxx move ...
通过以上操作虽然消除了迁移,但没有根本上解决问题。如果应用程序的处理模式相同,则随着时间的推移相同的问题会重现。所以与其重建表这样“治标”,不如通过对PCTFREE调整、完善应用程序等根本性的解决方法“治本”。
db file scattered read事件与db file sequential read事件相同,是oracle中最经常发生的等待事件。因为从数据文件读取块时只能执行Multi Block I/O或Single Block I/O。
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段层
需要检查,通过合理执行partition能否减少FTS范围。例如为获得100万个数据中10万个数据而执行FTS时,将10万个数据相应的范围利用partition分开,则可以将FTS的范围缩小至1/10。
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