db file scattered read等待事件

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無效。