db file sequential read (本文由thomaswoo_dba翻译,转载请注明出处)
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_typefrom dba_objects a, v$session_wait b, x$bh cwhere c.obj = a.object_id(+)and b.p1 = c.file#(+)and b.p2 = c.dbablk(+)and b.event = 'db file sequential read'unionselect 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_typefrom dba_objects a, v$session_wait b, x$bh cwhere 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 0145 66 optimizer_index_cost_adj YES 100145 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_oldfrom v$system_event a, v$instance border 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_wrtndev8-0 3.93 17.03 34.66 54592552 111099454dev8-1 12.08 56.68 99.93 181659920 320286944dev8-2 23.38 194.11 189.93 622154550 608747464dev8-3 16.00 230.43 128.04 738570544 410383416dev8-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_waitfrom v$filestat a, dba_data_files b where a.file# = b.file_id and a.singleblkrds > 0order 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
其中SINGLEBLKRDTIM是centiseconds(本篇完)