db file sequential read等待事件

db file sequential read等待事件有三个参数，属于User I/O类的等待：

 SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name = 'db file sequential read';

 NAME                           PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS

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

 db file sequential read        file#      block#     blocks     User I/O

 file#：要读取的数据块锁在数据文件的文件号。

 block#：要读取的起始数据块号。

 blocks：要读取的数据块数目（这里应该等于1）。

当进程需要访问一个在SGA中不存在的block时，进程会等待Oracle将此block从disk读取到SGA中。在这个过程中发生的等待事件即db file sequential read，过程如图：

db file sequential read等待事件是发生的常见原因：

 1.索引的访问及回表

 2.访问undo segment或者回滚操作

 3.直接以ROWID方式访问表中的数据

在此，我们来分别对3种情况进行验证。

首先，创建测试表及索引：

 SQL> create table scott.tb_test(id int,age int,name varchar2(20));

 Table created.

 SQL> create index scott.idx_id on scott.tb_test(id);

 Index created.

插入测试数据：

 SQL> begin

  2  for i in 1..1000 loop

  3  insert into scott.tb_test values(i,i+1,'test');

  4  end loop;

  5  commit;

  6  end;

  7  /

 PL/SQL procedure successfully completed.

在另外的session中对当前session进行10046追踪：

 SQL> oradebug setospid 3836

 Oracle pid: 22, Unix process pid: 3836, image: oracle@zhuga (TNS V1-V3)

 SQL> oradebug event 10046 trace name context forever,level 12

 Statement processed.

 SQL> oradebug tracefile_name

 /opt/app/oracle/diag/rdbms/bddev2/BDDEV2/trace/BDDEV2_ora_3836.trc

持续观察追踪文件的输出

1.索引的访问

 查看当前session的累积等待信息：

 SQL> SELECT   INST_ID

  2  ,SID

  3  ,EVENT

  4  ,TOTAL_WAITS WAITS_CNT

  5  ,TIME_WAITED

  6  ,AVERAGE_WAIT/100 "AVERAGE_WAIT(S)"

  7  ,MAX_WAIT/100 "MAX_WAIT(S)"

  8    FROM   GV$SESSION_EVENT 

  9   WHERE SID=&INPUT_SID

 10  ORDER BY TOTAL_WAITS DESC;

 Enter value for input_sid: 812

 old   9:  WHERE SID=&INPUT_SID

 new   9:  WHERE SID=812

 

    INST_ID        SID EVENT                           WAITS_CNT TIME_WAITED AVERAGE_WAIT(S) MAX_WAIT(S)

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

          1        812 db file sequential read               207           15           .0007         .01

          1        812 SQL*Net message to client              29           0               0           0

          1        812 SQL*Net message from client            28      215952         77.1256      680.52

          1        812 Disk file operations I/O                6           0           .0001           0

          1        812 log file sync                           5           0           .0005           0

          1        812 events in waitclass Other               3           5           .0156         .02

          1        812 db file scattered read                  1           0               0           0

 当前session中执行如下语句：

 SQL> alter system flush buffer_cache;

 System altered.

 SQL> select /*+ index(tb_test,idx_id)*/ age from scott.tb_test where id = 500;

 追踪文件内容：

 PARSING IN CURSOR #140128573892064 len=72 dep=0 uid=0 oct=3 lid=0 tim=1541577174155088 hv=968314915 ad='55a3880b0' sqlid='d53f2unwvfn13'

 select /*+ index(tb_test,idx_id)*/ age from scott.tb_test where id = 500

 END OF STMT

 PARSE #140128573892064:c=94,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1923716061,tim=1541577174155086

 EXEC #140128573892064:c=42,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1923716061,tim=1541577174155379

 WAIT #140128573892064: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=91021 tim=1541577174155417

  WAIT #140128573892064: nam='db file sequential read' ela= 11 file#=5 block#=147 blocks=1 obj#=91022 tim=1541577174155531

 WAIT #140128573892064: nam='db file sequential read' ela= 11 file#=5 block#=150 blocks=1 obj#=91022 tim=1541577174155602

 WAIT #140128573892064: nam='db file sequential read' ela= 11 file#=5 block#=134 blocks=1 obj#=91021 tim=1541577174155659

 FETCH #140128573892064:c=227,e=239,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1923716061,tim=1541577174155687

 WAIT #140128573892064: nam='SQL*Net message from client' ela= 154 driver id=1650815232 #bytes=1 p3=0 obj#=91021 tim=1541577174155878

 FETCH #140128573892064:c=101,e=100,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=1923716061,tim=1541577174156014

 STAT #140128573892064 id=1 cnt=1 pid=0 pos=1 obj=91021 op='TABLE ACCESS BY INDEX ROWID TB_TEST (cr=4 pr=3 pw=0 time=217 us cost=1 size=26 card=1)'

 STAT #140128573892064 id=2 cnt=1 pid=1 pos=1 obj=91022 op='INDEX RANGE SCAN IDX_ID (cr=3 pr=2 pw=0 time=256 us cost=1 size=0 card=1)'

 WAIT #140128573892064: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=91021 tim=1541577174156117

 

 SQL> select owner,object_name,object_type from dba_objects where object_id = 91022;

 OWNER                OBJECT_NAME          OBJECT_TYPE

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

 SCOTT                IDX_ID               INDEX

 或者

 SQL> select owner,segment_name,segment_type from dba_extents where file_id = 5 and 134 between block_id and block_id+blocks-1;

 OWNER                SEGMENT_NAME   SEGMENT_TYPE

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

 SCOTT                TB_TEST        TABLE

 可看到发生了 三次db file sequential read等待，其中两次是访问索引(一次访问索引头块，另一次是访问真正数据块)，一次是回表

 此时查看当前session的累积等待信息：

    INST_ID        SID EVENT                             WAITS_CNT TIME_WAITED AVERAGE_WAIT(S) MAX_WAIT(S)

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

          1        812 db file sequential read                 210          15           .0007         .01

          1        812 SQL*Net message to client                32           0               0           0

          1        812 SQL*Net message from client              31      233915         75.4564      680.52

          1        812 Disk file operations I/O                  6           0           .0001           0

          1        812 log file sync                             5           0           .0005           0

          1        812 events in waitclass Other                 4           6           .0152         .02

          1        812 db file scattered read                    1           0               0           0

 可看到信息是一致的。

2.访问undo或回滚

 在另外的session中修改scott.tb_test数据，不提交：

 SQL> update scott.tb_test set age = 100 where id = 500;

 1 row updated.

 在当前session执行：

 SQL> alter system flush buffer_cache;

 System altered.

 SQL> select age from scott.tb_test where id = 500;

        AGE

 ----------

        501

 PARSING IN CURSOR #140128575016040 len=44 dep=0 uid=0 oct=3 lid=0 tim=1541577933156059 hv=2526155070 ad='55a80a9a0' sqlid='0x9dgqqb9449y'

 select age from scott.tb_test where id = 500

 END OF STMT

 PARSE #140128575016040:c=0,e=125,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1923716061,tim=1541577933156058

 EXEC #140128575016040:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1923716061,tim=1541577933156190

 WAIT #140128575016040: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=91021 tim=1541577933156224

  WAIT #140128575016040: nam='db file sequential read' ela= 18 file#=5 block#=147 blocks=1 obj#=91022 tim=1541577933156326

 WAIT #140128575016040: nam='db file sequential read' ela= 12 file#=5 block#=150 blocks=1 obj#=91022 tim=1541577933156396

 WAIT #140128575016040: nam='db file sequential read' ela= 11 file#=5 block#=134 blocks=1 obj#=91021 tim=1541577933156456

 WAIT #140128575016040: nam='db file sequential read' ela= 12 file#=3 block#=176 blocks=1 obj#=0 tim=1541577933156512

 WAIT #140128575016040: nam='db file sequential read' ela= 12 file#=3 block#=32616 blocks=1 obj#=0 tim=1541577933156594

 FETCH #140128575016040:c=140,e=410,p=5,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=1923716061,tim=1541577933156658

 WAIT #140128575016040: nam='SQL*Net message from client' ela= 164 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1541577933156863

 FETCH #140128575016040:c=88,e=88,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=1923716061,tim=1541577933156988

 STAT #140128575016040 id=1 cnt=1 pid=0 pos=1 obj=91021 op='TABLE ACCESS BY INDEX ROWID TB_TEST (cr=6 pr=5 pw=0 time=409 us cost=1 size=26 card=1)'

 STAT #140128575016040 id=2 cnt=1 pid=1 pos=1 obj=91022 op='INDEX RANGE SCAN IDX_ID (cr=3 pr=2 pw=0 time=262 us cost=1 size=0 card=1)'

 WAIT #140128575016040: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1541577933157090

 可看到，除了 两次索引访问和一次回表 之外，还有 两次db file sequential read等待是发生在file#=3,block#为176和32616上 ，其中176是对象的头块，查看此对象(dba_extents或者DBA_UNDO_EXTENTS)：

 SQL> select segment_name,segment_type from dba_extents where file_id = 3 and 176 between block_id and block_id + blocks - 1

   2  union all

   3  select segment_name,segment_type from dba_extents where file_id = 3 and 32616 between block_id and block_id + blocks - 1

   4  ;

 

 SEGMENT_NAME                                                                      SEGMENT_TYPE

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

 _SYSSMU4_1254879796$                                                              TYPE2 UNDO

 _SYSSMU4_1254879796$                                                              TYPE2 UNDO

 说明对undo段的访问可引起db file sequential read等待。

 

3.通过ROWID访问表数据其实与索引回表是一样的，因此不再单独验证。

注意：

 1.对db file sequential read等待来说，绝大多数系统无法避免。db file sequential read等待比较多也不算是坏事，关键要看是否合理

 2.平均等待时间不应该超过7ms, 否则证明存储存在争用。

 3.优化方式：加大SGA, 降低总体物理读，提高存储性能，选择合理的索引，对频繁访问的表做cache等

查看各session的db file sequential read等待情况：

select a.sid,

       a.event,

       a.total_waits,

       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  wait_class <> 'Idle'

        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.session_id,

       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$active_session_history 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.session_id,

       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$active_session_history 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;

查看数据文件上单块读的平均等待时间：

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;

          

某个session自登录以来所有等待事件的情况：

SELECT INST_ID,

       SID,

       EVENT,

       TOTAL_WAITS WAITS_CNT,

       TIME_WAITED,

       AVERAGE_WAIT/100 "AVERAGE_WAIT(S)",

       MAX_WAIT/100 "MAX_WAIT(S)"

FROM GV$SESSION_EVENT 

WHERE SID=&INPUT_SID

ORDER BY TOTAL_WAITS DESC;