[20260310]理解db file parallel read等待事件与异步IO.txt
--//以前的测试在filesystemio_options=none的情况，今天补充测试filesystemio_options=asynch的情况。
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
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试环境：
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);
3.测试：
SCOTT@book01p> alter system flush buffer_cache ;
System altered.
SCOTT@book01p> @10046on 12
Session altered.
SCOTT@book01p> select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 80;
MAX
----
AB
SCOTT@book01p> @ 10046off
Session altered.
--//跟踪文件内容如下：
=====================
PARSING IN CURSOR #140166383605984 len=77 dep=0 uid=109 oct=3 lid=109 tim=5675391060 hv=3755752580 ad='72e02f18' sqlid='dbg2xxrgxsg44'
select /*+ index(t) */ max (substr(vc,1,2)) from t where id between 1 and 80
END OF STMT
PARSE #140166383605984:c=5738,e=6208,p=8,cr=3,cu=8,mis=1,r=0,dep=0,og=1,plh=1996139868,tim=5675391059
EXEC #140166383605984:c=42,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1996139868,tim=5675391175
WAIT #140166383605984: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=198148 tim=5675391221
WAIT #140166383605984: nam='Disk file operations I/O' ela= 19 FileOperation=2 fileno=12 filetype=2 obj#=198650 tim=5675391293
WAIT #140166383605984: nam='db file sequential read' ela= 9 file#=12 block#=299 blocks=1 obj#=198650 tim=5675391324
WAIT #140166383605984: nam='db file sequential read' ela= 8 file#=12 block#=300 blocks=1 obj#=198650 tim=5675391362
WAIT #140166383605984: nam='db file sequential read' ela= 8 file#=12 block#=617 blocks=1 obj#=198649 tim=5675391435
WAIT #140166383605984: nam='db file sequential read' ela= 8 file#=12 block#=764 blocks=1 obj#=198649 tim=5675391495
WAIT #140166383605984: nam='PGA memory operation' ela= 14 p1=1114112 p2=1 p3=0 obj#=198649 tim=5675391551
WAIT #140166383605984: nam='db file parallel read' ela= 359 files=1 blocks=78 requests=78 obj#=198649 tim=5675392282
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #140166383605984:c=981,e=1338,p=82,cr=82,cu=0,mis=0,r=1,dep=0,og=1,plh=1996139868,tim=5675392586
STAT #140166383605984 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=82 pr=82 pw=0 str=1 time=1350 us)'
STAT #140166383605984 id=2 cnt=80 pid=1 pos=1 obj=198649 op='TABLE ACCESS BY INDEX ROWID BATCHED T (cr=82 pr=82 pw=0 str=1 time=3370 us cost=83 size=280400 card=80)'
STAT #140166383605984 id=3 cnt=80 pid=2 pos=1 obj=198650 op='INDEX RANGE SCAN I_T_ID (cr=2 pr=2 pw=0 str=1 time=238 us cost=2 size=0 card=80)'
WAIT #140166383605984: nam='SQL*Net message from client' ela= 725 driver id=1413697536 #bytes=1 p3=0 obj#=198649 tim=5675393473
FETCH #140166383605984:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1996139868,tim=5675393516
WAIT #140166383605984: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=198649 tim=5675393548
*** 2026-03-10T16:42:46.937083+08:00 (BOOK01P(3))
WAIT #140166383605984: nam='SQL*Net message from client' ela= 6519577 driver id=1413697536 #bytes=1 p3=0 obj#=198649 tim=5681913141
CLOSE #140166383605984:c=0,e=16,dep=0,type=0,tim=5681913341
WAIT #0: nam='PGA memory operation' ela= 21 p1=0 p2=0 p3=0 obj#=198649 tim=5681913421
=====================
--//前面4个单块读，前2个对应索引段(obj#=198650)，然后单独2次读块dba=12,617和12,764.
--//db file parallel read等待事件仅仅看到读取块的数量，请求的看不见读取的那些块。
--//先看看dba=12,617和12,764对应那些记录。
SCOTT@book01p> @ cr_rowid 198649 12 617 0
argument list : data_object_id file# block# row#
CREATE_ROWID
------------------
AAAwf5AAMAAAAJpAAA
SCOTT@book01p> select t.id,substr(vc,1,2) from t where rowid between 'AAAwf5AAMAAAAJpAAA' and 'AAAwf5AAMAAAAJpBBB';
ID SUBS
---------- ----
1 AB
--//建立的表pctfree=99，这样每块仅仅1条记录。
SCOTT@book01p> @ cr_rowid 198649 12 764 0
argument list : data_object_id file# block# row#
CREATE_ROWID
------------------
AAAwf5AAMAAAAL8AAA
SCOTT@book01p> select t.id,substr(vc,1,2) from t where rowid between 'AAAwf5AAMAAAAL8AAA' and 'AAAwf5AAMAAAAL8BBB';
ID SUBS
---------- ----
2 AB
--//可以发现开始2个单块读对应id=1，2. 剩下自然id=3到80的记录。
--//看看strace的跟踪,注意测试前刷新数据缓存。
--//alter system flush buffer_cache ;
$ strace -f -p 5156 -e pread,io_submit -a 80 -y
Process 5156 attached
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0+\1\0\3\251\211L\4\0\0\1\4\227\36\0\0\2\0\0\0\372\7\3\0\246\211L\4"..., 8192, 2449408) = 8192
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0,\1\0\3\250\211L\4\0\0\2\4\374}\0\0\2\0\0\0\372\7\3\0\246\211L\4"..., 8192, 2457600) = 8192
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0i\2\0\3R\211L\4\0\0\2\4R$\0\0\1\0\0\0\371\7\3\0%\211L\4"..., 8192, 5054464) = 8192
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\374\2\0\3U\211L\4\0\0\2\4F$\0\0\1\0\0\0\371\7\3\0%\211L\4"..., 8192, 6258688) = 8192
io_submit(140166482505728, 78, {{data:0x7f7b07731be0, pread, filedes:260, buf:0x95ede000, nbytes:8192, offset:1417216}, {data:0x7f7b07731ea0, pread, filedes:260, buf:0x884c6000, nbytes:8192, offset:1441792}, {data:0x7f7b07732160, pread, filedes:260, buf:0x9c0fe000, nbytes:8192, offset:1449984}, {data:0x7f7b07732420, pread, filedes:260, buf:0x9c720000, nbytes:8192, offset:1564672}, {data:0x7f7b077326e0, pread, filedes:260, buf:0x9bbf4000, nbytes:8192, offset:1622016}, {data:0x7f7b077329a0, pread, filedes:260, buf:0x88728000, nbytes:8192, offset:1646592}, {data:0x7f7b07732c60, pread, filedes:260, buf:0x8892e000, nbytes:8192, offset:1703936}, {data:0x7f7b07732f20, pread, filedes:260, buf:0x9793e000, nbytes:8192, offset:1802240}, {data:0x7f7b077331e0, pread, filedes:260, buf:0x92a0e000, nbytes:8192, offset:1818624}, {data:0x7f7b077334a0, pread, filedes:260, buf:0x8852e000, nbytes:8192, offset:1835008}, {data:0x7f7b07733760, pread, filedes:260, buf:0x967b2000, nbytes:8192, offset:1859584}, {data:0x7f7b07733a20, pread, filedes:260, buf:0x978f8000, nbytes:8192, offset:1892352}, {data:0x7f7b07733ce0, pread, filedes:260, buf:0x99578000, nbytes:8192, offset:2080768}, {data:0x7f7b07733fa0, pread, filedes:260, buf:0x88460000, nbytes:8192, offset:2195456}, {data:0x7f7b07734260, pread, filedes:260, buf:0x88932000, nbytes:8192, offset:2211840}, {data:0x7f7b07734520, pread, filedes:260, buf:0x9c708000, nbytes:8192, offset:2236416}, {data:0x7f7b077347e0, pread, filedes:260, buf:0x8811e000, nbytes:8192, offset:2310144}, {data:0x7f7b07734aa0, pread, filedes:260, buf:0x88518000, nbytes:8192, offset:2383872}, {data:0x7f7b07734d60, pread, filedes:260, buf:0x96aac000, nbytes:8192, offset:2392064}, {data:0x7f7b07735020, pread, filedes:260, buf:0x88e44000, nbytes:8192, offset:2408448}, {data:0x7f7b077352e0, pread, filedes:260, buf:0x95ebc000, nbytes:8192, offset:3252224}, {data:0x7f7b077355a0, pread, filedes:260, buf:0x8869c000, nbytes:8192, offset:3309568}, {data:0x7f7b07735860, pread, filedes:260, buf:0x888da000, nbytes:8192, offset:3391488}, {data:0x7f7b07735b20, pread, filedes:260, buf:0x88cd2000, nbytes:8192, offset:3416064}, {data:0x7f7b07735de0, pread, filedes:260, buf:0x97cc0000, nbytes:8192, offset:3440640}, {data:0x7f7b077360a0, pread, filedes:260, buf:0x9ce4c000, nbytes:8192, offset:3448832}, {data:0x7f7b07736360, pread, filedes:260, buf:0x88a1c000, nbytes:8192, offset:3571712}, {data:0x7f7b07736620, pread, filedes:260, buf:0x9c6ee000, nbytes:8192, offset:3661824}, {data:0x7f7b077368e0, pread, filedes:260, buf:0x9bc50000, nbytes:8192, offset:3760128}, {data:0x7f7b07736ba0, pread, filedes:260, buf:0x884b8000, nbytes:8192, offset:3850240}, {data:0x7f7b07736e60, pread, filedes:260, buf:0x974f4000, nbytes:8192, offset:3932160}, {data:0x7f7b07737120, pread, filedes:260, buf:0x88c3a000, nbytes:8192, offset:4014080}, {data:0x7f7b077373e0, pread, filedes:260, buf:0x882e6000, nbytes:8192, offset:4038656}, {data:0x7f7b077376a0, pread, filedes:260, buf:0x9ccee000, nbytes:8192, offset:4128768}, {data:0x7f7b07737960, pread, filedes:260, buf:0x88b62000, nbytes:8192, offset:4145152}, {data:0x7f7b07737c20, pread, filedes:260, buf:0x88cf0000, nbytes:8192, offset:4235264}, {data:0x7f7b07737ee0, pread, filedes:260, buf:0x8837e000, nbytes:8192, offset:4251648}, {data:0x7f7b077381a0, pread, filedes:260, buf:0x8869a000, nbytes:8192, offset:4284416}, {data:0x7f7b07738460, pread, filedes:260, buf:0x9c380000, nbytes:8192, offset:4300800}, {data:0x7f7b07738720, pread, filedes:260, buf:0x88edc000, nbytes:8192, offset:4374528}, {data:0x7f7b077389e0, pread, filedes:260, buf:0x97caa000, nbytes:8192, offset:4382720}, {data:0x7f7b07738ca0, pread, filedes:260, buf:0x885fe000, nbytes:8192, offset:4415488}, {data:0x7f7b07738f60, pread, filedes:260, buf:0x88ae4000, nbytes:8192, offset:4440064}, {data:0x7f7b07739220, pread, filedes:260, buf:0x88c9a000, nbytes:8192, offset:4489216}, {data:0x7f7b077394e0, pread, filedes:260, buf:0x8827a000, nbytes:8192, offset:4612096}, {data:0x7f7b077397a0, pread, filedes:260, buf:0x9b04a000, nbytes:8192, offset:4636672}, {data:0x7f7b07739a60, pread, filedes:260, buf:0x975c0000, nbytes:8192, offset:4653056}, {data:0x7f7b07739d20, pread, filedes:260, buf:0x9c442000, nbytes:8192, offset:4694016}, {data:0x7f7b07739fe0, pread, filedes:260, buf:0x9cb72000, nbytes:8192, offset:4743168}, {data:0x7f7b0773a2a0, pread, filedes:260, buf:0x8845e000, nbytes:8192, offset:4841472}, {data:0x7f7b0773a560, pread, filedes:260, buf:0x88998000, nbytes:8192, offset:4866048}, {data:0x7f7b0773a820, pread, filedes:260, buf:0x88c58000, nbytes:8192, offset:4898816}, {data:0x7f7b0773aae0, pread, filedes:260, buf:0x95ecc000, nbytes:8192, offset:4907008}, {data:0x7f7b0773ada0, pread, filedes:260, buf:0x96414000, nbytes:8192, offset:5005312}, {data:0x7f7b0773b060, pread, filedes:260, buf:0x9c292000, nbytes:8192, offset:5021696}, {data:0x7f7b0773b320, pread, filedes:260, buf:0x88ec0000, nbytes:8192, offset:5111808}, {data:0x7f7b0773b5e0, pread, filedes:260, buf:0x97d2a000, nbytes:8192, offset:5136384}, {data:0x7f7b0773b8a0, pread, filedes:260, buf:0x9677c000, nbytes:8192, offset:5144576}, {data:0x7f7b0773bb60, pread, filedes:260, buf:0x9c166000, nbytes:8192, offset:5185536}, {data:0x7f7b0773be20, pread, filedes:260, buf:0x978cc000, nbytes:8192, offset:5201920}, {data:0x7f7b0773c0e0, pread, filedes:260, buf:0x9cb38000, nbytes:8192, offset:5373952}, {data:0x7f7b0773c3a0, pread, filedes:260, buf:0x971b4000, nbytes:8192, offset:5431296}, {data:0x7f7b0773c660, pread, filedes:260, buf:0x88906000, nbytes:8192, offset:5455872}, {data:0x7f7b0773c920, pread, filedes:260, buf:0x88d3e000, nbytes:8192, offset:5480448}, {data:0x7f7b0773cbe0, pread, filedes:260, buf:0x88234000, nbytes:8192, offset:5521408}, {data:0x7f7b0773cea0, pread, filedes:260, buf:0x970be000, nbytes:8192, offset:5578752}, {data:0x7f7b0773d160, pread, filedes:260, buf:0x97546000, nbytes:8192, offset:5685248}, {data:0x7f7b0773d420, pread, filedes:260, buf:0x9c7a8000, nbytes:8192, offset:5824512}, {data:0x7f7b0773d6e0, pread, filedes:260, buf:0x9c904000, nbytes:8192, offset:5873664}, {data:0x7f7b0773d9a0, pread, filedes:260, buf:0x8851e000, nbytes:8192, offset:5947392}, {data:0x7f7b0773dc60, pread, filedes:260, buf:0x9740a000, nbytes:8192, offset:6012928}, {data:0x7f7b0773df20, pread, filedes:260, buf:0x88eca000, nbytes:8192, offset:6078464}, {data:0x7f7b0773e1e0, pread, filedes:260, buf:0x881da000, nbytes:8192, offset:6103040}, {data:0x7f7b0773e4a0, pread, filedes:260, buf:0x885b4000, nbytes:8192, offset:6119424}, {data:0x7f7b0773e760, pread, filedes:260, buf:0x88b68000, nbytes:8192, offset:6135808}, {data:0x7f7b0773ea20, pread, filedes:260, buf:0x9b890000, nbytes:8192, offset:6217728}, {data:0x7f7b0773ece0, pread, filedes:260, buf:0x8814a000, nbytes:8192, offset:6225920}, {data:0x7f7b0773efa0, pread, filedes:260, buf:0x9d7d6000, nbytes:8192, offset:6283264}}) = 78
--//前面4个单块读，不再说明。
--//io_submit输出很长，做一些格式话处理，单独保存文件为io_submit.txt便于重复分析，最后等于78。
--//注：如果加大查询范围，最大可以到127.估计与db_file_multiblock_read_count=128相关。
--// man io_submit
...
RETURN VALUE
On success, io_submit() returns the number of iocbs submitted (which may be 0 if nr is zero). For the failure return,
see NOTES.
--//vim很好操作，:.s/}/}^M/g,^M在vim for windows 下按ctrl+q，ctrl+m输入，linux下按ctrl+v，ctrl+m输入。
io_submit(140166482505728, 78, {
, {data:0x7f7b07731be0, pread, filedes:260, buf:0x95ede000, nbytes:8192, offset:1417216} --//前面加一个逗号，便于以后分析。
, {data:0x7f7b07731ea0, pread, filedes:260, buf:0x884c6000, nbytes:8192, offset:1441792}
, {data:0x7f7b07732160, pread, filedes:260, buf:0x9c0fe000, nbytes:8192, offset:1449984}
, {data:0x7f7b07732420, pread, filedes:260, buf:0x9c720000, nbytes:8192, offset:1564672}
, {data:0x7f7b077326e0, pread, filedes:260, buf:0x9bbf4000, nbytes:8192, offset:1622016}
, {data:0x7f7b077329a0, pread, filedes:260, buf:0x88728000, nbytes:8192, offset:1646592}
, {data:0x7f7b07732c60, pread, filedes:260, buf:0x8892e000, nbytes:8192, offset:1703936}
, {data:0x7f7b07732f20, pread, filedes:260, buf:0x9793e000, nbytes:8192, offset:1802240}
, {data:0x7f7b077331e0, pread, filedes:260, buf:0x92a0e000, nbytes:8192, offset:1818624}
, {data:0x7f7b077334a0, pread, filedes:260, buf:0x8852e000, nbytes:8192, offset:1835008}
, {data:0x7f7b07733760, pread, filedes:260, buf:0x967b2000, nbytes:8192, offset:1859584}
, {data:0x7f7b07733a20, pread, filedes:260, buf:0x978f8000, nbytes:8192, offset:1892352}
, {data:0x7f7b07733ce0, pread, filedes:260, buf:0x99578000, nbytes:8192, offset:2080768}
, {data:0x7f7b07733fa0, pread, filedes:260, buf:0x88460000, nbytes:8192, offset:2195456}
, {data:0x7f7b07734260, pread, filedes:260, buf:0x88932000, nbytes:8192, offset:2211840}
, {data:0x7f7b07734520, pread, filedes:260, buf:0x9c708000, nbytes:8192, offset:2236416}
, {data:0x7f7b077347e0, pread, filedes:260, buf:0x8811e000, nbytes:8192, offset:2310144}
, {data:0x7f7b07734aa0, pread, filedes:260, buf:0x88518000, nbytes:8192, offset:2383872}
, {data:0x7f7b07734d60, pread, filedes:260, buf:0x96aac000, nbytes:8192, offset:2392064}
, {data:0x7f7b07735020, pread, filedes:260, buf:0x88e44000, nbytes:8192, offset:2408448}
, {data:0x7f7b077352e0, pread, filedes:260, buf:0x95ebc000, nbytes:8192, offset:3252224}
, {data:0x7f7b077355a0, pread, filedes:260, buf:0x8869c000, nbytes:8192, offset:3309568}
, {data:0x7f7b07735860, pread, filedes:260, buf:0x888da000, nbytes:8192, offset:3391488}
, {data:0x7f7b07735b20, pread, filedes:260, buf:0x88cd2000, nbytes:8192, offset:3416064}
, {data:0x7f7b07735de0, pread, filedes:260, buf:0x97cc0000, nbytes:8192, offset:3440640}
, {data:0x7f7b077360a0, pread, filedes:260, buf:0x9ce4c000, nbytes:8192, offset:3448832}
, {data:0x7f7b07736360, pread, filedes:260, buf:0x88a1c000, nbytes:8192, offset:3571712}
, {data:0x7f7b07736620, pread, filedes:260, buf:0x9c6ee000, nbytes:8192, offset:3661824}
, {data:0x7f7b077368e0, pread, filedes:260, buf:0x9bc50000, nbytes:8192, offset:3760128}
, {data:0x7f7b07736ba0, pread, filedes:260, buf:0x884b8000, nbytes:8192, offset:3850240}
, {data:0x7f7b07736e60, pread, filedes:260, buf:0x974f4000, nbytes:8192, offset:3932160}
, {data:0x7f7b07737120, pread, filedes:260, buf:0x88c3a000, nbytes:8192, offset:4014080}
, {data:0x7f7b077373e0, pread, filedes:260, buf:0x882e6000, nbytes:8192, offset:4038656}
, {data:0x7f7b077376a0, pread, filedes:260, buf:0x9ccee000, nbytes:8192, offset:4128768}
, {data:0x7f7b07737960, pread, filedes:260, buf:0x88b62000, nbytes:8192, offset:4145152}
, {data:0x7f7b07737c20, pread, filedes:260, buf:0x88cf0000, nbytes:8192, offset:4235264}
, {data:0x7f7b07737ee0, pread, filedes:260, buf:0x8837e000, nbytes:8192, offset:4251648}
, {data:0x7f7b077381a0, pread, filedes:260, buf:0x8869a000, nbytes:8192, offset:4284416}
, {data:0x7f7b07738460, pread, filedes:260, buf:0x9c380000, nbytes:8192, offset:4300800}
, {data:0x7f7b07738720, pread, filedes:260, buf:0x88edc000, nbytes:8192, offset:4374528}
, {data:0x7f7b077389e0, pread, filedes:260, buf:0x97caa000, nbytes:8192, offset:4382720}
, {data:0x7f7b07738ca0, pread, filedes:260, buf:0x885fe000, nbytes:8192, offset:4415488}
, {data:0x7f7b07738f60, pread, filedes:260, buf:0x88ae4000, nbytes:8192, offset:4440064}
, {data:0x7f7b07739220, pread, filedes:260, buf:0x88c9a000, nbytes:8192, offset:4489216}
, {data:0x7f7b077394e0, pread, filedes:260, buf:0x8827a000, nbytes:8192, offset:4612096}
, {data:0x7f7b077397a0, pread, filedes:260, buf:0x9b04a000, nbytes:8192, offset:4636672}
, {data:0x7f7b07739a60, pread, filedes:260, buf:0x975c0000, nbytes:8192, offset:4653056}
, {data:0x7f7b07739d20, pread, filedes:260, buf:0x9c442000, nbytes:8192, offset:4694016}
, {data:0x7f7b07739fe0, pread, filedes:260, buf:0x9cb72000, nbytes:8192, offset:4743168}
, {data:0x7f7b0773a2a0, pread, filedes:260, buf:0x8845e000, nbytes:8192, offset:4841472}
, {data:0x7f7b0773a560, pread, filedes:260, buf:0x88998000, nbytes:8192, offset:4866048}
, {data:0x7f7b0773a820, pread, filedes:260, buf:0x88c58000, nbytes:8192, offset:4898816}
, {data:0x7f7b0773aae0, pread, filedes:260, buf:0x95ecc000, nbytes:8192, offset:4907008}
, {data:0x7f7b0773ada0, pread, filedes:260, buf:0x96414000, nbytes:8192, offset:5005312}
, {data:0x7f7b0773b060, pread, filedes:260, buf:0x9c292000, nbytes:8192, offset:5021696}
, {data:0x7f7b0773b320, pread, filedes:260, buf:0x88ec0000, nbytes:8192, offset:5111808}
, {data:0x7f7b0773b5e0, pread, filedes:260, buf:0x97d2a000, nbytes:8192, offset:5136384}
, {data:0x7f7b0773b8a0, pread, filedes:260, buf:0x9677c000, nbytes:8192, offset:5144576}
, {data:0x7f7b0773bb60, pread, filedes:260, buf:0x9c166000, nbytes:8192, offset:5185536}
, {data:0x7f7b0773be20, pread, filedes:260, buf:0x978cc000, nbytes:8192, offset:5201920}
, {data:0x7f7b0773c0e0, pread, filedes:260, buf:0x9cb38000, nbytes:8192, offset:5373952}
, {data:0x7f7b0773c3a0, pread, filedes:260, buf:0x971b4000, nbytes:8192, offset:5431296}
, {data:0x7f7b0773c660, pread, filedes:260, buf:0x88906000, nbytes:8192, offset:5455872}
, {data:0x7f7b0773c920, pread, filedes:260, buf:0x88d3e000, nbytes:8192, offset:5480448}
, {data:0x7f7b0773cbe0, pread, filedes:260, buf:0x88234000, nbytes:8192, offset:5521408}
, {data:0x7f7b0773cea0, pread, filedes:260, buf:0x970be000, nbytes:8192, offset:5578752}
, {data:0x7f7b0773d160, pread, filedes:260, buf:0x97546000, nbytes:8192, offset:5685248}
, {data:0x7f7b0773d420, pread, filedes:260, buf:0x9c7a8000, nbytes:8192, offset:5824512}
, {data:0x7f7b0773d6e0, pread, filedes:260, buf:0x9c904000, nbytes:8192, offset:5873664}
, {data:0x7f7b0773d9a0, pread, filedes:260, buf:0x8851e000, nbytes:8192, offset:5947392}
, {data:0x7f7b0773dc60, pread, filedes:260, buf:0x9740a000, nbytes:8192, offset:6012928}
, {data:0x7f7b0773df20, pread, filedes:260, buf:0x88eca000, nbytes:8192, offset:6078464}
, {data:0x7f7b0773e1e0, pread, filedes:260, buf:0x881da000, nbytes:8192, offset:6103040}
, {data:0x7f7b0773e4a0, pread, filedes:260, buf:0x885b4000, nbytes:8192, offset:6119424}
, {data:0x7f7b0773e760, pread, filedes:260, buf:0x88b68000, nbytes:8192, offset:6135808}
, {data:0x7f7b0773ea20, pread, filedes:260, buf:0x9b890000, nbytes:8192, offset:6217728}
, {data:0x7f7b0773ece0, pread, filedes:260, buf:0x8814a000, nbytes:8192, offset:6225920}
, {data:0x7f7b0773efa0, pread, filedes:260, buf:0x9d7d6000, nbytes:8192, offset:6283264}
}
) = 78
--//取出offset后的数字。
$ awk '{print $7}' io_submit.txt | cut -d: -f2| sed 's/}//' | xargs -IQ bash -c "echo Q/8192| bc" | tee xxx.txt
173
176
177
191
198
201
208
220
222
224
227
231
254
268
270
273
282
291
292
294
397
404
414
417
420
421
436
447
459
470
480
490
493
504
506
517
519
523
525
534
535
539
542
548
563
566
568
573
579
591
594
598
599
611
613
624
627
628
633
635
656
663
666
669
674
681
694
711
717
726
734
742
745
747
749
759
760
767
--//可以发现读取块的数量是逐步增加的，虽然不是连续的。
$ cat xxx.txt | (echo set head off ;xargs -IQ echo "select id,substr(vc,1,2) vv from t where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)=Q;") | sqlplus -s -l scott/book@book01p | sed -n '/AB/p' | head -4
66 AB
39 AB
58 AB
40 AB
$ cat xxx.txt | (echo set head off ;xargs -IQ echo "select id,substr(vc,1,2) vv from t where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)=Q;") | sqlplus -s -l scott/book@book01p | sed -n '/AB/p' | tail -4
60 AB
44 AB
74 AB
36 AB
--//可以看出整个操作过程，访问索引，然后按照取出的数据块排序，回表读取数据块。
--//这样的好处大家也可以看出来就是按照块排序，"连续跳跃"地扫描对应的数据块，这样读取模式比起1块1块读更加块。
--//至少可能磁盘寻道的时间会减少。
4.输出问题：
--//前面的测试输出最大值，仅仅输出1行，如果输出记录，你会发现输出大部分信息还是按照索引id顺序输出，但是会出现一点点"意外"
--//不能通过提示控制输出顺序。
SCOTT@book01p> alter system flush buffer_cache ;
System altered.
COTT@book01p> select /*+ index(t) */ rowid,id,substr(vc,1,2) vv from t where id between 1 and 5;
ROWID ID VV
------------------ ---------- ----
AAAwf5AAMAAAAJpAAA 1 AB
AAAwf5AAMAAAAL8AAA 2 AB
AAAwf5AAMAAAAHWAAA 3 AB
AAAwf5AAMAAAAGUAAA 5 AB
AAAwf5AAMAAAAJPAAA 4 AB
--//id=5的输出在前。
$ strace -f -p 5156 -e pread,io_submit -a 80 -y
Process 5156 attached
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0+\1\0\3\251\211L\4\0\0\1\4\227\36\0\0\2\0\0\0\372\7\3\0\246\211L\4"..., 8192, 2449408) = 8192
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0,\1\0\3\250\211L\4\0\0\2\4\374}\0\0\2\0\0\0\372\7\3\0\246\211L\4"..., 8192, 2457600) = 8192
--//前2个读是读取索引段。
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0i\2\0\3R\211L\4\0\0\2\4R$\0\0\1\0\0\0\371\7\3\0%\211L\4"..., 8192, 5054464) = 8192
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\374\2\0\3U\211L\4\0\0\2\4F$\0\0\1\0\0\0\371\7\3\0%\211L\4"..., 8192, 6258688) = 8192
pread(260</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\326\1\0\3O\211L\4\0\0\2\4k$\0\0\1\0\0\0\371\7\3\0%\211L\4"..., 8192, 3850240) = 8192
io_submit(140166482505728, 2, {
{data:0x7f7b073e7358, pread, filedes:260, buf:0x91b4a000, nbytes:8192, offset:3309568},
{data:0x7f7b073e7618, pread, filedes:260, buf:0x89ee2000, nbytes:8192, offset:4841472}}) = 2
--//虽然读取按照块号顺序，但是输出却非常独特，基本按照顺序，但是存在一点点变化。
--//输出的顺序与sqlplus下arraysize，rowprefetch(缺省等于1)设置有关.
SCOTT@book01p> set arraysize 5
SCOTT@book01p> alter system flush buffer_cache;
System altered.
SCOTT@book01p> select /*+ index(t) */ rowid,id,substr(vc,1,2) vv from t where id between 1 and 11;
ROWID ID VV
------------------ ---------- ----
AAAwf5AAMAAAAJpAAA 1 AB
AAAwf5AAMAAAAL8AAA 2 AB
AAAwf5AAMAAAAHWAAA 3 AB
AAAwf5AAMAAAADgAAA 6 AB
AAAwf5AAMAAAAGUAAA 4 AB
AAAwf5AAMAAAAJPAAA 5 AB
AAAwf5AAMAAAAIkAAA 11 AB
AAAwf5AAMAAAAKpAAA 7 AB
AAAwf5AAMAAAAJ5AAA 8 AB
AAAwf5AAMAAAAJlAAA 9 AB
AAAwf5AAMAAAADnAAA 10 AB
11 rows selected.
$ strace -f -p 3764 -e pread,io_submit -a 80 -y
Process 3764 attached
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0+\1\0\3\251\211L\4\0\0\1\4\227\36\0\0\2\0\0\0\372\7\3\0\246\211L\4"..., 8192, 2449408) = 8192
io_submit(140367876583424, 1, {{data:0x7fa9eb7f3300, pread, filedes:259, buf:0x73da8000, nbytes:32768, offset:2457600}}) = 1
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0i\2\0\3R\211L\4\0\0\2\4R$\0\0\1\0\0\0\371\7\3\0%\211L\4"..., 8192, 5054464) = 8192
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\374\2\0\3U\211L\4\0\0\2\4F$\0\0\1\0\0\0\371\7\3\0%\211L\4"..., 8192, 6258688) = 8192
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\326\1\0\3O\211L\4\0\0\2\4k$\0\0\1\0\0\0\371\7\3\0%\211L\4"..., 8192, 3850240) = 8192
io_submit(140367876583424, 3, {{data:0x7fa9eb7f3358, pread, filedes:259, buf:0x952e2000, nbytes:8192, offset:1835008}, {data:0x7fa9eb7f3618, pread, filedes:259, buf:0x9549e000, nbytes:8192, offset:3309568}, {data:0x7fa9eb7f38d8, pread, filedes:259, buf:0x94c9a000, nbytes:8192, offset:4841472}}) = 3
io_submit(140367876583424, 5, {{data:0x7fa9eb7f3358, pread, filedes:259, buf:0x95f22000, nbytes:8192, offset:1892352}, {data:0x7fa9eb7f3618, pread, filedes:259, buf:0x9454c000, nbytes:8192, offset:4489216}, {data:0x7fa9eb7f38d8, pread, filedes:259, buf:0x95464000, nbytes:8192, offset:5021696}, {data:0x7fa9eb7f3b98, pread, filedes:259, buf:0x94e64000, nbytes:8192, offset:5185536}, {data:0x7fa9eb7f3e58, pread, filedes:259, buf:0x96014000, nbytes:8192, offset:5578752}}) = 5
^CProcess 3764 detached
--//修改rowprefetch也会出现类似情况。
--//链接 ：[20231013]为什么刷新缓存后输出记录顺序发生变化3.txt 给出的解析：
--//我当时做到这里db file parallel read最大请求blocks的数量是127(注:这个很容易测试出来)，而且还与arraysize参数有关。
--//oracle执行时先从索引收集rowid,确定那些文件与数据块要读取,按照文件号与块号排序,抽取数据块进入缓存后,按照索引的读取顺
--//序数据块(不然不是这样的顺序输出),当然已经在数据缓存的块记录优先填充.当读取数量达到arraysize或者没有剩余记录时输出结
--//果,而最后1条记录会优先输出,这样就会出现看到的情况。
--//如果数据块在数据缓存，输出按照id顺序输出。
SCOTT@book01p> select /*+ index(t) */ rowid,id,substr(vc,1,2) vv from t where id between 1 and 5;
ROWID ID VV
------------------ ---------- ----
AAAwf5AAMAAAAJpAAA 1 AB
AAAwf5AAMAAAAL8AAA 2 AB
AAAwf5AAMAAAAHWAAA 3 AB
AAAwf5AAMAAAAJPAAA 4 AB
AAAwf5AAMAAAAGUAAA 5 AB
--//就是修改id=4，5记录对调，也会出现前面一样的情况。
