db file sequential read事件

Oracle db file sequential read

  这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，可能没有正确的使用驱动表；

或者可能说明不加选择地进行索引。在大多数情况下我们说，通过索引可以更为快速的获取记录，所以对于一个编码规范、调整良好的数据库，这个等待很大是很正常的。但是在很多情况下，使用索引并不是最佳的选择，比如读取较大表中大量的数据，全表扫描可能会明显快于索引扫描，所以在开发中我们就应该注意，对于这样的查询应该进行避免使用索引扫描。

诊断方法：通常要找到那些会话读取那些对象时产生db file sequential read，进而找到对应的sql语句进行优化

在会话级，查询视图V$SESSION_WAIT时如果有该事件存在，那么该视图中的

P1－表示文件编号FILE#

P2－表示块编号BLOCK#

P3－表示读取的块数BLOCKS，也就是数据库在FILE#上第BLOCK#开始读取的数据库块数。

由参数P1与P2推得访问的数据对象：

select s.segment_name, s.partition_name
  from dba_extents s
where <P2的值> between s.block_id and (s.block_id + s.blocks -1) and s.file_id = <P1的值>

在实例级，通过STATSPACK报告中的FILE IO和TABLESPACE IO部分可以判断出读取最频繁的表空间或数据文件。

通过下面的查询可以搜索出那些会话正在执行顺序读取：

SELECT sid, total_waits, time_waited

FROM v$session_event

WHERE event='db file sequential read'

and total_waits>0

ORDER BY 3,2;

获取各个session中db file sequential read等待事件的总的等待时间，和等待时间所占总的等待时间(各种等待事件的总和时间)的比例
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;

获取那些对象上产生了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;

通常的解决方法：

块读取通常是不可避免的，但可以通过最小化不必要的IO避免不必要的块读取。

检查使用了不合适的索引扫描的SQL，优化SQL语句；

如果该语句的执行计划是table access by index rowed,检查索引的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'
在上述sql语句的输出结果中，如果dba_indexes.clustering_factor接近表中块的数量，那么表中大多数行是排序的。这是期望的，然而，如果clustering factor接近表中行的数量，它意味着表中的行是随机排列，这种情况对于同样叶块中的索引块来说，指向同样的数据块中的行是不可能的，因此它会导致更多的I/O来完成操作。你可以采取rebuilding表来改善索引clustering factor，为了行根据索引键来排序，其后重建索引。