db file sequential read

1.这个事件出现的情况

1. 需要的信息不在SGA中，需要从磁盘中读取
2. db_file_multiblock_read_count参数值为1（这个是不是依然是db file scatt read,只不过这个p3的值应该是1）（需要验证）
   
3. extent边界，在一个extent中要读的块只有1个（需要验证，一个extent里面只有一条记录则肯定只有一个block）
4. 在buffer cache中已经有部分块
5. 数据文件头部（这个在什么情况下出现，需要验证）
6. 控制文件（这个在什么是否发生）
7. 索引（这个在什么时候发生）
8. 回滚段（这个在什么时候发生）
9. 借助于rowid访问表

2.如何查看这个事件的相关统计信息

db file sequential read等待事件有3个参数：file＃，first block＃，和block数量，奇怪这个p3存在干什么既然已经确定一次只读取1个。
查看哪个session的db file sequential read事件所占用的事件比例比较高， TIME_WAITED和AVERAGE_WAIT

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;

-----查看到底哪个sql所产生的磁盘IO比较大
除了DISK_READS字段外，oracle10g中的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语句。其实这个还是没有办法确定到底哪个sql语句有问题，因为等待事件已经发生了。
----------如果等待事件的历史保留则查看到底哪个对象是发生db file sequential read的对象

目的：根据db file sequential read中的P1,P2两个参数得到对象名和分区名（该等待事件单块读等待的对象名和分区名），使用v$bh的缺点你必须等待块被读入到buffer cache中；否则X$BH视图在buffer中没有P1,P2参数所指的信息。DBA_OBJECTS视图也不包含P1和P2所指的rollback或undo段对象：

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;

3.解决方法

如果在索引上存在大量的db file sequential read
1.定位到底是那个sql产生的io比较大

select a.sid, a.seq#, a.event, a.p1text,
a.p1, a.p1raw, a.p2text, a.p2,
a.p2raw, a.p3text, a.p3, a.p3raw,
a.wait_time, a.seconds_in_wait, a.state, b.serial#,
b.username, b.osuser, b.paddr, b.logon_time,
b.process, b.sql_hash_value, b.saddr, b.module,
b.row_wait_obj#, b.row_wait_file#, b.row_wait_block#,
b.row_wait_row#
from v$session_wait a, v$session b
where a.sid = b.sid
and b.username is not null
and b.type <> 'BACKGROUND'
and a.event in (
'db file sequential read',
'db file scattered read',
'latch free',
'direct path read',
'direct path write',
'enqueue',
'library cache pin',
'library cache load lock',
'buffer busy waits',
'free buffer waits');

select hash_value,address,piece,sql_text

from v$sqltext

where hash_value =

order by piece;

2.如果执行计划是table access by index rowid，检查索引的clustering factor也是值得做的。

select id.index_name,tb.table_name,id.clustering_factor,tb.num_rows,tb.blocks
from dba_indexes id,dba_tables tb
where id.table_name=tb.table_name
and tb.table_name='&1' and tb.owner='&2';

如果DBA_INDEXES.CLUSTERING_FACTOR接近表中块的数量，那么表中大多数行是排序的。这是期望的。然而，如果clustering factor接近表中行的数量，它意味着表中行是随机排列。这种情况，对于在同样叶块中的索引块来说，指向同样数据块中行是不可能的，因此它要求更多I/Os来完成这操作。你可以采取rebuilding表来改善索引clustering fator，为了行根据索引键来被排序，其后重建索引。如果表不只有一个索引，什么会发生？好，它会下降。你仅能迎合最多使用的索引

3.也检查来看是否应用有最近已经引入一个新的索引，通过以下查询。新索引的引入可能导致优化器为访问表的SQL语句选择一个不同的执行计划。新计划可能产生一个比旧计划更好的，中性的，或糟糕的性能。

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;

OPTIMIZER_INDEX_COST_ADJ和OPTIMIZER_INDEX_CACHING初始化参数能影响优化器去采用nested loops操作和在全表扫描上选择一个索引访问路径。OPTIMIZER_INDEX_COST_ADJ参数默认是100。较低的值哄骗优化器认为索引访问路径更便宜。OPTIMIZER_INDEX_CACHING参数默认值是0。较高的值通知优化器一个更高的百分比索引块已经在buffer cache中，nested loops操作更便宜。一些第三方的应用使用这方法来改善索引使用。这些参数的不合适的使用能导致重大的I/O等待时间。查明会话正以什么值运行。直到9i数据库，这信息仅能通过跟踪以trace event 10053的级别1的会话，并检查trace文件。在oracle10g中，这可以查询v$ses_optimizer_env视图。

确保所有对象的统计数据是当前数据的典型，因为不准确的统计数据的确会导致优化器生成糟糕的不该用索引读却调用索引读的执行计划。记住，统计数据需要是有代表性的，而不必最新的，并且执行计划可能在统计数据被收集的每一次而改变。

注意：当使用一个低estimate比例值分析表或索引的时候，oracle正常情况使用单个块读，这将增加该会话的db file sequential read统计数据（v$session_event）和实例（v$system_event）。

针对表（table access by index rowid）的sequential reads

你可以看db file sequential read等待事件，通过P1,P2参数得到是表而不是索引。对于SQL语句来说通过从索引获得的rowid访问表是正常的，如下面解释计划显示，当通过rowid来读一个表的时候，oracle使用一个单独块I/O：

LVL OPERATION OBJECT

--- --------------------------------- ---------------------

1 SELECT STATEMENT

2 TABLE ACCESS BY INDEX ROWID RESOURCE_ASGN_SNP

3 INDEX RANGE SCAN RESOURCE_ASGN_SNP_4IX

4.相关案例

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7928987/viewspace-364746/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7928987/viewspace-364746/