oracle等待事件I/O上的等待事件

时间:2022-08-12 07:50:56

1、db file scattered read

oracle在执行全表扫描(FTS:full table scan)或全索引扫描(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都有最大值的界定,可以通过如下方法确认最大值:

SQL> show parameter db_file_multiblock_read
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
db_file_multiblock_read_count        integer     16
SQL> alter system set db_file_multiblock_read_count=100000;    ——设置一个超大值,看看最大能到多少。
系统已更改。
SQL> show parameter db_file_multiblock_read
NAME                                                        TYPE      VALUE
-----------------------------------               -----------   ------------------
db_file_multiblock_read_count        integer       128

测试完之后就可以改回原来的值了。

oraclle在执行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个块时,若其中第3个块被缓存,oracle将前2个块通过multi block I/O读取,对于第3个块执行一次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的性能造成影响。消除行迁移时留心以上部分。

针对I/O层,讨论一下对于db file scatterd read 等待的解决方法:

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

oracle 9i开始提供了视图v$sql_plan,可以很快地帮助我们找到那些全表扫描或者 fast full index 扫描的sql语句,这个视图会自动忽略关于数据字典的sql语句:

查找全表扫描的sql语句可以使用以下语句:

select sql_text from v$sqltext t,v$sql_plan p  where t.hash_value= p.hash_value and p.operation= 'TABLE ACCESS' and p.options= 'FULL' order by p.hash_value,t.piece;

查找fast full index 扫描的sql语句可以使用以下语句:

select sql_text from v$sqltext t,v$sql_plan p  where t.hash_value= p.hash_value and p.operation= 'INDEX' and p.options= 'FULL' order by p.hash_value,t.piece;

可以查看物理读取块数最多的sql语句的执行计划,看里面是否包含了全表扫描的fast full index 扫描。可以通过以下语句来查看物理读取最多的sql语句:

select sql_text from (select * from v$sqlarea order by disk_reads) where rownum<=10;


2)oracle内存层:如果高速缓冲区过小,就会反复需要物理I/O,相应的db file scatterd read等待也会增加。这时 free buffer waits等待事件一同出现的几率较高。FTS引起的db file scattered read等待的严重性不仅在于需要I/O,而且在与降低高速缓冲区的效率,进而影响会话的工作。从这种角度出发,处理FTS的有效方法之一就是使用多重缓冲池。多重缓冲池从三个方面改善缓冲区的性能,1、经常访问的对象保存于内存,进而物理I/O最小化。2、临时性数据所占用的内存被快速重新使用,进而将内存的浪费最小化。3、每个缓冲池各使用不同的cache buffer lru chain锁存器,所以有减少锁存器争用的效果。

有效使用FTS的另一种方法是将DB_FILE_MULTIBLOCK_READ_COUNT参数值提高。但是最好是在会话级别(alter session set 。。。)而不要在系统级别(alter system set 。。);在会话级别,只在执行sql语句期间提升这个值,因为这个值如果升高,有关FTS的费用会算的较低,可能会导致SQL执行计划变更。

使用较大的块也是提高FTS性能的方法。较大的块在如下两个方面改善FTS的性能。主要在两个方面1、包含的行数增大;2、行链接行迁移概率降低。

3)oracle段层:需要检查,通过合理执行partitioning能否减少FTS范围,例如为获得100万个数据中10万个数据而执行FTS时,将10万个数据相应的范围利用partitioning分开,则可以将FTS的范围缩小至十分之一。

4)OS/裸设备层:如果利用SQL的优化,或高速缓冲区的优化也不能解决问题,就应该怀疑I/O系统本身的性能。将db file scttered read 事件的等待次数和等待时间比较后,如果平均等待时间长,缓慢的I/O系统成为原因的可能性高。之前也讨论过,I/O系统上的西能耐问题在多种情况下会发生,因此需要充分调查各种原因。利用V$FILESTAT视图,可分别获得各数据文件关于multi block I/O和 single block I/O的活动信息。


2、db file sequential read

如果db file scattered read 事件是伴随着multi block I/O发生的等待事件,那db file sequential read 事件就是伴随single block I/O发生的等待事件。每次发生single block I/O时,就会发生一次db file sequential read 事件等待。single block I/O可以发生在从文件读取一个块的所有工作上,一般在索引扫描,通过rowid的表扫描、读取控制文件和文件头(file header)时发生。db file sequential read 事件的P1=file#,P2=block#, P3=blocks, 与db file scattered read事件相同。若P2=1,就表明文件头块已读。

db file sequential read 等待使性能出现问题,这些性能问题大多数发生在低效的索引扫描、行迁移、行连接引发的I/O过程中。虽然因为db file sequential read 等待与索引相关,所以存在与db file scattered read 等待相比db file sequential read 等待被忽视的 倾向,但是使用索引不一定比FTS更好。

下面针对oracle的I/O层,讨论发生db file sequential read 等待问题情况及解决方法:

1)应用程序层:低效的SQL语句或低效的索引扫描经常被使用时,因不必要的物理I/O增加,可能增加db file sequential read 等待。使用选择性较差的索引是发生db file sequential read 等待的主要原因。使用不当的索引可能引发I/O争用,而且还可能引发告诉缓冲区争用。只需将SQL语句优化,有效使用索引,就能防患于未然。

总是拥有最新统计信息,也是相当重要的,利用dbms_stats程序包,可以对数据库内的所有对象以最优的方式更新统计信息。

偶尔有这种情况,在sql*plus等环境下执行时使用合理索引的sql语句,在等实际应用程序过程中,有事会不当的使用索引,引起db file sequential read 等待,导致性能大幅下降。同样在使用bind 变量时也发生这种问题,应该检查是否正使用bind peeking功能(关于bing peeking的详细介绍参考我的博客:http://blog.csdn.net/changyanmanman/article/details/7988614)。从9i起,基本上使用bind peeking 功能。这个功能如被激活,即便使用了bind 变量的sql语句,也可以利用在最初的执行计划。如果被确认为因为发生这种现象导致db file sequential read等待增加,建议将_OPTIM_PEEK_USER_BINDS隐含参数值修改为false。此值为false后,将无法使用bind peeking 功能。


2)oracle内存层:如果高速缓冲区过小,就会反复发生物理I/O,因此可能增加db file sequential read 等待,这是同时发生free buffer waits 等待的概率较高。这时应该和db file scattered read 等待的原理一样,处理的方法也是相同的。

即便恰当创建了索引,db file sequential read 等待依然比期待的时间长,就需要考虑以下事项:

1)clustering factor(CF)是否过高?

clustering factor(集群因子,简称CF)意味对索引表的集群度。CF是在假设内存大小只能载入一个块,这时伴随索引扫描所需的表扫描次数的计算值,更准确地说,它表示沿着索引的叶块,在rowid值上代表块编号的第1--15位的值与之前rowid做出比较后备更替的次数。为了让大家理解这个概念,举一个例子就能清楚了:由五个块组成的索引和由五个块组成的表,而一个块里有四个行,因此总行数是5*4=20个,顺序地扫描索引的同时读取相对应的表,这时可能有两个极端的情况:
***CF最低时:如果一个索引块所包含的rowid都被一个表块所包含,则通过索引扫描表时,只扫描索引5次和表5次,就能如愿获得想要的数据。此时CF(集群因子)是5(表的扫描次数),CF的最小值与表的块数相同。

***CF最高时:如果一个索引块所包含的rowid分别被不同的表快所包含,则通过索引扫描时,必须扫描25次【5(索引块数)+5(索引块

数)*4(各索引块需要扫描的表块数)=25次】才能获得想要的数据。这时CF是20(表块的扫描次数),CF的最大值与表行数相同。

假设I/O不适用内存,CF越高读取表块的次数越多,因此物理I/O也会增加。即,CF越高,通过rowid读取表块的次数就会越多,db file sequential read 等待也会相应增加。通过高速缓冲存储区读取过的块因为不再发生物理I/O,因此CF高,未必一定会降低SQL语句的性能。但是大量扫描CF值高的索引,则需要读取的表块增加。因此可能给性能带来致命的打击。

利用analyze命令或DBMS_STATS程序包,可以获得索引的CF,将创建的索引统计信息的CF值记录在DBA_INDEXSE.CLUSTEERING_FACTOR列。如果CF与表的块数相当就是 好现象,如果与行数相当就不是好现象。如果SQL语句的性能问题被判断为CF,则将索引扫描替换为FTS是比较好的解决方法。还可以使用其他的索引。如果所有这些都不能得到满意的结果,则按照索引的排序顺序重新创建新表,也能解决这个问题。(可使用create table new_table as select ... from old_table order by indexed_column之类的命令)。但重新创建表应该是最后的选择。

再次强调一次,CF不合理不一定是导致性能缓慢,更重要的是正确理解问题发生原因。况且使用assm之类的管理方法时,存在比之前CF值有所盛盖的现象,因此判断是需要小心。


2)行迁移或行链接是否过多发生?

利用索引的ROWID扫描表时,因为改行的行链接或行迁移发生附加的I/O,因此db file sequential read 等待增加。利用analyze命令创建统计信息,则在dba_tables 视图的chain_cnt 列上记录发生链接或迁移的行数。利用v$sysstat视图或v$sesstat视图,可以间接确认链接或迁移发生与否。table fetch by rowid 统计值是通过rowid扫描的行次数,这个值再通过索引扫描表时增加。table fetch continued row 值是链接或迁移附加执行fetch 的次数。如果链接或迁移存在于多个块,则table fetch continued row值增加的值相当于发生这些链接或迁移的块数。因链接或迁移db file sequential read 等待增加时,消除相应现象就是解决方法。

行链接发生在行的大小大于块的大小时,所以除修改表的定义或降低pctfree值重新创建表,或者使用最大的块之外,别无他法。对行连接利用export/import重组表没有任何意义。但是将pctfree值设定的过小,可能会引起其他性能问题,因此需要对此进行考虑。虽说发生了行链接,但不一定发生附加的I/O。select..里所有的列如果都在第一个访问的块里,一次执行I/O就可以得到所需的结果,这是table fetch continued row 值不会增加。所以该养成在select 语句里不取没必要列的习惯。

行迁移与行链接不同,最初是正常插入到一个块内,但之后因块里剩余空间用尽时,执行update导致超过剩余空间的情况下发生。这时整个行将搬到新的块,最初的位置则记录当前移动的ROWID位置。欲解决这个问题,在创建表初始应该合理设置pctfree值,赋予足够pctfree值,因update发生行迁移的概率就会降低,幸亏行迁移可以通过重建表来消除。消除行迁移的方法如下:

***先输出(export)后输入(import)。

***执行alter table xxx move..

*** 利用执行analyze table xxx list chained rows into yyyy 筛选发生迁移的行,对于该行执行删除后插入。

通过以上操作虽然消除了迁移,但没有根本上解决问题,如果应用程序的处理模式相同,则随着时间推移相同的问题会重现。所以,与其重建表这样“治标”,不如通过对pctfree调整、完善应用程序等根本性的解决方法“治本”

3)OS/裸设备层:如果利用SQL的优化,或高速缓冲区的优化也不能解决问题,就应该怀疑I/O系统本身的性能。将db file sequential read 事件的等待次数和等待时间比较后,如果平均等待时间长,缓慢的I/O系统成为原因的可能性高。之前也讨论过,I/O系统上的西能耐问题在多种情况下会发生,因此需要充分调查各种原因。利用V$FILESTAT视图,可分别获得各数据文件关于multi block I/O和 single block I/O的活动信息。


3、db file parallel read 

当oracle从多个数据文件中并行读取多个block到内存的不连续缓冲区中(高速缓冲区或者是pga)时可能会出现这个等待事件。这种并行读取一般出现在恢复操作中 或者是从缓冲中欲取数据达到最优化(而不是多次从单个block中读取)。这个事件表明会话正在并行执行多个读取的需求。

在v$session_wait 这个视图里面,这个等待事件有3个参数P1, P2,P3, 其中P1代表有多少个文件被读取所请求。 P2代表总共有多少个blcok被请求。 P3代表总共有多少次请求。

 如果在等待事件中,这个等待事件的比重比较大,可以按照处理db  file sequential read 等待时间的方法来处理这个事件。


4.1、direct path read

direct path read 事件的等待是在执行parallel query时,slave session所执行的direct path I/O引发的。P1=file#,P2=start block#,P3=读取的块数。slave session 在执行direct path read 期间,coordinator session等待从slave session的响应, 可通过对PX Deq:execute reply事件的等待现象进行观察。执行parallel query时发生direct path read等待是必然结果。如果direct path read事件的等待时间过长,就应该在如下方面寻找调优点。

***提高parallel query 本身性能。执行paralle query 过程中的direct path read 等待是必然的,调优这个等待事件本身是不可能的,而通过对SQL进行调优,改善parallel query 本身性能是恰当的解决方式。比起系统容量 ,不必要的执行parallel query 反而成为性能下降的因素。请记住一点,对于数据文件执行直接读取工作之前,应该将读取的对象所在脏块写入到数据文件上。即,会发生检查点,执行这个工作期间coordinator session 将经历enq:TC-contention等待。

***提高I/O系统本身的性能。

oracle在需要预读(readahead)时,会执行direct path read ;记住一点,在没有执行paralle query 的情况下,如果db file scattered read/db file sequential read等待的同时发生direct path read 等待,就可以判断为oracle因为I/O负荷过大而执行direct path I/O引起的。

使用drect path I/O时,因为没有经过告诉缓冲区,如果发生高速缓冲区相关的争用时,就可以考虑使用它,如多个会话同时对相同的表执行FTS时,会因为latch:cache buffer chains 等待或read by other session等待,可能发生性能下降的现象。不仅如此,还降低高速缓冲区的效率,因此影响其他的会话性能。如果将FTS替换为prarllel query ,因为不经过高速缓冲区,所以与此相关的等待现象也将消失。但使用parallel query时,也可能发生CPU和内存使用量增加,检查点工作增加等负面影响,所以要慎重考虑后使用。

_DB_FILE_DIRECT_IO_COUNT隐含参数值决定direct path I/O的最大I/O缓冲区大小。从9i开始,这个值基本上去1M,但实际上是根据OS或硬件配置决定最大值的。将此值提高,parallel query 的吸能就可能提高,但大多数情况下可能使用的值是比1M小的值,实际上没有必要修改。

对于direct path read 要记住以下两点:

1)direct path read 虽然从数据文件直接读取数据,但是引用撤销的机制相同。即,direct path read 只是不经过SGA,保障读取一致性的方法则相同,证明这个的方法就是观察创建较小的撤销表空间后,执行paralle query 过程中,另外会话上过多执行dml时出现的错误ora-01555(snap-short too old) 这个错误可以解释为parallel query 的slave session 对数文件执行direct path read过程中,如果发现修改的块,就会参考回滚段数据。

2)如何证明在执行PQ时,slave session上的direct path read 不是对内存,而是对数据文件的direct path read ?一个会话执行PQ后,在执行PQ期间,另外会话上通过查询v$session_wait视图获得p1值,就可以确定对于哪个文件进行了direct path read。


4.2、direct path write

diect path write 等待意味着发生了directload工作(CTAS:create tbale as select 或 insert /*+ append */..等)这些工作呗请求时,oracle将不经过SGA在数据文件上执行直接写入工作。即,不是通过DBWR实现写入工作,而是通过服务器进程实现写入工作。CTAS(cerate table as select)或insert /* +append */ ,direct 模式执行SQL* Loader 时执行direct load工作,这些工作具有如下特点。

***不经过SGA,在数据文件上直接写入。

***在HWM之后添加块,不适用位图上管理的空闲块。

***对于创建的数据不创建回滚段(只是CTAS时,创建对于数据字典修改的撤销)

***表里有nologging选项时,不生成重做记录。

***对于表一exclusive模式获得TM锁,因此不允许其他会话执行DML。

如果合理的使用directload 操作,就可以快速创建大量数据。通过并行执行direct模式和parallel模式,可将性能进一步最优化。以PCTAS(parallel CTAS),insert/*+parallel(alias degree) */ 或direct parallel模式执行SQL*Loader就是代表性的例子。以parallel模式创建数据时,oracle按如下方法使用:

***各个slave session 在表所属的表空间内创建临时段以存储数据(请注意,即便不是临时表空间,也会创建临时段)这时,DBA_SEGMENTS.SEGMENT_TYPE列值时“TEMPORARY”

***各slave session在表所属的表空间内创建临时段在执行结束后,合并为一个临时段。

***提交执行之后,临时段合并为表段,HWM将被移动。

***执行回滚后,临时段将被drop。

如果是direct 模式,数据将被直接写入到表的段,但是与parallel模式并行时,暂时直接写入到表所属的永久表空间内的临时段后,在所有的工作结束之后再合并到表段上。

执行direct load 工作时发生的direct path write等待是必然的,而且不能减少等待的发生。如果direct path write 事件的平均等待时间过高,就可以判断为文件系统本身的性能存在问题。