详解 db file sequential read 等待事件

db file sequential read 事件有三个参数:file#,first block#, block count, 在oracle 10g里,此等待事件在归于 User I/O wait class 下面的. 处理db file sequential read 事件要牢牢把握下面三个主要思想:
1)oracle 进程需要访问的block不能从SGA 中获取,因此oracle 进程会等待block从I/O读到SGA
2)两个重要参数TIME_WAITED,AVERAGE_WAIT,是以单个session获取的
3)影响较大的db file sequential read 一般很像应用程序问题

Common Causes, Diagnosis, and Actions

db file sequential read 等待事件被SQL 语句初始化,主要从index,rollback(or undo) segments, tables(通过rowid访问表),control files 和data file headers中进行single-block read.

访问数据对象(table,index)总是会产生Physical I/o需求,当出现db file sequential read等待事件时,并不意味着数据库产生系统问题,基至它大量出现都不是一件坏事.真正要引起注意的是像enqueue 和latch free等待事件,它们总是引起系统性题的根源.并且它们使single-block(单块读取)变得因难了.

那么什么情况下, 当出现db file sequential read等待事件,才可以视为性能问题呢?
什么情况下,db file sequential read可以视为系统的超额负担,并且基准线应该怎样去定义?
这是一个比较复杂的问题.在没有工业标准指引的情况下.我们要依据数据库运行环境来制定标准线.
比如,我们定义超过多少时间的db file sequential read等待事件,可以视为性能问题,还可以用最原始的方法,那就是等待用户抱怨.

在V$SESSION_EVENT视图中,db file sequential read的高TIME_WAITED是较为容易发现的,当时因为V$SESSION_EVENT是记录从实例启动以来的数据,所以我们同以前的TIME_WAITED进行比较,当然跟同一个session,同一个LOGON_TIME的非空闲事件进行比较是可以的,也是比较准确的.当实例不间断运行很长一段时间(数天或数星期)之后,TIME_WAITED的累计值就会很高,这当然不能说是性能问题.

select a.sid,
a.event,
a.time_waited,
a.time_waited / c.sum_time_waited * 100 pct_wait_time,
round((sysdate - b.logon_time) * 24) hours_connected
from v$session_event a, v$session b,
(select sid, sum(time_waited) sum_time_waited
from v$session_event
where event not in (
'Null event',
'client message',
'KXFX: Execution Message Dequeue - Slave',
'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue',
'PX Deq: Table Q Normal',
'Wait for credit - send blocked',
'PX Deq Credit: send blkd',
'Wait for credit - need buffer to send',
'PX Deq Credit: need buffer',
'Wait for credit - free buffer',
'PX Deq Credit: free buffer',
'parallel query dequeue wait',
'PX Deque wait',
'Parallel Query Idle Wait - Slaves',
'PX Idle Wait',
'slave wait',
'dispatcher timer',
'virtual circuit status',
'pipe get',
'rdbms ipc message',
'rdbms ipc reply',
'pmon timer',
'smon timer',
'PL/SQL lock timer',
'SQL*Net message from client',
'WMON goes to sleep')
having sum(time_waited) > 0 group by sid) c
where a.sid = b.sid
and a.sid = c.sid
and a.time_waited > 0
and a.event = 'db file sequential read'
order by hours_connected desc, pct_wait_time; 
(本条语句来源于OWI 材料,但是本SQL语句计算的结果是不精确的,因为session sid是时时改变的)

减少db file sequential read 等待事件,我们可以从两方面入手:
1)第一条当然是优化SQL语句,以减少物理读和逻辑读
2)第二条是从统计上减少平均等待时间(比如优化最高wait_time的等待事件)
备注:特别是给客户看结果时效果最明显,因为图形给人的感观是比较明显的

相对每一条来说,除非用你10046事件或自己做一个不间断等待事件程序,不然是非常难以锁定哪一条SQL引起长时间的wait_time.退一步讲,当前的SQL也不一定就是引起wait_time的原因.所以我们发现要解决等待事件的问题没有历史数据是很困难的.

你也可以通过查询V$SQL视图获取平均DISK_READS,当然我们不能就认为此SQL就属于某个SESSION,所以下次对session进行trace,一般可以定位SQL,然后优化SQL以减少物理读与逻辑读.

备注:除了DISK_READS之外,oracle 10g为V$SQL 和V$SQLAREA视图增加了一些另人兴奋不己的新列:
USER_IO_WAIT_TIME
DIRECT_WRITES
APPLICATION_WAIT_TIME
CONCURRENCY_WAIT_TIME
CLUSTER_WAIT_TIME
PLSQL_EXEC_TIME
JAVA_EXEC_TIME
当然我们通过高累计的USER_IO_WAIT_TIME去定位SQL是可能的,但V$SQL和V$SQLAREA两个视图的访问速度是较慢的.
另外可以减少db file sequential read等待事件影响的方法是减少AVERAGE_WAIT ,AVERAGE_WAIT列是一个session等待single block被从硬盘获取的平均等待时间(英文好读,中文有点扭,主要我的水平不够)
This is the average time a session has to wait for a single block fetch from disk(英文原句).AVERAGE_TIME是V$SESSION_EVENT视图中的列.在高速的存储系统中,平均的single-block读不能够超过10ms(milliseconds,千分之一秒) 或1cs(centiseconds,百分之一秒).一般的情况下,SAN(storage area network,网络存储)的AVERAGE_TIME平均等待事间在4至8ms之间,因为SAN的cache都较大.
AVERAGE_TIME的值越大,single-block读的系统资源开耗也随之增大,也即进程的响应时间会受到影响.

从另外一个方面来讲,较低的AVERAGE_TIME值反应进程等待single-block读的时间会较短.当然
AVERAGE_TIME调优的优先级远没有SQL优化的优先级高,因为优化一个占用大量资源的SQL的效果是非常明显和有效的.

需要注意的db file sequential read 并不总是对index对像进行资源占用,有时也会对table/partition对像进行资源占用.所以我们需要将P1/P2参数的值进行转换,在此我们会用到视图DBA_EXTENTS以获取对像名.
但是DBA_EXTENTS是一个复杂的,响应极慢的视图.要想用快一点的方法,X$和DBA_OBJECTS将是一个更好的选择.因为X$BH不占用BUFFER_CACHE所以,访问X$BH会有I/O产生,还有就是DBA-OBJECTS视图不包括rollback 和undo 段,所以如果db file sequential read访问这两个对象,也是不能被解析的.
查询的例子:

select b.sid,
       nvl(substr(a.object_name,1,30),
                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
       a.subobject_name,
       a.object_type
from   dba_objects a, v$session_wait b, x$bh c
where  c.obj = a.object_id(+)
and    b.p1 = c.file#(+)
and    b.p2 = c.dbablk(+)
and    b.event = 'db file sequential read'
union
select b.sid,
       nvl(substr(a.object_name,1,30),
                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
       a.subobject_name,
       a.object_type
from   dba_objects a, v$session_wait b, x$bh c
where  c.obj = a.data_object_id(+)
and    b.p1 = c.file#(+)
and    b.p2 = c.dbablk(+)
and    b.event = 'db file sequential read'
order  by 1;

  SID OBJECT_NAME               SUBOBJECT_NAME            OBJECT_TYPE
----- ------------------------- ------------------------- -----------------
   12 DVC_TRX_REPOS             DVC_TRX_REPOS_PR64        TABLE PARTITION
  128 DVC_TRX_REPOS             DVC_TRX_REPOS_PR61        TABLE PARTITION
  154 ERROR_QUEUE               ERROR_QUEUE_PR1           TABLE PARTITION
  192 DVC_TRX_REPOS_1IX         DVC_TRX_REPOS_20040416    INDEX PARTITION
  194 P1=22 P2=30801 P3=1
  322 P1=274 P2=142805 P3=1
  336 HOLD_Q1_LIST_PK                                     INDEX

像本例中的object_type,如果是table,要进SQL进行相应的优化.

Sequential Reads Against Indexes

db file sequential read 主要的问题不是对index的访问,而且超额的对错误index的访问.当系统的
访问路径发生更改时,可能对效能慢的index进行访问,从而产生等待.当然如果一个SQL执行了大量的index读
这也可能是一个性能问题.所以分析SQL的执行计划是一个比较好的方法,当要用FULL TABLE SCAN时,用index
就会产生性能问题.还有就是FIRST_ROWS 和ALL_ROWS的问题,当然从大的方面讲OLTP与DSS的混用也会产生不
合时适的db file sequential read.还有关于驱动表(driving table)的问题.不对的驱动表,性能也不会好.

记住,所有的努力的目的应该是一样的,那就是降低logical and physical I/Os

下面有个种方法:
1)分析SQL,弄清SQL的逻辑,看看SQL到底想获取什么,然后优化,甚至重写
2)将index放在快磁盘上,尤其不要放在RAID-5上,因为慢磁盘导致高average time,然而I/O优化的优先级
  不可以高于SQL CODE的优化.因为SQL有问题再快的磁盘的也不行,最好用OUTLINE稳固执计计划,尤其是第三方软件
3)关于index表,最好将数据进行排列,以减少I/O.可以通过DBA_INDEXS.CLUSTERING_FACTOR来查看index有没有达到
  表的所有块的数量,如有是,说明大部份列是排列的,如是不是,表时表是随机排列的.这时可以通过重组表以解决问题.
4)看看表最近没有没建立新的index,使SQL的执行计划发生改变.(下面的语句可以查看到)
  看看有没有invalid的index.

select owner, 
       substr(object_name,1,30) object_name, 
       object_type, 
       created
   from   dba_objects
   where  object_type in ('INDEX','INDEX PARTITION')
   order by created;

5)OPTIMIZER_INDEX_COST_ADJ 和OPTIMIZER_INDEX_CACHING

(来源于网上)其次，由于测试环境的不同，Tom的测试结果是在缺省值（100）的环境下，
就已经和上面取值500时一样了，即对T2全表扫描而T1使用索引。Tom试验中，减小取值直至0，
访问路径就变成使用两个索引，而并不会出现均不使用索引的情况。除去系统的不同
（可能导致取缺省值时访问路径是否一致），只看变化趋势，显然10g中灵活性更高
，1-10000的取值使得CBO可以覆盖所有的访问路径。另一方面，正如Tom的结论所说，
OPTIMIZER_INDEX_COST_ADJ的取值越大，优化器越倾向于使用全表扫描，取值越小，
优化器越倾向于使用索引。
                 再次，我们对比相同访问路径下的不同点。在取值从1变化到200（1-50-100-200）
的过程中，优化器计算出的代价是持续增长的，而从1000到10000则是不变的。
这说明这个参数与索引I/O的代价有关，而和全表扫描并无关系，这与Tom所说的并不矛盾，
不过显然更精确一点。
                 最后我们其实应该看到，虽然有如上所说的代价变化问题，
同一访问路径下实际的运行性能并无区别，由于数据量比较小，上面的例子也许不能很好的说明这一点，
不过想想Oracle用相同的路径去执行，也没有理由不同性能吧。
              OPTIMIZER_INDEX_CACHING值为0,值越大,系统越tendence去用nested loops .
Find out what values the sessions are running with. Up to Oracle9i Database,
 this information could only be obtained by tracing the sessions with the trace 
event 10053 at level 1 and examining the trace files. In Oracle Database 10g, 
this is as simple as querying the V$SES_OPTIMIZER_ENV view.
可以通过10053事件查看SESSION相应的OPTIMIZER_INDEX_COST_ADJ 和OPTIMIZER_INDEX_CACHING值是多少,
在10g中省不了事,直接查V$SES_OPTIMIZER_ENV视图就可以了,下面的是例子:

select * FROM V$SES_OPTIMIZER_ENV  WHERE NAME=LOWER('OPTIMIZER_INDEX_COST_ADJ') or 
                                         name=lower('OPTIMIZER_INDEX_CACHING');

SID   ID    NAME                       ISDEFAULT  VALUE
--------------------------------------------------------
144   67    optimizer_index_caching    YES        0
145   66    optimizer_index_cost_adj   YES        100
145   67    optimizer_index_caching    YES        0

因为oracle的optimizer依赖于表与索引的statistics,所以要确保现在的statistics能够代表现有数据,
不正确的statistics会让optimizer 产生低效的执行计划,当然statistics也不必天天更新,因为这样的话,
执行计划就也会天天更新,这对性能问题的分析会产生干扰

System-Level Diagnosis

V$SYSTEM_EVENT视图为系统级别的诊断提供数据,基中AVERAGE_TIME和TIME_WAITED与I/O相关事件关联
记住TIME_WAITED只是记录自实例启动以来的记录,当实例运行比较长的一段时间后,db file sequential read 
通常较高.当然,经常查询V$SYSTEM_EVENT并且以TIME_WAITED排序,能够通过相互比较而找到比较明显的等待事件.
当db file sequential read 不位于top five时,不要担心,因为可能有更大的问题要去发现
当db file sequential read 位于top five时,也总能说明数据库进行了大量的single-block读.
这里可以看系统级别的诊断能力是非常受限的.但事件总是两面情,这里却可以看系统硬件上瓶颈
这在v$session_wait事件里可是看不到的.当你想升级系统,可是你的直接上司要求你提供系统瓶颈报告时,
下面就是那个好办法:

select a.event, 
       a.total_waits, 
       a.time_waited, 
       a.time_waited/a.total_waits average_wait,  这里的average_wait是很用的
       sysdate – b.startup_time days_old
from   v$system_event a, v$instance b
order by a.time_waited;  
当average single-block读超过你所定的阀门的时候,你要看看I/O子系统是不是得到优化了.
当然用操作系统的I/O控制命令(iostat,vmstat)去监控硬盘,可以发现I/O的瓶颈,
可以去评估各I/O子系统之间是不是平衡.
Device:                  tps         Blk_read/s         Blk_wrtn/s         Blk_read         Blk_wrtn
dev8-0                  3.93              17.03              34.66         54592552  111099454
dev8-1                 12.08              56.68              99.93  181659920  320286944
dev8-2                 23.38             194.11             189.93  622154550  608747464
dev8-3                 16.00             230.43             128.04  738570544  410383416
dev8-4                  4.73              59.89              80.98  191965458  259557752
通过上例,可以看到dev8-2,dev8-3的块读写是远远超过其它的,所以可以考虑平衡一下I/O

另外,除了从V$SYSTEM_EVENT视图中进行系统级别的db file sequential read average wait之外,
oracle也提供了另外一个视图v$filestat来获取single-block读的统计数据.

select a.file#, 
       b.file_name, 
       a.singleblkrds, 
       a.singleblkrdtim, 
       a.singleblkrdtim/a.singleblkrds average_wait
from   v$filestat a, dba_data_files b 
where  a.file# = b.file_id   
and    a.singleblkrds > 0
order by average_wait;


FILE# FILE_NAME                     SINGLEBLKRDS SINGLEBLKRDTIM AVERAGE_WAIT
----- ----------------------------- ------------ -------------- ------------ 
  367 /dev/vgEMCp113/rPOM1P_4G_039          5578            427   .076550735
  368 /dev/vgEMCp113/rPOM1P_4G_040          5025            416    .08278607
  369 /dev/vgEMCp113/rPOM1P_4G_041         13793           1313   .095193214
  370 /dev/vgEMCp113/rPOM1P_4G_042          6232            625   .100288832
  371 /dev/vgEMCp113/rPOM1P_4G_043          4663            482   .103366931
  372 /dev/vgEMCp108/rPOM1P_8G_011        164828         102798   .623668309
  373 /dev/vgEMCp108/rPOM1P_8G_012        193071         125573    .65039804
  374 /dev/vgEMCp108/rPOM1P_8G_013        184799         126720   .685717996
  375 /dev/vgEMCp108/rPOM1P_8G_014        175565         125969   .717506337