[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等待事件。
[20210315]理解db file parallel read等待事件3.txt
来源：这里教程网
时间：2026-03-03 16:31:29
作者：
编辑推荐：
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
![[oracle] 索引低效，导致read by other session等待事件](https://www.herecours.com/d/file/efpub/2026/03-03/20260303151531841710.jpg "[oracle] 索引低效，导致read by other session等待事件")
热文推荐
- read by other session 等待事件分析
read by other session 等待事件分析
26-03-03 - Oracle sqlldr工具功能测试
Oracle sqlldr工具功能测试
26-03-03 - SQLServer 2012复制订阅数据订阅过程
SQLServer 2012复制订阅数据订阅过程
26-03-03 - [oracle] 索引低效，导致read by other session等待事件
- 【SQL】SQL表连接方法方式介绍（Oracle/Postgresql）
【SQL】SQL表连接方法方式介绍（Oracle/Postgresql）
26-03-03 - Oracle 19c数据库体系结构-2
Oracle 19c数据库体系结构-2
26-03-03 - 数据库无法注册至监听服务解决办法
数据库无法注册至监听服务解决办法
26-03-03 - Oracle数据库启动过程及状态详解
Oracle数据库启动过程及状态详解
26-03-03 - 数据库常用的事务隔离级别都有哪些？都是什么原理？
数据库常用的事务隔离级别都有哪些？都是什么原理？
26-03-03 - 检查联机后的43 号文件信息：alter database datafile 43 online;
