[20210315]理解db file parallel read等待事件3.txt
[20210315]理解db file parallel read等待事件3.txt
db file parallel read
This happens during recovery. It can also happen during buffer prefetching, as an optimization (rather than performing
multiple single-block reads). Database blocks that need to be changed as part of recovery are read in parallel from the
database.
Wait Time: Wait until all of the I/Os are completed
------------------------------------------------------------------------------------------------
Parameter Description
------------------------------------------------------------------------------------------------
files This indicates the number of files to which the session is reading
blocks This indicates the total number of blocks to be read
requests This indicates the total number of I/O requests, which will be the same as blocks
--//我看了生產系統,查詢不到select * from V$ACTIVE_SESSION_HISTORY where event='db file parallel read'.相關等待事件.
--//我在11g下僅僅11g動態取樣時看到這個等待事件。
--//最近在最佳化時發現索引範圍掃描時也發現這樣的等待事件，感覺情況很特殊，透過例子說明問題。
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
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 50;
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.
--//怎麼會出現這樣的情況有點不明白。tkprof處理後看到的情況:
********************************************************************************
SQL ID: 28mgrb1jdb7sh Plan Hash: 714684053
select /*+ index(t) */ max (substr(vc,1,2))
from
t where id between 1 and 200
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 202 202 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 202 202 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=202 pr=202 pw=0 time=3250 us)
200 200 200 TABLE ACCESS BY INDEX ROWID T (cr=202 pr=202 pw=0 time=49475 us cost=203 size=701000 card=200)
200 200 200 INDEX RANGE SCAN I_T_ID (cr=2 pr=2 pw=0 time=227 us cost=2 size=0 card=200)(object id 90882)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 7 0.00 0.00
db file parallel read 3 0.00 0.00
db file scattered read 2 0.00 0.00
SQL*Net message from client 2 3.27 3.27
********************************************************************************
3.使用strace跟蹤看看。
SCOTT@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
30 3905 52496 DEDICATED 52497 26 197 alter system kill session '30,3905' immediate;
alter system flush buffer_cache ;
select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 50;
--//範圍在id between 1 and 50;不然輸出太多了。
$ strace -f -p 52497 -e pread
Process 52497 attached - interrupt to quit
pread(257, "\6\242\0\0\253\r\0\1n\333{\27\3\0\1\4\21x\0\0\2\0\0\0\2c\1\0l\333{\27"..., 8192, 28663808) = 8192
pread(257, "\6\242\0\0\254\r\0\1n\333{\27\3\0\2\4\265\220\0\0\2\0\0\0\2c\1\0l\333{\27"..., 8192, 28672000) = 8192
pread(257, "\6\242\0\0,\2\0\0019\333{\27\3\0\2\4\361S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4554752) = 8192
pread(257, "\6\242\0\0-\2\0\0019\333{\27\3\0\2\4\367S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4562944) = 8192
pread(257, "\6\242\0\0.\2\0\0019\333{\27\3\0\2\4\365S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4571136) = 8192
pread(257, "\6\242\0\0/\2\0\0019\333{\27\3\0\2\4\367S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4579328) = 8192
pread(257, "\6\242\0\0000\2\0\0019\333{\27\3\0\2\4\351R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4587520) = 8192
pread(257, "\6\242\0\0001\2\0\0019\333{\27\3\0\2\4\347R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4595712) = 8192
pread(257, "\6\242\0\0002\2\0\0019\333{\27\3\0\2\4\345R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4603904) = 8192
pread(257, "\6\242\0\0003\2\0\0019\333{\27\3\0\2\4\347R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4612096) = 8192
pread(257, "\6\242\0\0004\2\0\0019\333{\27\3\0\2\4\341R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4620288) = 8192
pread(257, "\6\242\0\0005\2\0\0019\333{\27\3\0\2\4\347R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4628480) = 8192
pread(257, "\6\242\0\0006\2\0\0019\333{\27\3\0\2\4\345R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4636672) = 8192
pread(257, "\6\242\0\0007\2\0\0019\333{\27\3\0\2\4\347R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4644864) = 8192
pread(257, "\6\242\0\0\241\2\0\1:\333{\27\3\0\2\4\370S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5513216) = 8192
pread(257, "\6\242\0\0\242\2\0\1;\333{\27\3\0\1\4\344S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5521408) = 8192
pread(257, "\6\242\0\0\243\2\0\1;\333{\27\3\0\2\4\344S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5529600) = 8192
pread(257, "\6\242\0\0\244\2\0\1;\333{\27\3\0\2\4\340S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5537792) = 8192
pread(257, "\6\242\0\0\245\2\0\1;\333{\27\3\0\2\4\340S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5545984) = 8192
pread(257, "\6\242\0\0\246\2\0\1;\333{\27\3\0\2\4\344S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5554176) = 8192
pread(257, "\6\242\0\0\247\2\0\1;\333{\27\3\0\2\4\344S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5562368) = 8192
pread(257, "\6\242\0\0\260\2\0\1=\333{\27\3\0\2\4\360R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5636096) = 8192
pread(257, "\6\242\0\0\261\2\0\1=\333{\27\3\0\2\4\360R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5644288) = 8192
pread(257, "\6\242\0\0\262\2\0\1=\333{\27\3\0\2\4\374R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5652480) = 8192
pread(257, "\6\242\0\0\263\2\0\1=\333{\27\3\0\2\4\374R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5660672) = 8192
pread(257, "\6\242\0\0\264\2\0\1=\333{\27\3\0\2\4\370R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5668864) = 8192
pread(257, "\6\242\0\0\265\2\0\1=\333{\27\3\0\2\4\370R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5677056) = 8192
pread(257, "\6\242\0\0\266\2\0\1=\333{\27\3\0\2\4\374R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5685248) = 8192
pread(257, "\6\242\0\0\267\2\0\1=\333{\27\3\0\2\4\374R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5693440) = 8192
pread(257, "\6\242\0\0\271\2\0\1>\333{\27\3\0\2\4\351S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5709824) = 8192
pread(257, "\6\242\0\0\272\2\0\1?\333{\27\3\0\1\4\353S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5718016) = 8192
pread(257, "\6\242\0\0\273\2\0\1?\333{\27\3\0\2\4\325S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5726208) = 8192
pread(257, "\6\242\0\0\274\2\0\1?\333{\27\3\0\2\4\323S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5734400) = 8192
pread(257, "\6\242\0\0\275\2\0\1?\333{\27\3\0\2\4\321S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5742592) = 8192
pread(257, "\6\242\0\0\276\2\0\1?\333{\27\3\0\2\4\323S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5750784) = 8192
pread(257, "\6\242\0\0\277\2\0\1?\333{\27\3\0\2\4\325S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5758976) = 8192
pread(257, "\6\242\0\0\310\2\0\1@\333{\27\3\0\2\4\243R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5832704) = 8192
pread(257, "\6\242\0\0\311\2\0\1@\333{\27\3\0\2\4\241R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5840896) = 8192
pread(257, "\6\242\0\0\312\2\0\1A\333{\27\3\0\1\4\243R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5849088) = 8192
pread(257, "\6\242\0\0\313\2\0\1A\333{\27\3\0\2\4\255R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5857280) = 8192
pread(257, "\6\242\0\0\314\2\0\1A\333{\27\3\0\2\4\253R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5865472) = 8192
pread(257, "\6\242\0\0+\2\0\0019\333{\27\3\0\1\4\367S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 4546560) = 8192
pread(257, "\6\242\0\0\315\2\0\1A\333{\27\3\0\2\4\251R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5873664) = 8192
pread(257, "\6\242\0\0\316\2\0\1A\333{\27\3\0\2\4\253R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5881856) = 8192
pread(257, "\6\242\0\0\317\2\0\1A\333{\27\3\0\2\4\255R\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5890048) = 8192
pread(257, "\6\242\0\0\321\2\0\1E\333{\27\3\0\1\4\332S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5906432) = 8192
pread(257, "\6\242\0\0\322\2\0\1E\333{\27\3\0\2\4\332S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5914624) = 8192
pread(257, "\6\242\0\0\323\2\0\1E\333{\27\3\0\2\4\332S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5922816) = 8192
pread(257, "\6\242\0\0\324\2\0\1E\333{\27\3\0\2\4\302S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5931008) = 8192
pread(257, "\6\242\0\0\325\2\0\1E\333{\27\3\0\2\4\302S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5939200) = 8192
pread(257, "\6\242\0\0\326\2\0\1E\333{\27\3\0\2\4\302S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5947392) = 8192
pread(257, "\6\242\0\0\327\2\0\1E\333{\27\3\0\2\4\302S\0\0\1\0\0\0\1c\1\0002\333{\27"..., 8192, 5955584) = 8192
--//將以上結果儲存aaa.txt文字便於後面分析：
$ awk '{print $4}' aaa.txt | sed 's/)$//' | xargs -IQ bash -c "echo Q/8192| bc"
3499
3500
556
557
558
559
560
561
562
563
564
565
566
567
673
674
675
676
677
678
679
688
689
690
691
692
693
694
695
697
698
699
700
701
702
703
712
713
714
715
716
555
717
718
719
721
722
723
724
725
726
727
PARSING IN CURSOR #140046993841704 len=77 dep=0 uid=83 oct=3 lid=83 tim=1615771805364336 hv=3939756639 ad='7dd58f58' sqlid='5649aq7pd7ukz'
select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 50
END OF STMT
PARSE #140046993841704:c=0,e=402,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=714684053,tim=1615771805364295
EXEC #140046993841704:c=0,e=116,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=714684053,tim=1615771805365108
WAIT #140046993841704: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1615771805365476
WAIT #140046993841704: nam='db file sequential read' ela= 102 file#=4 block#=3499 blocks=1 obj#=90882 tim=1615771805365793
WAIT #140046993841704: nam='db file sequential read' ela= 89 file#=4 block#=3500 blocks=1 obj#=90882 tim=1615771805366051
WAIT #140046993841704: nam='db file parallel read' ela= 3524 files=1 blocks=39 requests=39 obj#=90881 tim=1615771805370004
WAIT #140046993841704: nam='db file sequential read' ela= 88 file#=4 block#=555 blocks=1 obj#=90881 tim=1615771805370370
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #140046993841704: nam='db file sequential read' ela= 88 file#=4 block#=717 blocks=1 obj#=90881 tim=1615771805370806
WAIT #140046993841704: nam='db file sequential read' ela= 84 file#=4 block#=718 blocks=1 obj#=90881 tim=1615771805371029
WAIT #140046993841704: nam='db file sequential read' ela= 82 file#=4 block#=719 blocks=1 obj#=90881 tim=1615771805371238
WAIT #140046993841704: nam='db file sequential read' ela= 83 file#=4 block#=721 blocks=1 obj#=90881 tim=1615771805371535
WAIT #140046993841704: nam='db file sequential read' ela= 82 file#=4 block#=722 blocks=1 obj#=90881 tim=1615771805371746
WAIT #140046993841704: nam='db file sequential read' ela= 85 file#=4 block#=723 blocks=1 obj#=90881 tim=1615771805371970
WAIT #140046993841704: nam='db file sequential read' ela= 83 file#=4 block#=724 blocks=1 obj#=90881 tim=1615771805372183
WAIT #140046993841704: nam='db file sequential read' ela= 84 file#=4 block#=725 blocks=1 obj#=90881 tim=1615771805372394
WAIT #140046993841704: nam='db file sequential read' ela= 84 file#=4 block#=726 blocks=1 obj#=90881 tim=1615771805372689
WAIT #140046993841704: nam='db file sequential read' ela= 89 file#=4 block#=727 blocks=1 obj#=90881 tim=1615771805372904
FETCH #140046993841704:c=1999,e=7423,p=52,cr=52,cu=0,mis=0,r=1,dep=0,og=1,plh=714684053,tim=1615771805373031
STAT #140046993841704 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=52 pr=52 pw=0 time=7404 us)'
STAT #140046993841704 id=2 cnt=50 pid=1 pos=1 obj=90881 op='TABLE ACCESS BY INDEX ROWID T (cr=52 pr=52 pw=0 time=5829 us cost=53 size=175250 card=50)'
STAT #140046993841704 id=3 cnt=50 pid=2 pos=1 obj=90882 op='INDEX RANGE SCAN I_T_ID (cr=2 pr=2 pw=0 time=572 us cost=2 size=0 card=50)'
WAIT #140046993841704: nam='SQL*Net message from client' ela= 330 driver id=1650815232 #bytes=1 p3=0 obj#=90881 tim=1615771805374112
FETCH #140046993841704:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=714684053,tim=1615771805374354
WAIT #140046993841704: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=90881 tim=1615771805374752
--//對比前面的塊號可以發現3499，3500是索引段(obj#=90882).
--//556 557 558 559 560 561 562 563 564 565 566 567 673 674 675 676 677 678 679 688 689 690 691 692 693 694 695 697 698 699 700 701 702 703 712 713 714 715 716
--//對應上面請求的39塊。注意看請求的塊並不是連續的,會出現跳躍比如567,673。
--//而block#=555 塊號反而在db file parallel read的後面，猜測視乎在這個版本db file parallel read'陣列是40，然後拆分39,1.
--//也就是在11g下INDEX RANGE SCAN時也有可能採用db file parallel read的方式。
--//表t是按照id排序匯入的，換一個方式亂序的情況呢？
4.重新建立表T。
--//drop table t purge ;
create table t pctfree 99 as select level id, rpad('abc', 3500, 'x') vc from dual connect by level <= 500 order by dbms_random.random;
create index i_t_id on t(id) ;
exec dbms_stats.gather_table_stats(null, 'T', cascade=>true);
SCOTT@book> @ 10046on 12
Session altered.
SCOTT@book> select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 50;
MAX
----
ab
SCOTT@book> @ 10046off
Session altered.
=====================
PARSING IN CURSOR #140047001700944 len=77 dep=0 uid=83 oct=3 lid=83 tim=1615772784775069 hv=3939756639 ad='7dd58f58' sqlid='5649aq7pd7ukz'
select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 50
END OF STMT
PARSE #140047001700944:c=1999,e=2224,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=714684053,tim=1615772784775017
EXEC #140047001700944:c=0,e=104,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=714684053,tim=1615772784775920
WAIT #140047001700944: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1615772784776347
WAIT #140047001700944: nam='db file sequential read' ela= 145 file#=4 block#=3499 blocks=1 obj#=90884 tim=1615772784777121
WAIT #140047001700944: nam='db file sequential read' ela= 150 file#=4 block#=3500 blocks=1 obj#=90884 tim=1615772784777534
WAIT #140047001700944: nam='db file parallel read' ela= 4412 files=1 blocks=39 requests=39 obj#=90883 tim=1615772784782365
WAIT #140047001700944: nam='db file sequential read' ela= 131 file#=4 block#=565 blocks=1 obj#=90883 tim=1615772784782808
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #140047001700944: nam='db file sequential read' ela= 130 file#=4 block#=1077 blocks=1 obj#=90883 tim=1615772784783215
WAIT #140047001700944: nam='db file sequential read' ela= 122 file#=4 block#=1089 blocks=1 obj#=90883 tim=1615772784783591
WAIT #140047001700944: nam='db file sequential read' ela= 129 file#=4 block#=1032 blocks=1 obj#=90883 tim=1615772784783873
WAIT #140047001700944: nam='db file sequential read' ela= 128 file#=4 block#=1054 blocks=1 obj#=90883 tim=1615772784784159
WAIT #140047001700944: nam='db file sequential read' ela= 124 file#=4 block#=3467 blocks=1 obj#=90883 tim=1615772784784533
WAIT #140047001700944: nam='db file sequential read' ela= 123 file#=4 block#=856 blocks=1 obj#=90883 tim=1615772784784807
WAIT #140047001700944: nam='db file sequential read' ela= 127 file#=4 block#=918 blocks=1 obj#=90883 tim=1615772784785098
WAIT #140047001700944: nam='db file sequential read' ela= 130 file#=4 block#=862 blocks=1 obj#=90883 tim=1615772784785478
WAIT #140047001700944: nam='db file sequential read' ela= 122 file#=4 block#=1017 blocks=1 obj#=90883 tim=1615772784785752
WAIT #140047001700944: nam='db file sequential read' ela= 125 file#=4 block#=1058 blocks=1 obj#=90883 tim=1615772784786036
FETCH #140047001700944:c=3999,e=9789,p=52,cr=52,cu=0,mis=0,r=1,dep=0,og=1,plh=714684053,tim=1615772784786285
STAT #140047001700944 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=52 pr=52 pw=0 time=9348 us)'
STAT #140047001700944 id=2 cnt=50 pid=1 pos=1 obj=90883 op='TABLE ACCESS BY INDEX ROWID T (cr=52 pr=52 pw=0 time=6809 us cost=53 size=175250 card=50)'
STAT #140047001700944 id=3 cnt=50 pid=2 pos=1 obj=90884 op='INDEX RANGE SCAN I_T_ID (cr=2 pr=2 pw=0 time=727 us cost=2 size=0 card=50)'
WAIT #140047001700944: nam='SQL*Net message from client' ela= 338 driver id=1650815232 #bytes=1 p3=0 obj#=90883 tim=1615772784787396
FETCH #140047001700944:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=714684053,tim=1615772784787773
WAIT #140047001700944: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=90883 tim=1615772784788102
*** 2021-03-15 09:46:35.875
WAIT #140047001700944: nam='SQL*Net message from client' ela= 11087232 driver id=1650815232 #bytes=1 p3=0 obj#=90883 tim=1615772795875560
CLOSE #140047001700944:c=0,e=59,dep=0,type=0,tim=1615772795876434
=====================
--//同樣出現db file parallel read.
$ strace -f -p 52497 -e pread
Process 52497 attached - interrupt to quit
pread(257, "\6\242\0\0\253\r\0\1\345\347{\27\3\0\1\4\351\30\0\0\2\0\0\0\4c\1\0\343\347{\27"..., 8192, 28663808) = 8192
pread(257, "\6\242\0\0\254\r\0\1\345\347{\27\3\0\2\4\274E\0\0\2\0\0\0\4c\1\0\343\347{\27"..., 8192, 28672000) = 8192
pread(257, "\6\242\0\0006\2\0\1\254\347{\27\3\0\2\4\341R\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 4636672) = 8192
pread(257, "\6\242\0\0\274\2\0\1\262\347{\27\3\0\2\4\330S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 5734400) = 8192
pread(257, "\6\242\0\0\277\2\0\1\262\347{\27\3\0\2\4\367S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 5758976) = 8192
pread(257, "\6\242\0\0\311\2\0\1\264\347{\27\3\0\2\4\231R\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 5840896) = 8192
pread(257, "\6\242\0\0\330\2\0\1\270\347{\27\3\0\2\4\330R\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 5963776) = 8192
pread(257, "\6\242\0\0\355\2\0\1\274\347{\27\3\0\2\4\357R\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6135808) = 8192
pread(257, "\6\242\0\0\365\2\0\1\300\347{\27\3\0\2\4\330S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6201344) = 8192
pread(257, "\6\242\0\0\f\3\0\1\310\347{\27\3\0\2\4\364S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6389760) = 8192
pread(257, "\6\242\0\0\21\3\0\1\310\347{\27\3\0\2\4\346S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6430720) = 8192
pread(257, "\6\242\0\0\31\3\0\1\310\347{\27\3\0\2\4\344S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6496256) = 8192
pread(257, "\6\242\0\0.\3\0\1\310\347{\27\3\0\2\4\372S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6668288) = 8192
pread(257, "\6\242\0\0002\3\0\1\310\347{\27\3\0\2\4\326S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6701056) = 8192
pread(257, "\6\242\0\0009\3\0\1\310\347{\27\3\0\2\4\305S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6758400) = 8192
pread(257, "\6\242\0\0H\3\0\1\310\347{\27\3\0\2\4\273S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6881280) = 8192
pread(257, "\6\242\0\0K\3\0\1\310\347{\27\3\0\2\4\247S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 6905856) = 8192
pread(257, "\6\242\0\0Z\3\0\1\310\347{\27\3\0\2\4\252S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7028736) = 8192
pread(257, "\6\242\0\0`\3\0\1\312\347{\27\3\0\2\4\222S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7077888) = 8192
pread(257, "\6\242\0\0p\3\0\1\312\347{\27\3\0\2\4\242S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7208960) = 8192
pread(257, "\6\242\0\0x\3\0\1\312\347{\27\3\0\2\4\227S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7274496) = 8192
pread(257, "\6\242\0\0\242\3\0\1\312\347{\27\3\0\2\4\367S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7618560) = 8192
pread(257, "\6\242\0\0\265\3\0\1\312\347{\27\3\0\2\4\324S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7774208) = 8192
pread(257, "\6\242\0\0\276\3\0\1\312\347{\27\3\0\2\4\307S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7847936) = 8192
pread(257, "\6\242\0\0\312\3\0\1\312\347{\27\3\0\2\4\242S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7946240) = 8192
pread(257, "\6\242\0\0\313\3\0\1\312\347{\27\3\0\2\4\255S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7954432) = 8192
pread(257, "\6\242\0\0\340\3\0\1\315\347{\27\3\0\2\4\202S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8126464) = 8192
pread(257, "\6\242\0\0\366\3\0\1\315\347{\27\3\0\2\4\240S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8306688) = 8192
pread(257, "\6\242\0\0\24\4\0\1\315\347{\27\3\0\2\4\305S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8552448) = 8192
pread(257, "\6\242\0\0\25\4\0\1\315\347{\27\3\0\2\4\357S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8560640) = 8192
pread(257, "\6\242\0\0'\4\0\1\315\347{\27\3\0\2\4\302S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8708096) = 8192
pread(257, "\6\242\0\0007\4\0\1\315\347{\27\3\0\2\4\335S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8839168) = 8192
pread(257, "\6\242\0\0j\4\0\1\320\347{\27\3\0\2\4\220S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 9256960) = 8192
pread(257, "\6\242\0\0k\4\0\1\320\347{\27\3\0\2\4\214S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 9265152) = 8192
pread(257, "\6\242\0\0w\4\0\1\320\347{\27\3\0\2\4\232S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 9363456) = 8192
pread(257, "\6\242\0\0|\4\0\1\320\347{\27\3\0\2\4\203S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 9404416) = 8192
pread(257, "\6\242\0\0\215\r\0\1\304\347{\27\3\0\2\4\356S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 28418048) = 8192
pread(257, "\6\242\0\0\216\r\0\1\304\347{\27\3\0\2\4\324S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 28426240) = 8192
pread(257, "\6\242\0\0\217\r\0\1\304\347{\27\3\0\2\4\350S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 28434432) = 8192
pread(257, "\6\242\0\0\227\r\0\1\304\347{\27\3\0\2\4\377R\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 28499968) = 8192
pread(257, "\6\242\0\0\243\r\0\1\310\347{\27\3\0\2\4\355R\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 28598272) = 8192
pread(257, "\6\242\0\0005\2\0\1\254\347{\27\3\0\2\4\353R\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 4628480) = 8192
pread(257, "\6\242\0\0005\4\0\1\315\347{\27\3\0\2\4\353S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8822784) = 8192
pread(257, "\6\242\0\0A\4\0\1\315\347{\27\3\0\2\4\237S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8921088) = 8192
pread(257, "\6\242\0\0\10\4\0\1\315\347{\27\3\0\2\4\320S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8454144) = 8192
pread(257, "\6\242\0\0\36\4\0\1\315\347{\27\3\0\2\4\307S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8634368) = 8192
pread(257, "\6\242\0\0\213\r\0\1\304\347{\27\3\0\2\4\331S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 28401664) = 8192
pread(257, "\6\242\0\0X\3\0\1\310\347{\27\3\0\2\4\202S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7012352) = 8192
pread(257, "\6\242\0\0\226\3\0\1\312\347{\27\3\0\2\4\322S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7520256) = 8192
pread(257, "\6\242\0\0^\3\0\1\310\347{\27\3\0\2\4\232S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 7061504) = 8192
pread(257, "\6\242\0\0\371\3\0\1\315\347{\27\3\0\2\4\276S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8331264) = 8192
pread(257, "\6\242\0\0\"\4\0\1\315\347{\27\3\0\2\4\345S\0\0\1\0\0\0\3c\1\0\245\347{\27"..., 8192, 8667136) = 8192
"}
--//將以上結果儲存bbb.txt文字便於後面分析：
$ awk '{print $4}' bbb.txt | sed 's/)$//' | xargs -IQ bash -c "echo Q/8192| bc"
3499
3500
566
700
703
713
728
749
757
780
785
793
814
818
825
840
843
858
864
880
888
930
949
958
970
971
992
1014
1044
1045
1063
1079
1130
1131
1143
1148
3469
3470
3471
3479
3491
565
1077
1089
1032
1054
3467
856
918
862
1017
1058
--//對比前面的塊號可以發現3499，3500是索引段(obj#=90884).
--//566 700 703 713 728 749 757 780 785 793 814 818 825 840 843 858 864 880 888 930 949 958 970 971 992 1014 1044 1045 1063 1079 1130 1131 1143 1148 3469 3470 3471 3479 3491
--//對應上面請求的39塊。注意看請求的塊並不是連續的.但是大小是遞增的。
--//而block#=565 塊號反而在db file parallel read的後面，猜測視乎在這個版本db file parallel read陣列是40.
--//也就是在11g下INDEX RANGE SCAN時也有可能採用db file parallel read的方式。
--//這樣的好處大家也可以看出來就是按照塊排序，"遞增連續跳躍"地掃描對應的塊，這樣讀取模式比起1塊1塊讀更加塊。
--//至少可能磁碟尋道的時間會減少。
SCOTT@book> select id from t where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) in (565);
ID
----------
1
--//可以猜測其它塊對應的ID是從2-40.
select id from t where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)
in (566,700,703,713,728,749,757,780,785,793,814,818,825,840,843,
858,864,880,888,930,949,958,970,971,992,1014,1044,1045,
1063,1079,1130,1131,1143,1148,3469,3470,3471,3479,3491)
minus
select level id from dual connect by level<=40;
no rows selected
select id from t where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)
in (566,700,703,713,728,749,757,780,785,793,814,818,825,840,843,
858,864,880,888,930,949,958,970,971,992,1014,1044,1045,
1063,1079,1130,1131,1143,1148,3469,3470,3471,3479,3491);
ID
----------
10
39
3
27
35
21
40
30
37
26
19
33
11
2
8
31
15
7
5
24
4
6
38
25
32
20
12
28
18
22
34
36
13
16
29
14
17
23
9
39 rows selected.
--//視乎在11g下出現db file parallel read的情況很特殊，如果執行：
SCOTT@book> alter system flush buffer_cache ;
System altered.
SCOTT@book> select /*+ index(t) */ DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) ,id ,substr(vc,1,2) from t where id between 1 and 50;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID SUBS
------------------------------------ ---------- ----
565 1 ab
785 2 ab
703 3 ab
858 4 ab
840 5 ab
864 6 ab
825 7 ab
793 8 ab
1148 9 ab
566 10 ab
780 11 ab
958 12 ab
1045 13 ab
1130 14 ab
818 15 ab
1063 16 ab
1131 17 ab
971 18 ab
3479 19 ab
949 20 ab
749 21 ab
992 22 ab
1143 23 ab
843 24 ab
888 25 ab
3471 26 ab
713 27 ab
970 28 ab
1079 29 ab
3469 30 ab
814 31 ab
930 32 ab
3491 33 ab
1014 34 ab
728 35 ab
1044 36 ab
3470 37 ab
880 38 ab
700 39 ab
757 40 ab
1077 41 ab
1089 42 ab
1032 43 ab
1054 44 ab
3467 45 ab
856 46 ab
918 47 ab
862 48 ab
1017 49 ab
1058 50 ab
50 rows selected.
=====================
PARSING IN CURSOR #140493966748128 len=114 dep=0 uid=83 oct=3 lid=83 tim=1615775079798416 hv=2272960823 ad='7be3c290' sqlid='22q5dak3rp89r'
select /*+ index(t) */ DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) ,id ,substr(vc,1,2) from t where id between 1 and 50
END OF STMT
PARSE #140493966748128:c=4999,e=5138,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=4153437776,tim=1615775079798410
EXEC #140493966748128:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4153437776,tim=1615775079798568
WAIT #140493966748128: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1615775079798643
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=3499 blocks=1 obj#=90884 tim=1615775079798730
WAIT #140493966748128: nam='db file sequential read' ela= 12 file#=4 block#=3500 blocks=1 obj#=90884 tim=1615775079798792
WAIT #140493966748128: nam='db file sequential read' ela= 12 file#=4 block#=565 blocks=1 obj#=90883 tim=1615775079798856
FETCH #140493966748128:c=1000,e=477,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=4153437776,tim=1615775079799163
WAIT #140493966748128: nam='SQL*Net message from client' ela= 421 driver id=1650815232 #bytes=1 p3=0 obj#=90883 tim=1615775079799659
WAIT #140493966748128: nam='db file sequential read' ela= 14 file#=4 block#=785 blocks=1 obj#=90883 tim=1615775079799796
WAIT #140493966748128: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=90883 tim=1615775079800006
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=703 blocks=1 obj#=90883 tim=1615775079800088
WAIT #140493966748128: nam='db file sequential read' ela= 14 file#=4 block#=858 blocks=1 obj#=90883 tim=1615775079800190
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=840 blocks=1 obj#=90883 tim=1615775079800277
WAIT #140493966748128: nam='db file sequential read' ela= 14 file#=4 block#=864 blocks=1 obj#=90883 tim=1615775079800366
WAIT #140493966748128: nam='db file sequential read' ela= 12 file#=4 block#=825 blocks=1 obj#=90883 tim=1615775079800449
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=793 blocks=1 obj#=90883 tim=1615775079800538
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=1148 blocks=1 obj#=90883 tim=1615775079800615
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=566 blocks=1 obj#=90883 tim=1615775079800696
WAIT #140493966748128: nam='db file sequential read' ela= 14 file#=4 block#=780 blocks=1 obj#=90883 tim=1615775079800785
WAIT #140493966748128: nam='db file sequential read' ela= 22 file#=4 block#=958 blocks=1 obj#=90883 tim=1615775079800877
WAIT #140493966748128: nam='db file sequential read' ela= 14 file#=4 block#=1045 blocks=1 obj#=90883 tim=1615775079800963
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=1130 blocks=1 obj#=90883 tim=1615775079801050
WAIT #140493966748128: nam='db file sequential read' ela= 14 file#=4 block#=818 blocks=1 obj#=90883 tim=1615775079801170
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=1063 blocks=1 obj#=90883 tim=1615775079801260
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=1131 blocks=1 obj#=90883 tim=1615775079801339
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=971 blocks=1 obj#=90883 tim=1615775079801418
WAIT #140493966748128: nam='db file sequential read' ela= 14 file#=4 block#=3479 blocks=1 obj#=90883 tim=1615775079801501
WAIT #140493966748128: nam='db file sequential read' ela= 15 file#=4 block#=949 blocks=1 obj#=90883 tim=1615775079801586
WAIT #140493966748128: nam='db file sequential read' ela= 14 file#=4 block#=749 blocks=1 obj#=90883 tim=1615775079801671
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=992 blocks=1 obj#=90883 tim=1615775079801759
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=1143 blocks=1 obj#=90883 tim=1615775079801837
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=843 blocks=1 obj#=90883 tim=1615775079801932
WAIT #140493966748128: nam='db file sequential read' ela= 9 file#=4 block#=888 blocks=1 obj#=90883 tim=1615775079802012
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=3471 blocks=1 obj#=90883 tim=1615775079802099
WAIT #140493966748128: nam='db file sequential read' ela= 13 file#=4 block#=713 blocks=1 obj#=90883 tim=1615775079802188
WAIT #140493966748128: nam='db file sequential read' ela= 13 file#=4 block#=970 blocks=1 obj#=90883 tim=1615775079802278
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=1079 blocks=1 obj#=90883 tim=1615775079802355
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=3469 blocks=1 obj#=90883 tim=1615775079802433
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=814 blocks=1 obj#=90883 tim=1615775079802518
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=930 blocks=1 obj#=90883 tim=1615775079802595
WAIT #140493966748128: nam='db file sequential read' ela= 12 file#=4 block#=3491 blocks=1 obj#=90883 tim=1615775079802674
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=1014 blocks=1 obj#=90883 tim=1615775079802755
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=728 blocks=1 obj#=90883 tim=1615775079802832
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=1044 blocks=1 obj#=90883 tim=1615775079802929
WAIT #140493966748128: nam='db file sequential read' ela= 9 file#=4 block#=3470 blocks=1 obj#=90883 tim=1615775079803005
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=880 blocks=1 obj#=90883 tim=1615775079803089
WAIT #140493966748128: nam='db file sequential read' ela= 13 file#=4 block#=700 blocks=1 obj#=90883 tim=1615775079803177
WAIT #140493966748128: nam='db file sequential read' ela= 15 file#=4 block#=757 blocks=1 obj#=90883 tim=1615775079803269
WAIT #140493966748128: nam='db file sequential read' ela= 9 file#=4 block#=1077 blocks=1 obj#=90883 tim=1615775079803353
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=1089 blocks=1 obj#=90883 tim=1615775079803430
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=1032 blocks=1 obj#=90883 tim=1615775079803510
WAIT #140493966748128: nam='db file sequential read' ela= 12 file#=4 block#=1054 blocks=1 obj#=90883 tim=1615775079803588
WAIT #140493966748128: nam='db file sequential read' ela= 13 file#=4 block#=3467 blocks=1 obj#=90883 tim=1615775079803669
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=856 blocks=1 obj#=90883 tim=1615775079803757
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=918 blocks=1 obj#=90883 tim=1615775079803840
WAIT #140493966748128: nam='db file sequential read' ela= 9 file#=4 block#=862 blocks=1 obj#=90883 tim=1615775079803948
WAIT #140493966748128: nam='db file sequential read' ela= 10 file#=4 block#=1017 blocks=1 obj#=90883 tim=1615775079804050
WAIT #140493966748128: nam='db file sequential read' ela= 11 file#=4 block#=1058 blocks=1 obj#=90883 tim=1615775079804136
FETCH #140493966748128:c=3999,e=4471,p=49,cr=50,cu=0,mis=0,r=49,dep=0,og=1,plh=4153437776,tim=1615775079804192
STAT #140493966748128 id=1 cnt=50 pid=0 pos=1 obj=90883 op='TABLE ACCESS BY INDEX ROWID T (cr=53 pr=52 pw=0 time=225 us cost=53 size=175250 card=50)'
STAT #140493966748128 id=2 cnt=50 pid=1 pos=1 obj=90884 op='INDEX RANGE SCAN I_T_ID (cr=3 pr=2 pw=0 time=132 us cost=2 size=0 card=50)'
*** 2021-03-15 10:24:44.321
WAIT #140493966748128: nam='SQL*Net message from client' ela= 4516853 driver id=1650815232 #bytes=1 p3=0 obj#=90883 tim=1615775084321242
CLOSE #140493966748128:c=0,e=11,dep=0,type=0,tim=1615775084321341
=====================
--//就不再出現db file parallel read等待事件。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2762820/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210315]理解db file parallel read等待事件4.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優化
- 解決db file sequential read與db file scattered read
- log file sync(日誌檔案同步) 與 Log file parallel write 等待事件Parallel事件
- 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