[20210315]理解db file parallel read等待事件4.txt
[20210315]理解db file parallel read等待事件4.txt
--//上午的測試，自己有點奇怪的是前面看到的是
WAIT #140047000530088: nam='db file parallel read' ela= 212 files=1 blocks=39 requests=39 obj#=90881 tim=1615770668231443
--//後面變成了：
WAIT #140047000530088: nam='db file scattered read' ela= 111 file#=4 block#=773 blocks=39 obj#=90881 tim=1615770668233113
--//開始不理解,後面想一下應該後面出現的db file scattered read，掃描的塊是連續的，才會出現這樣的情況。
1.環境
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
----------------------------- ------- -------
db_file_multiblock_read_count integer 128
create table t pctfree 99 as select level id, rpad('ABC', 3500, 'X') vc from dual connect by level <= 500;
create index i_t_id on t(id) ;
exec dbms_stats.gather_table_stats(null, 'T', cascade=>true);
2.測試：
alter system flush buffer_cache ;
SCOTT@book> @ 10046on 12
Session altered.
SCOTT@book> select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 200;
MAX
----
AB
SCOTT@book> @ 10046off
Session altered.
=====================
PARSING IN CURSOR #140047000530088 len=78 dep=0 uid=83 oct=3 lid=83 tim=1615770668230603 hv=1658167056 ad='7bf671f0' sqlid='28mgrb1jdb7sh'
select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 200
END OF STMT
PARSE #140047000530088:c=2000,e=1812,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=714684053,tim=1615770668230597
EXEC #140047000530088:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=714684053,tim=1615770668230764
WAIT #140047000530088: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1615770668230831
WAIT #140047000530088: nam='db file sequential read' ela= 12 file#=4 block#=3499 blocks=1 obj#=90882 tim=1615770668230924
WAIT #140047000530088: nam='db file sequential read' ela= 9 file#=4 block#=3500 blocks=1 obj#=90882 tim=1615770668230986
WAIT #140047000530088: nam='db file parallel read' ela= 212 files=1 blocks=39 requests=39 obj#=90881 tim=1615770668231443
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #140047000530088: nam='db file sequential read' ela= 11 file#=4 block#=555 blocks=1 obj#=90881 tim=1615770668231562
WAIT #140047000530088: nam='db file parallel read' ela= 234 files=1 blocks=39 requests=39 obj#=90881 tim=1615770668231999
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #140047000530088: nam='db file sequential read' ela= 11 file#=4 block#=717 blocks=1 obj#=90881 tim=1615770668232202
WAIT #140047000530088: nam='db file parallel read' ela= 208 files=1 blocks=39 requests=39 obj#=90881 tim=1615770668232613
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #140047000530088: nam='db file sequential read' ela= 10 file#=4 block#=760 blocks=1 obj#=90881 tim=1615770668232803
WAIT #140047000530088: nam='db file scattered read' ela= 111 file#=4 block#=773 blocks=39 obj#=90881 tim=1615770668233113
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
WAIT #140047000530088: nam='db file sequential read' ela= 11 file#=4 block#=772 blocks=1 obj#=90881 tim=1615770668233402
WAIT #140047000530088: nam='db file scattered read' ela= 97 file#=4 block#=813 blocks=39 obj#=90881 tim=1615770668233694
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
WAIT #140047000530088: nam='db file sequential read' ela= 10 file#=4 block#=812 blocks=1 obj#=90881 tim=1615770668233992
FETCH #140047000530088:c=2999,e=3244,p=202,cr=202,cu=0,mis=0,r=1,dep=0,og=1,plh=714684053,tim=1615770668234126
STAT #140047000530088 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=202 pr=202 pw=0 time=3250 us)'
STAT #140047000530088 id=2 cnt=200 pid=1 pos=1 obj=90881 op='TABLE ACCESS BY INDEX ROWID T (cr=202 pr=202 pw=0 time=49475 us cost=203 size=701000 card=200)'
STAT #140047000530088 id=3 cnt=200 pid=2 pos=1 obj=90882 op='INDEX RANGE SCAN I_T_ID (cr=2 pr=2 pw=0 time=227 us cost=2 size=0 card=200)'
WAIT #140047000530088: nam='SQL*Net message from client' ela= 241 driver id=1650815232 #bytes=1 p3=0 obj#=90881 tim=1615770668234568
FETCH #140047000530088:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=714684053,tim=1615770668234610
WAIT #140047000530088: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=90881 tim=1615770668234640
*** 2021-03-15 09:11:11.508
WAIT #140047000530088: nam='SQL*Net message from client' ela= 3274193 driver id=1650815232 #bytes=1 p3=0 obj#=90881 tim=1615770671508880
CLOSE #140047000530088:c=0,e=10,dep=0,type=0,tim=1615770671508991
=====================
--//從跟蹤檔案可以發現出現3次db file parallel read,請求的次數39.
--//很奇怪的是你後面還看到nam='db file scattered read' ela= 111 file#=4 block#=773 blocks=39 obj#=90881.
3.可以驗證看看：
SCOTT@book> select * from dba_extents where segment_name='T';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T TABLE USERS 0 4 552 65536 8 4
SCOTT T TABLE USERS 1 4 560 65536 8 4
SCOTT T TABLE USERS 2 4 672 65536 8 4
SCOTT T TABLE USERS 3 4 688 65536 8 4
SCOTT T TABLE USERS 4 4 696 65536 8 4
SCOTT T TABLE USERS 5 4 712 65536 8 4
SCOTT T TABLE USERS 6 4 720 65536 8 4
SCOTT T TABLE USERS 7 4 728 65536 8 4
SCOTT T TABLE USERS 8 4 736 65536 8 4
SCOTT T TABLE USERS 9 4 744 65536 8 4
SCOTT T TABLE USERS 10 4 752 65536 8 4
SCOTT T TABLE USERS 11 4 760 65536 8 4
SCOTT T TABLE USERS 12 4 3464 65536 8 4
SCOTT T TABLE USERS 13 4 3472 65536 8 4
SCOTT T TABLE USERS 14 4 3480 65536 8 4
SCOTT T TABLE USERS 15 4 3488 65536 8 4
SCOTT T TABLE USERS 16 4 768 1048576 128 4
--//768+128-1 = 895
--//file#=4 block#=773 blocks=39 obj#=90881
--//file#=4 block#=813 blocks=39 obj#=90881
--//也就是這段區域分配block是連續的，也就是當掃描區域連續時，等待事件變成了db file scattered read。
--//視乎還是按照讀39塊的方式，你還可以注意一個細節。
WAIT #140047000530088: nam='db file scattered read' ela= 111 file#=4 block#=773 blocks=39 obj#=90881 tim=1615770668233113
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
WAIT #140047000530088: nam='db file sequential read' ela= 11 file#=4 block#=772 blocks=1 obj#=90881 tim=1615770668233402
--//後面出現的db file sequential read等待事件block#=772，而前面的是db file scattered read是block#=773。
SCOTT T TABLE USERS 17 4 896 1048576 128 4
SCOTT T TABLE USERS 18 4 1024 1048576 128 4
SCOTT T TABLE USERS 19 4 1152 1048576 128 4
20 rows selected.
--//從這裡也可以得到一個結論，即使是INDEX RANGE SCAN回表，也有可能出現db file scattered read的情況。
4.使用strace跟蹤看看。
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
253 29 55745 DEDICATED 55746 18 10 alter system kill session '253,29' immediate;
SCOTT@book> alter system flush buffer_cache ;
System altered.
SCOTT@book> select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 200;
MAX(
----
AB
$ strace -f -p 55746 -e pread 2>&1 | tee | grep -v "8192$"
Process 55746 attached - interrupt to quit
pread(258, "\6\242\0\0\5\3\0\1r\\|\27\3\0\2\4\1\275\0\0\1\0\0\0\rc\1\0O\\|\27"..., 319488, 6332416) = 319488
pread(258, "\6\242\0\0-\3\0\1r\\|\27\3\0\2\4\1\275\0\0\1\0\0\0\rc\1\0O\\|\27"..., 319488, 6660096) = 319488
--//注意看讀的數量不是8192.是319488,319488/8192 = 39.
--//6332416/8192 = 773
--//6660096/8192 = 813
$ strace -f -p 55746 -e pread 2>&1 | tee | grep -A1 "319488$"
pread(258, "\6\242\0\0\5\3\0\1r\\|\27\3\0\2\4\1\275\0\0\1\0\0\0\rc\1\0O\\|\27"..., 319488, 6332416) = 319488
pread(258, "\6\242\0\0\4\3\0\1r\\|\27\3\0\2\4\1\275\0\0\1\0\0\0\rc\1\0O\\|\27"..., 8192, 6324224) = 8192
pread(258, "\6\242\0\0-\3\0\1r\\|\27\3\0\2\4\1\275\0\0\1\0\0\0\rc\1\0O\\|\27"..., 319488, 6660096) = 319488
pread(258, "\6\242\0\0,\3\0\1r\\|\27\3\0\2\4\1\275\0\0\1\0\0\0\rc\1\0O\\|\27"..., 8192, 6651904) = 8192
--//6324224/8192 = 772
--//6651904/8192 = 812
--//資訊也與跟蹤轉儲一致。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2762941/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210315]理解db file parallel read等待事件3.txtParallel事件
- 0322理解db file parallel read等待事件2Parallel事件
- 0316理解db file parallel read等待事件Parallel事件
- db file scattered read等待事件事件
- db file sequential read等待事件事件
- 【等待事件】db file sequential read事件
- 【等待事件】db file scattered read事件
- Oracle db file parallel write 和 log file parallel write 等待事件 說明OracleParallel事件
- 詳解 db file sequential read 等待事件事件
- 【TUNE_ORACLE】等待事件之IO等待“db file parallel write”Oracle事件Parallel
- 等待事件db file sequential read、db file scattered read和direct read的區別事件
- 【TUNE_ORACLE】等待事件之IO等待“db file sequential read”Oracle事件
- 【TUNE_ORACLE】等待事件之IO等待“db file scattered read”Oracle事件
- 非空閒的等待事件-db file scattered read事件
- oracle之 db file sequential read等待事件優化思想Oracle事件優化
- 非空閒等待事件之：db file scattered read(轉)事件
- 何時會發生db file sequential read等待事件？事件
- db file sequential read wait event等待事件之二AI事件
- 事件：db file scattered read事件
- control file parallel write等待事件Parallel事件
- 消除11.2上的db file parallel readParallel
- I/O上的等待事件 —— control file sequential read/control file parallel write事件Parallel
- oracle等待事件2構造一個DB File Sequential Read等待事件和構造一個Direct Path ReadOracle事件
- db file scattered read與事件db file sequential read相類似(轉)事件
- 【kingsql分享】何時出現生db file sequential read等待事件SQL事件
- 等待事件--db file scattered reads事件
- 等待事件--db file sequential reads事件
- 【TUNE_ORACLE】等待事件之日誌等待“log file parallel write”Oracle事件Parallel
- control file sequential read等待事件事件
- db file parallel writeParallel
- db file sequential read事件的發生事件
- Oracle之 等待事件log file sync + log file parallel write (awr優化)Oracle事件Parallel優化
- log file sync(日誌檔案同步) 與 Log file parallel write 等待事件Parallel事件
- 解決db file sequential read與db file scattered read
- oracle等待事件1分別用表和索引上資料的訪問來產生db file scattered read等待事件Oracle事件索引
- db file async I/O submit 等待事件優化MIT事件優化
- db file async I/O submit 等待事件說明MIT事件
- 找出導致db file scattered read等待事件發生的SQL及其執行計劃事件SQL