db file scattered read等待事件有三个参数，属于User I/O类的等待
：
SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name = 'db file scattered read';
NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
------------------------------ ---------- ---------- ---------- ----------
db file sequential read file# block# blocks User I/O
file#：要读取的数据块锁在数据文件的文件号。
block#：要读取的起始数据块号。
blocks：要读取的数据块数目。
当会话发出一个读入多个数据块的I/O请求时提交db file scattered read(数据文件离散读取)等待事件
。过程如图：
db file scattered read等待事件发生的原因
：
1.全表扫描
2.索引快速全扫描
在此，我们来分别对2种情况进行验证。
首先，创建测试表：
SQL> create table scott.tb_test(id int,age int,name varchar2(20));
Table 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 9305
Oracle pid: 22, Unix process pid: 9305, 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_9305.trc
对测试表进行操作，持续观察追踪文件的输出。
1.全表扫描
当前session中执行如下语句： SQL> alter system flush buffer_cache; System altered. SQL> select * from scott.tb_test where age = 2; ID AGE NAME ---------- ---------- ----- 1 2 test 查看追踪文件： *** 2018-11-08 14:22:24.399 WAIT #139755673442328: nam='SQL*Net message from client' ela= 9621966 driver id=1650815232 #bytes=1 p3=0 obj#=91054 tim=1541658144399459 CLOSE #139755673442328:c=0,e=9,dep=0,type=3,tim=1541658144399580 ===================== PARSING IN CURSOR #139755673515256 len=41 dep=0 uid=0 oct=3 lid=0 tim=1541658144399734 hv=3789676117 ad='55ad24638' sqlid='493dsa3hy3rkp' select * from scott.tb_test where age = 2 END OF STMT PARSE #139755673515256:c=144,e=113,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1092599453,tim=1541658144399732 EXEC #139755673515256:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1092599453,tim=1541658144399967 WAIT #139755673515256: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=91054 tim=1541658144400005 WAIT #139755673515256: nam='db file sequential read' ela= 13 file#=5 block#=178 blocks=1 obj#=91054 tim=1541658144400098 WAIT #139755673515256: nam='db file scattered read' ela= 20 file#=5 block#=179 blocks=5 obj#=91054 tim=1541658144400359 FETCH #139755673515256:c=0,e=427,p=6,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=1092599453,tim=1541658144400458 WAIT #139755673515256: nam='SQL*Net message from client' ela= 179 driver id=1650815232 #bytes=1 p3=0 obj#=91054 tim=1541658144400675 FETCH #139755673515256:c=0,e=44,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=1092599453,tim=1541658144400760 STAT #139755673515256 id=1 cnt=1 pid=0 pos=1 obj=91054 op='TABLE ACCESS FULL TB_TEST (cr=8 pr=6 pw=0 time=414 us cost=3 size=38 card=1)' WAIT #139755673515256: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=91054 tim=1541658144400842 可看到 发生了一次单块读和一次多块读，其中单块读是访问scott.tb_test的头块 ： SQL> select owner,header_file,header_block from dba_segments where segment_name = 'TB_TEST'; OWNER HEADER_FILE HEADER_BLOCK ------------------------------ ----------- ------------ SCOTT 5 178 多块读是读取的scott.tb_test的数据块 ： SQL> select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from scott.tb_test order by 2; DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ------------------------------------ ------------------------------------ 5 179 5 182 5 183
2.索引快速全扫描
创建索引： SQL> create index scott.idx_id on scott.tb_test(id); Index created. 当前session中执行如下语句： SQL> alter system flush buffer_cache; System altered. SQL> select id from scott.tb_test where id = 100; 查看跟踪文件： PARSING IN CURSOR #139755673927112 len=43 dep=0 uid=0 oct=3 lid=0 tim=1541659656338252 hv=2432667479 ad='55b721790' sqlid='1vgubku8gz3ur' select id from scott.tb_test where id = 100 END OF STMT PARSE #139755673927112:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=578627003,tim=1541659656338250 EXEC #139755673927112:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=578627003,tim=1541659656338348 WAIT #139755673927112: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=91056 tim=1541659656338387 WAIT #139755673927112: nam='db file sequential read' ela= 20 file#=5 block#=187 blocks=1 obj#=91057 tim=1541659656338473 WAIT #139755673927112: nam='db file sequential read' ela= 9 file#=5 block#=188 blocks=1 obj#=91057 tim=1541659656338534 FETCH #139755673927112:c=163,e=192,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=578627003,tim=1541659656338602 WAIT #139755673927112: nam='SQL*Net message from client' ela= 158 driver id=1650815232 #bytes=1 p3=0 obj#=91057 tim=1541659656338804 FETCH #139755673927112:c=89,e=88,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=578627003,tim=1541659656338941 STAT #139755673927112 id=1 cnt=1 pid=0 pos=1 obj=91057 op='INDEX RANGE SCAN IDX_ID (cr=3 pr=2 pw=0 time=186 us cost=1 size=13 card=1)' WAIT #139755673927112: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=91057 tim=1541659656339036 可看到此时执行计划走的是 索引范围扫描，此时等待事件是db file sequential read 。添加hint，强制走索引快速全扫描： SQL> alter system flush buffer_cache; System altered. SQL> select /*+ index_ffs(tb_test idx_id)*/ id from scott.tb_test where id = 100; ID ---------- 100 查看跟踪文件： PARSING IN CURSOR #139755673927112 len=75 dep=0 uid=0 oct=3 lid=0 tim=1541659831795488 hv=2021045085 ad='55a741cc0' sqlid='8hbw5jtw7dcux' select /*+ index_ffs(tb_test idx_id)*/ id from scott.tb_test where id = 100 END OF STMT PARSE #139755673927112:c=2808,e=2974,p=10,cr=11,cu=0,mis=1,r=0,dep=0,og=1,plh=3381169862,tim=1541659831795487 EXEC #139755673927112:c=16,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3381169862,tim=1541659831795676 WAIT #139755673927112: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=91056 tim=1541659831795714 WAIT #139755673927112: nam='db file sequential read' ela= 10 file#=5 block#=186 blocks=1 obj#=91057 tim=1541659831795799 FETCH #139755673927112:c=810,e=116,p=1,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=3381169862,tim=1541659831795853 WAIT #139755673927112: nam='SQL*Net message from client' ela= 130 driver id=1650815232 #bytes=1 p3=0 obj#=91057 tim=1541659831796029 WAIT #139755673927112: nam='db file scattered read' ela= 6 file#=5 block#=189 blocks=2 obj#=91057 tim=1541659831796106 FETCH #139755673927112:c=0,e=107,p=2,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=3381169862,tim=1541659831796169 STAT #139755673927112 id=1 cnt=1 pid=0 pos=1 obj=91057 op='INDEX FAST FULL SCAN IDX_ID (cr=7 pr=3 pw=0 time=92 us cost=4 size=13 card=1)' WAIT #139755673927112: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=91057 tim=1541659831796258 可看到此时 执行计划走了索引快速全扫描，并且出现了一个单块读(读索引的块头)和一个多块读 ，多块读发生的对象正好是索引： SQL> select owner,segment_name,segment_type from dba_extents where file_id = 5 and 189 between block_id and block_id+blocks-1; OWNER SEGMENT_NAME SEGMENT_TYPE ---------- -------------------- -------------------- SCOTT IDX_ID INDEX
注意：
1.平均等待时间不应该超过20ms, 否则证明存储存在争用。 2.OLTP系统应避免出现此等待事件。 3.优化方式：加大SGA, 避免全表扫描，提高存储性能，对频繁访问的做cache等。 4.调高数据库参数: db_file_multiblock_read_count可降低等待次数，但大于1MB/blocks_size无效。
