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無效。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31544156/viewspace-2219225/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【等待事件】db file scattered read事件
- 等待事件db file sequential read、db file scattered read和direct read的區別事件
- 【TUNE_ORACLE】等待事件之IO等待“db file scattered read”Oracle事件
- db file sequential read等待事件事件
- 【等待事件】db file sequential read事件
- Oracle:db file scattered readOracle
- 0316理解db file parallel read等待事件Parallel事件
- 0322理解db file parallel read等待事件2Parallel事件
- 【TUNE_ORACLE】等待事件之IO等待“db file sequential read”Oracle事件
- [20210315]理解db file parallel read等待事件3.txtParallel事件
- [20210315]理解db file parallel read等待事件4.txtParallel事件
- 【TUNE_ORACLE】等待事件之IO等待“db file parallel write”Oracle事件Parallel
- I/O上的等待事件 —— control file sequential read/control file parallel write事件Parallel
- direct path read/read temp等待事件事件
- read by other session等待事件Session事件
- read by other session 等待事件分析Session事件
- log file sync等待事件事件
- 【等待事件】log file sync事件
- cell single block physical read等待事件BloC事件
- 【TUNE_ORACLE】等待事件之IO等待“read by other session”Oracle事件Session
- 【TUNE_ORACLE】等待事件之IO等待“direct path read”Oracle事件
- 【TUNE_ORACLE】等待事件之日誌等待“log file sync”Oracle事件
- 【WAIT】 log file sync等待事件說明AI事件
- log file sync等待事件處理思路事件
- 【TUNE_ORACLE】等待事件之日誌等待“log file parallel write”Oracle事件Parallel
- [20201204]關於等待事件Log File Sync.txt事件
- read content in a text file in pythonPython
- Solidity事件,等待事件Solid事件
- 【TUNE_ORACLE】等待事件之等待事件類別Oracle事件
- db block gets 與 consistent read getsBloC
- [20181130]control file sequential read.txt
- C# read excel file via ExcelDataReaderC#ExcelLDA
- DB BUFFER LRU 列表的latch等待
- The Db2 Recovery History FileDB2
- Selenium等待事件Waits事件AI
- ORACLE 常見等待事件Oracle事件
- latch等待事件彙總事件
- Latch free等待事件(轉)事件