--//當(dāng)年提的問(wèn)題，鏈接http://blog.itpub.net/267265/viewspace-2763181/=> [20210316]為什么刷新緩存后輸出記錄順序發(fā)生變化.txt，
--//正好別人問(wèn)我，順便我重復(fù)看了一下，順便解答這個(gè)問(wèn)題，實(shí)際上也許解答不對(duì)，許多行為我僅僅是猜測(cè)。
--//實(shí)際上這個(gè)問(wèn)題在于oracle 在索引范圍掃描時(shí)如果數(shù)據(jù)塊不在緩存，有可能采用db file parallel read的方式讀取數(shù)據(jù)塊。
--//最直觀的表現(xiàn)在執(zhí)行計(jì)劃上出現(xiàn)TABLE ACCESS BY INDEX ROWID BATCHED。
--//這個(gè)特性僅僅出現(xiàn)在12c以后，另外windows下我的測(cè)試有點(diǎn)奇怪，大家可以自行測(cè)試。我不再說(shuō)明，我的測(cè)試在linux環(huán)境進(jìn)行。
1.環(huán)境：
TTT@192.168.2.7:1521/orcl> @ ver1
TTT@192.168.2.7:1521/orcl> @ pr
==============================
PORT_STRING?????????????????? : x86_64/Linux 2.4.xx
VERSION?????????????????????? : 18.0.0.0.0
BANNER??????????????????????? : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL?????????????????? : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY???????????????? : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID??????????????????????? : 0
PL/SQL procedure successfully completed.
2.建立測(cè)試?yán)樱?br>TTT@192.168.2.7:1521/orcl> create table t? pctfree 99 as select level id, lpad(level, 3500, '0') vc from dual connect by level <= 500 order by dbms_random.random;
Table created.
--//建立的表非常特殊1塊1條記錄,這樣便于后面的分析。
TTT@192.168.2.7:1521/orcl> create index i_t_id on t(id) ;
Index created.
TTT@192.168.2.7:1521/orcl> exec dbms_stats.gather_table_stats(null, 'T', cascade=>true);
PL/SQL procedure successfully completed.
3.測(cè)試：
TTT@orcl> show rowprefetch array
rowprefetch 1
arraysize 200
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where id between 1 and 10;
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
???????? 2 002??? AABnNmAAMAAC/IqAAA
???????? 3 003??? AABnNmAAMAAC+78AAA
???????? 4 004??? AABnNmAAMAAC/KSAAA
???????? 5 005??? AABnNmAAMAAC+7pAAA
???????? 6 006??? AABnNmAAMAAC/L2AAA
???????? 7 007??? AABnNmAAMAAC/K8AAA
???????? 8 008??? AABnNmAAMAAC/IgAAA
???????? 9 009??? AABnNmAAMAAC/G5AAA
??????? 10 010??? AABnNmAAMAAC/J7AAA
10 rows selected.
--//執(zhí)行計(jì)劃選擇索引范圍掃描與全部數(shù)據(jù)塊在緩存的情況下按照id順序輸出,如果不出現(xiàn)上面的執(zhí)行輸出，你可以再執(zhí)行1次看看是否出
--//現(xiàn).
TTT@orcl> alter system flush buffer_cache;
System altered.
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where rowid='AABnNmAAMAAC/L2AAA';
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 6 006??? AABnNmAAMAAC/L2AAA
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where id between 1 and 10;
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
???????? 2 002??? AABnNmAAMAAC/IqAAA
???????? 3 003??? AABnNmAAMAAC+78AAA
???????? 6 006??? AABnNmAAMAAC/L2AAA
??????? 10 010??? AABnNmAAMAAC/J7AAA
???????? 4 004??? AABnNmAAMAAC/KSAAA
???????? 5 005??? AABnNmAAMAAC+7pAAA
???????? 7 007??? AABnNmAAMAAC/K8AAA
???????? 8 008??? AABnNmAAMAAC/IgAAA
???????? 9 009??? AABnNmAAMAAC/G5AAA
10 rows selected.
--//如果緩存id=6的數(shù)據(jù)塊,跑到前面去好理解,因?yàn)槠渌鼣?shù)據(jù)塊不再緩存里面,db file parallel read操作對(duì)于上面的情況,
--//我的理解先通過(guò)rowid確定那些數(shù)據(jù)塊需要讀取,然后按照文件號(hào)以及數(shù)據(jù)塊號(hào)排序,從小到大讀取.id=6數(shù)據(jù)已經(jīng)在數(shù)據(jù)緩存,
--//這樣讀取操作已經(jīng)進(jìn)入fetch池(這個(gè)是我亂想的),優(yōu)先輸出.
--//你還可以看到id=2,3的記錄在id=6的前面,為什么呢?實(shí)際上當(dāng)數(shù)據(jù)不在緩存時(shí)并不是馬上采用db file parallel read操作,
--//而是先采用db file sequential read(當(dāng)然在數(shù)據(jù)緩存情況除外)2次,再有讀取操作才有可能采用db file parallel read.
--//注:有1個(gè)特例,如果要訪問(wèn)的數(shù)據(jù)塊是連續(xù)的讀取出現(xiàn)的等待事件是db file scattered read.
--//你可以建表時(shí)order by 1測(cè)試的等待事件是db file scattered read.
--//問(wèn)題在于為什么刷新數(shù)據(jù)緩存后,id=10的記錄顯示在前面.
4.繼續(xù)測(cè)試:
TTT@orcl> alter system flush buffer_cache;
System altered.
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where rowid='AABnNmAAMAAC/IqAAA';
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 2 002??? AABnNmAAMAAC/IqAAA
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where rowid='AABnNmAAMAAC+78AAA';
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 3 003??? AABnNmAAMAAC+78AAA
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where rowid='AABnNmAAMAAC/L2AAA';
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 6 006??? AABnNmAAMAAC/L2AAA
--//緩存id=2,3，6的數(shù)據(jù)塊.?? ??? ?
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where id between 1 and 10;
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
???????? 2 002??? AABnNmAAMAAC/IqAAA
???????? 3 003??? AABnNmAAMAAC+78AAA
???????? 4 004??? AABnNmAAMAAC/KSAAA
???????? 6 006??? AABnNmAAMAAC/L2AAA
??????? 10 010??? AABnNmAAMAAC/J7AAA
???????? 5 005??? AABnNmAAMAAC+7pAAA
???????? 7 007??? AABnNmAAMAAC/K8AAA
???????? 8 008??? AABnNmAAMAAC/IgAAA
???????? 9 009??? AABnNmAAMAAC/G5AAA
10 rows selected.
--//總之是先出現(xiàn)1,2次db file sequential read,才會(huì)有可能采用db file parallel read操作.
TTT@orcl> alter system flush buffer_cache;
System altered.
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where rowid='AABnNmAAMAAC/JYAAA';
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where rowid='AABnNmAAMAAC/IqAAA';
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 2 002??? AABnNmAAMAAC/IqAAA
TTT@orcl>? select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where rowid='AABnNmAAMAAC+78AAA';
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 3 003??? AABnNmAAMAAC+78AAA
--//緩存id=1,2,3的數(shù)據(jù)塊.
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where id between 1 and 10;
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
???????? 2 002??? AABnNmAAMAAC/IqAAA
???????? 3 003??? AABnNmAAMAAC+78AAA
???????? 4 004??? AABnNmAAMAAC/KSAAA
??????? 10 010??? AABnNmAAMAAC/J7AAA
???????? 5 005??? AABnNmAAMAAC+7pAAA
???????? 6 006??? AABnNmAAMAAC/L2AAA
???????? 7 007??? AABnNmAAMAAC/K8AAA
???????? 8 008??? AABnNmAAMAAC/IgAAA
???????? 9 009??? AABnNmAAMAAC/G5AAA
10 rows selected.
--//但是還是無(wú)法理解為什么這樣的情況下id=10優(yōu)先輸出.
5.繼續(xù)分析:
TTT@orcl> update t set id=10 where id=11;
1 row updated.
TTT@orcl> commit ;
Commit complete.
--//修改id=11為id=10,這樣輸出多一條記錄.
TTT@orcl> alter system flush buffer_cache;
System altered.
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where id between 1 and 10;
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
???????? 2 002??? AABnNmAAMAAC/IqAAA
???????? 3 003??? AABnNmAAMAAC+78AAA
??????? 10 011??? AABnNmAAMAAC/N/AAA
???????? 4 004??? AABnNmAAMAAC/KSAAA
???????? 5 005??? AABnNmAAMAAC+7pAAA
???????? 6 006??? AABnNmAAMAAC/L2AAA
???????? 7 007??? AABnNmAAMAAC/K8AAA
???????? 8 008??? AABnNmAAMAAC/IgAAA
???????? 9 009??? AABnNmAAMAAC/G5AAA
??????? 10 010??? AABnNmAAMAAC/J7AAA
11 rows selected.
--//在sqlplus下執(zhí)行fetch的數(shù)量總是1,array,array,..,剩下的數(shù)據(jù).
--//注:前面的1=rowprefetch,rowprefetch與array的關(guān)系有點(diǎn)復(fù)雜,我不建議設(shè)置rowprefetch>=arraysize的情況.
--//rowprefetch 的設(shè)置僅僅sqlplus 12c以上版本才能設(shè)置.
--//這樣看來(lái)id=10的優(yōu)先輸出一定與db file parallel read操作有關(guān).
6.做1次10046跟蹤:
TTT@orcl> alter system flush buffer_cache;
System altered.
TTT@orcl> alter session set events '10046 trace name context forever, level 12';
Session altered.
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where id between 1 and 10;
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
???????? 2 002??? AABnNmAAMAAC/IqAAA
???????? 3 003??? AABnNmAAMAAC+78AAA
??????? 10 011??? AABnNmAAMAAC/N/AAA
???????? 4 004??? AABnNmAAMAAC/KSAAA
???????? 5 005??? AABnNmAAMAAC+7pAAA
???????? 6 006??? AABnNmAAMAAC/L2AAA
???????? 7 007??? AABnNmAAMAAC/K8AAA
???????? 8 008??? AABnNmAAMAAC/IgAAA
???????? 9 009??? AABnNmAAMAAC/G5AAA
??????? 10 010??? AABnNmAAMAAC/J7AAA
11 rows selected.
TTT@orcl> alter session set events '10046 trace name context off';
Session altered.
TTT@192.168.2.7:1521/orcl> @ oid 422758
owner object_name object_type??????? SUBOBJECT_NAME CREATED???????????? LAST_DDL_TIME?????? status??? DATA_OBJECT_ID? OBJECT_ID
----- ----------- ------------------ -------------- ------------------- ------------------- --------- -------------- ----------
TTT?? T?????????? TABLE???????????????????????????? 2023-10-13 09:19:25 2023-10-13 09:19:46 VALID???????????? 422758???? 422758
TTT@192.168.2.7:1521/orcl> @ oid 422759
owner object_name object_type??????? SUBOBJECT_NAME CREATED???????????? LAST_DDL_TIME?????? status??? DATA_OBJECT_ID? OBJECT_ID
----- ----------- ------------------ -------------- ------------------- ------------------- --------- -------------- ----------
TTT?? I_T_ID????? INDEX???????????????????????????? 2023-10-13 09:19:45 2023-10-13 09:19:45 VALID???????????? 422759???? 422759
$ egrep "db file|FETCH" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_28039.trc
WAIT #139969772337544: nam='db file sequential read' ela= 428 file#=41 block#=782811 blocks=1 obj#=422759 tim=11387992574423?? --//索引root
WAIT #139969772337544: nam='db file sequential read' ela= 281 file#=41 block#=782812 blocks=1 obj#=422759 tim=11387992574858?? --//索引葉子
WAIT #139969772337544: nam='db file sequential read' ela= 20377 file#=41 block#=782936 blocks=1 obj#=422758 tim=11387992595353 --//id=1
FETCH #139969772337544:c=1320,e=21673,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3446268138,tim=11387992595609
WAIT #139969772337544: nam='db file sequential read' ela= 3548 file#=41 block#=782890 blocks=1 obj#=422758 tim=11387992601138? --//id=2
WAIT #139969772337544: nam='db file sequential read' ela= 11913 file#=41 block#=782076 blocks=1 obj#=422758 tim=11387992613217 --//id=3
WAIT #139969772337544: nam='db file parallel read' ela= 31429 files=1 blocks=8 requests=8 obj#=422758 tim=11387992645691?????? --//id=4..10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #139969772337544:c=2076,e=48359,p=10,cr=11,cu=0,mis=0,r=10,dep=0,og=1,plh=3446268138,tim=11387992645894
--//注：db file parallel read 看到blocks=8,具體讀取那些塊，你可以使用strace -fp spid -e pread|pread64 跟蹤 確定。
--//我以前做過(guò)類似測(cè)試，不再贅述。
--//我當(dāng)時(shí)做到這里db file parallel read最大請(qǐng)求blocks的數(shù)量是127(注:這個(gè)很容易測(cè)試出來(lái))，而且還與arraysize參數(shù)有關(guān)。
--//oracle執(zhí)行時(shí)先從索引收集rowid,確定那些文件與數(shù)據(jù)塊要讀取,按照文件號(hào)與塊號(hào)排序,抽取數(shù)據(jù)塊進(jìn)入緩存后,按照索引的讀取順
--//序數(shù)據(jù)塊(不然不是這樣的順序輸出),當(dāng)然已經(jīng)在數(shù)據(jù)緩存的塊記錄優(yōu)先填充.當(dāng)讀取數(shù)量達(dá)到arraysize或者沒(méi)有剩余記錄時(shí)輸出結(jié)
--//果,而最后1條記錄會(huì)優(yōu)先輸出,這樣就會(huì)出現(xiàn)前面id=10優(yōu)先輸出的情況.
--//注:解析的非常牽強(qiáng)，那位能給出更好更合理的解析。
--//會(huì)不會(huì)要判斷是否達(dá)到arraysize數(shù)量，我的sql語(yǔ)句沒(méi)有加入過(guò)濾，下個(gè)星期繼續(xù)測(cè)試看看。
--//設(shè)置arraysize=5看看.
TTT@orcl> set arraysize 5
TTT@orcl> alter system flush buffer_cache;
System altered.
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where id between 1 and 10;
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
???????? 2 002??? AABnNmAAMAAC/IqAAA
???????? 3 003??? AABnNmAAMAAC+78AAA
???????? 6 006??? AABnNmAAMAAC/L2AAA
???????? 4 004??? AABnNmAAMAAC/KSAAA
???????? 5 005??? AABnNmAAMAAC+7pAAA
??????? 10 011??? AABnNmAAMAAC/N/AAA
???????? 7 007??? AABnNmAAMAAC/K8AAA
???????? 8 008??? AABnNmAAMAAC/IgAAA
???????? 9 009??? AABnNmAAMAAC/G5AAA
??????? 10 010??? AABnNmAAMAAC/J7AAA
11 rows selected.
--//如果數(shù)據(jù)全部在緩存輸出順序
1
2,3,4,5,6
7,8,9,10,10(vc=011)
--//全部不再緩存順序應(yīng)該是
1
2,3,6,4,5
10(vc=011),7,8,9,10
--//與真實(shí)的測(cè)試結(jié)果能對(duì)上。
--//如果arraysize=4呢?
--//如果數(shù)據(jù)全部在緩存輸出順序
1
2,3,4,5
6,7,8,9
10,10(vc=011)
--//全部不再緩存順序應(yīng)該是
1
2,3,5,4
9,6,7,8
10(vc=011),10
--//驗(yàn)證我的判斷是否正確
TTT@orcl> set arraysize 4
TTT@orcl> alter system flush buffer_cache;
System altered.
TTT@orcl> select /*+ index(t) */ id,substr(vc,3498,3),rowid from? t where id between 1 and 10;
??????? ID SUBSTR ROWID
---------- ------ ------------------
???????? 1 001??? AABnNmAAMAAC/JYAAA
???????? 2 002??? AABnNmAAMAAC/IqAAA
???????? 3 003??? AABnNmAAMAAC+78AAA
???????? 5 005??? AABnNmAAMAAC+7pAAA
???????? 4 004??? AABnNmAAMAAC/KSAAA
???????? 9 009??? AABnNmAAMAAC/G5AAA
???????? 6 006??? AABnNmAAMAAC/L2AAA
???????? 7 007??? AABnNmAAMAAC/K8AAA
???????? 8 008??? AABnNmAAMAAC/IgAAA
??????? 10 011??? AABnNmAAMAAC/N/AAA
??????? 10 010??? AABnNmAAMAAC/J7AAA
11 rows selected.
--//你可以發(fā)現(xiàn)能與我的猜測(cè)對(duì)上.
--//10046跟蹤內(nèi)容如下:
$ egrep "db file|FETCH" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_28039.trc
WAIT #139969774020880: nam='db file sequential read' ela= 341 file#=41 block#=782811 blocks=1 obj#=422759 tim=11389669160631
WAIT #139969774020880: nam='db file sequential read' ela= 220 file#=41 block#=782812 blocks=1 obj#=422759 tim=11389669160991
WAIT #139969774020880: nam='db file sequential read' ela= 389 file#=41 block#=782936 blocks=1 obj#=422758 tim=11389669161510
FETCH #139969774020880:c=880,e=1404,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3446268138,tim=11389669161591
WAIT #139969774020880: nam='db file sequential read' ela= 377 file#=41 block#=782890 blocks=1 obj#=422758 tim=11389669163820
WAIT #139969774020880: nam='db file sequential read' ela= 490 file#=41 block#=782076 blocks=1 obj#=422758 tim=11389669164442
WAIT #139969774020880: nam='db file parallel read' ela= 208 files=1 blocks=2 requests=2 obj#=422758 tim=11389669165139
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #139969774020880:c=1088,e=1884,p=4,cr=5,cu=0,mis=0,r=4,dep=0,og=1,plh=3446268138,tim=11389669165249
WAIT #139969774020880: nam='db file parallel read' ela= 27 files=1 blocks=4 requests=4 obj#=422758 tim=11389669166538
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #139969774020880:c=439,e=845,p=4,cr=5,cu=0,mis=0,r=4,dep=0,og=1,plh=3446268138,tim=11389669166657
WAIT #139969774020880: nam='db file parallel read' ela= 79 files=1 blocks=2 requests=2 obj#=422758 tim=11389669167635
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #139969774020880:c=515,e=631,p=2,cr=3,cu=0,mis=0,r=2,dep=0,og=1,plh=3446268138,tim=11389669167746
--//sqlplus arraysize設(shè)置與db file parallel read的最大讀取塊數(shù)量有關(guān).
7.總結(jié):
--//實(shí)際上這些細(xì)節(jié)并不重要,說(shuō)明幾點(diǎn):
1.sqlplus arraysize設(shè)置與db file parallel read的最大讀取塊數(shù)量有關(guān).
2.要排序輸出一定顯示加入order by操作.
3.我給再好好看看我以前的鏈接:[20200824]12c sqlplus rowprefetch arraysize 顯示行數(shù)量的關(guān)系.txt =>http://blog.itpub.net/267265/viewspace-2714661/
4.我前面的查詢沒(méi)有出現(xiàn)過(guò)濾,下個(gè)星期應(yīng)該補(bǔ)充測(cè)試出現(xiàn)過(guò)濾的情況。
8.補(bǔ)充測(cè)試：
--//grant EXECUTE ON? dbms_lock to TTT;
CREATE OR REPLACE FUNCTION sleepT (seconds IN NUMBER)
?? RETURN timestamp
AS
BEGIN
?? --//sys.DBMS_LOCK.sleep (seconds);
?? sys.DBMS_session.sleep (seconds);
?? RETURN SYSTIMESTAMP-seconds/86400;
END;
/
$ cat aa.txt
set term off
set timing on
set arraysize &1
set rowprefetch &2
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
set term on
select /*+ index(t) */ id,substr(vc,3498,3),rowid,sleept(1) from t where id between 1 and 10;
set term off
alter session set events '10046 trace name context off';
set timing off
set term on
quit
$ cat /usr/local/bin/ts.awk
#! /bin/bash
awk '{ print strftime("[%Y-%m-%d %H:%M:%S]"), $0 }'
$ sqlplus -s -l ttt/oracle@orcl @ aa.txt 200 1 | ts.awk
[2023-10-16 08:39:46]
[2023-10-16 08:39:46]???????? ID SUBSTR ROWID????????????? SLEEPT(1)
[2023-10-16 08:39:46] ---------- ------ ------------------ ------------------------------
[2023-10-16 08:39:46]????????? 1 001??? AABnNmAAMAAC/JYAAA 2023-10-16 08:39:35.000000000
[2023-10-16 08:39:46]????????? 2 002??? AABnNmAAMAAC/IqAAA 2023-10-16 08:39:36.000000000
[2023-10-16 08:39:46]????????? 3 003??? AABnNmAAMAAC+78AAA 2023-10-16 08:39:37.000000000
[2023-10-16 08:39:46]???????? 10 011??? AABnNmAAMAAC/N/AAA 2023-10-16 08:39:38.000000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[2023-10-16 08:39:46]????????? 4 004??? AABnNmAAMAAC/KSAAA 2023-10-16 08:39:39.000000000
[2023-10-16 08:39:46]????????? 5 005??? AABnNmAAMAAC+7pAAA 2023-10-16 08:39:40.000000000
[2023-10-16 08:39:46]????????? 6 006??? AABnNmAAMAAC/L2AAA 2023-10-16 08:39:41.000000000
[2023-10-16 08:39:46]????????? 7 007??? AABnNmAAMAAC/K8AAA 2023-10-16 08:39:42.000000000
[2023-10-16 08:39:46]????????? 8 008??? AABnNmAAMAAC/IgAAA 2023-10-16 08:39:43.000000000
[2023-10-16 08:39:46]????????? 9 009??? AABnNmAAMAAC/G5AAA 2023-10-16 08:39:44.000000000
[2023-10-16 08:39:46]???????? 10 010??? AABnNmAAMAAC/J7AAA 2023-10-16 08:39:45.000000000
[2023-10-16 08:39:46]
[2023-10-16 08:39:46] 11 rows selected.
[2023-10-16 08:39:46]
[2023-10-16 08:39:46] Elapsed: 00:00:11.09
--//顯示輸出有11秒間隔.
--//注意看sleept(1)列，顯示的時(shí)間順序增加的。
$ sqlplus -s -l ttt/oracle@orcl @ aa.txt 4 1 | ts.awk
[2023-10-19 10:04:26]
[2023-10-19 10:04:26]???????? ID SUBSTR ROWID????????????? SLEEPT(1)
[2023-10-19 10:04:26] ---------- ------ ------------------ -----------------------------
[2023-10-19 10:04:26]????????? 1 001??? AABnNmAAMAAC/JYAAA 2023-10-19 10:04:21.000000000
[2023-10-19 10:04:26]????????? 2 002??? AABnNmAAMAAC/IqAAA 2023-10-19 10:04:22.000000000
[2023-10-19 10:04:26]????????? 3 003??? AABnNmAAMAAC+78AAA 2023-10-19 10:04:23.000000000
[2023-10-19 10:04:26]????????? 5 005??? AABnNmAAMAAC+7pAAA 2023-10-19 10:04:24.000000000
[2023-10-19 10:04:30]????????? 4 004??? AABnNmAAMAAC/KSAAA 2023-10-19 10:04:25.000000000
[2023-10-19 10:04:30]????????? 9 009??? AABnNmAAMAAC/G5AAA 2023-10-19 10:04:26.000000000
[2023-10-19 10:04:30]????????? 6 006??? AABnNmAAMAAC/L2AAA 2023-10-19 10:04:27.000000000
[2023-10-19 10:04:30]????????? 7 007??? AABnNmAAMAAC/K8AAA 2023-10-19 10:04:28.000000000
[2023-10-19 10:04:32]????????? 8 008??? AABnNmAAMAAC/IgAAA 2023-10-19 10:04:29.000000000
[2023-10-19 10:04:32]???????? 10 011??? AABnNmAAMAAC/N/AAA 2023-10-19 10:04:30.000000000
[2023-10-19 10:04:32]???????? 10 010??? AABnNmAAMAAC/J7AAA 2023-10-19 10:04:31.000000000
[2023-10-19 10:04:32]
[2023-10-19 10:04:32] 11 rows selected.
[2023-10-19 10:04:32]
[2023-10-19 10:04:32] Elapsed: 00:00:11.01
--//fetch的數(shù)量是 1,4,4,2.
--//而顯示的數(shù)量(看前面的時(shí)間)? 4,4,3。
--//參考：[20200824]12c sqlplus rowprefetch arraysize 顯示行數(shù)量的關(guān)系.txt =>http://blog.itpub.net/267265/viewspace-2714661/
$ sqlplus -s -l ttt/oracle@orcl @ aa.txt 2 1 | ts.awk
[2023-10-19 10:03:24]
[2023-10-19 10:03:24]???????? ID SUBSTR ROWID????????????? SLEEPT(1)
[2023-10-19 10:03:24] ---------- ------ ------------------ -----------------------------
[2023-10-19 10:03:24]????????? 1 001??? AABnNmAAMAAC/JYAAA 2023-10-19 10:03:21.000000000
[2023-10-19 10:03:24]????????? 2 002??? AABnNmAAMAAC/IqAAA 2023-10-19 10:03:22.000000000
[2023-10-19 10:03:26]????????? 3 003??? AABnNmAAMAAC+78AAA 2023-10-19 10:03:23.000000000
[2023-10-19 10:03:26]????????? 4 004??? AABnNmAAMAAC/KSAAA 2023-10-19 10:03:24.000000000
[2023-10-19 10:03:28]????????? 5 005??? AABnNmAAMAAC+7pAAA 2023-10-19 10:03:25.000000000
[2023-10-19 10:03:28]????????? 7 007??? AABnNmAAMAAC/K8AAA 2023-10-19 10:03:26.000000000
[2023-10-19 10:03:30]????????? 6 006??? AABnNmAAMAAC/L2AAA 2023-10-19 10:03:27.000000000
[2023-10-19 10:03:30]????????? 9 009??? AABnNmAAMAAC/G5AAA 2023-10-19 10:03:28.000000000
[2023-10-19 10:03:33]????????? 8 008??? AABnNmAAMAAC/IgAAA 2023-10-19 10:03:29.000000000
[2023-10-19 10:03:33]???????? 10 011??? AABnNmAAMAAC/N/AAA 2023-10-19 10:03:31.000000000
[2023-10-19 10:03:33]???????? 10 010??? AABnNmAAMAAC/J7AAA 2023-10-19 10:03:32.000000000
[2023-10-19 10:03:33]
[2023-10-19 10:03:33] 11 rows selected.
[2023-10-19 10:03:33]
[2023-10-19 10:03:33] Elapsed: 00:00:11.06
--//我不展開(kāi)分析了。僅僅7,6 9,8 10(vc=011),10 輸出順序反了。
--//補(bǔ)充加入過(guò)濾的情況,還原id=11的記錄。
update t set id=11 where substr(vc,3498,3)='011';
commit ;
--//加入過(guò)濾條件：
$ cat ab.txt
set term off
set timing on
set arraysize &1
set rowprefetch &2
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
set term on
select /*+ index(t) */ id,substr(vc,3498,3),rowid,sleept(1) from t where id between 1 and 10 and substr(vc,3498,3)<>'005';
set term off
alter session set events '10046 trace name context off';
set timing off
set term on
quit
--//不輸出id =5的記錄。
$ sqlplus -s -l ttt/oracle@orcl @ ab.txt 6 1 | ts.awk
[2023-10-19 10:07:03]
[2023-10-19 10:07:03]???????? ID SUBSTR ROWID????????????? SLEEPT(1)
[2023-10-19 10:07:03] ---------- ------ ------------------ ------------------------------
[2023-10-19 10:07:03]????????? 1 001??? AABnNmAAMAAC/JYAAA 2023-10-19 10:06:56.000000000
[2023-10-19 10:07:03]????????? 2 002??? AABnNmAAMAAC/IqAAA 2023-10-19 10:06:57.000000000
[2023-10-19 10:07:03]????????? 3 003??? AABnNmAAMAAC+78AAA 2023-10-19 10:06:58.000000000
[2023-10-19 10:07:03]????????? 7 007??? AABnNmAAMAAC/K8AAA 2023-10-19 10:06:59.000000000
[2023-10-19 10:07:03]????????? 4 004??? AABnNmAAMAAC/KSAAA 2023-10-19 10:07:00.000000000
[2023-10-19 10:07:03]????????? 6 006??? AABnNmAAMAAC/L2AAA 2023-10-19 10:07:01.000000000
[2023-10-19 10:07:05]????????? 8 008??? AABnNmAAMAAC/IgAAA 2023-10-19 10:07:02.000000000
[2023-10-19 10:07:05]???????? 10 010??? AABnNmAAMAAC/J7AAA 2023-10-19 10:07:03.000000000
[2023-10-19 10:07:05]????????? 9 009??? AABnNmAAMAAC/G5AAA 2023-10-19 10:07:04.000000000
[2023-10-19 10:07:05]
[2023-10-19 10:07:05] 9 rows selected.
[2023-10-19 10:07:05]
[2023-10-19 10:07:05] Elapsed: 00:00:09.01
$ egrep "db file|FETCH" orclcdb_ora_32259.trc
WAIT #140509161269912: nam='db file sequential read' ela= 511 file#=41 block#=782811 blocks=1 obj#=422759 tim=11905890260390?? --//索引root
WAIT #140509161269912: nam='db file sequential read' ela= 251 file#=41 block#=782812 blocks=1 obj#=422759 tim=11905890260831?? --//索引葉子
WAIT #140509161269912: nam='db file sequential read' ela= 516 file#=41 block#=782936 blocks=1 obj#=422758 tim=11905890261495?? --//id=1
FETCH #140509161269912:c=1620,e=1001787,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3446268138,tim=11905891261547????????????????? ?
WAIT #140509161269912: nam='db file sequential read' ela= 581 file#=41 block#=782890 blocks=1 obj#=422758 tim=11905891264701?? --//id=2
WAIT #140509161269912: nam='db file sequential read' ela= 558 file#=41 block#=782076 blocks=1 obj#=422758 tim=11905892265105?? --//id=3
WAIT #140509161269912: nam='db file parallel read' ela= 222 files=1 blocks=4 requests=4 obj#=422758 tim=11905893266587???????? --//id= 4..7
--//可以看出總是把db file parallel read操作后的最后的記錄id =7優(yōu)先輸出。
WAIT #140509161269912: nam='db file sequential read' ela= 669 file#=41 block#=782880 blocks=1 obj#=422758 tim=11905896267269?? --//id＝8
--//因?yàn)槲壹尤脒^(guò)濾條件substr(vc,3498,3)<>'005',這樣沒(méi)有達(dá)到fetch=6的情況，單獨(dú)做了一次db file sequential read。
FETCH #140509161269912:c=5071,e=6004437,p=7,cr=8,cu=0,mis=0,r=6,dep=0,og=1,plh=3446268138,tim=11905897268386
WAIT #140509161269912: nam='db file parallel read' ela= 307 files=1 blocks=2 requests=2 obj#=422758 tim=11905897270746???????? --//id=9,10
FETCH #140509161269912:c=1746,e=2000488,p=2,cr=3,cu=0,mis=0,r=2,dep=0,og=1,plh=3446268138,tim=11905899270318
--//可以看出一個(gè)規(guī)律，在做db file parallel read操作后，最后的滿足輸出條件的記錄總是優(yōu)先輸出。
--//至于為什么我不知道。
文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-711004.html
文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-711004.html
到了這里，關(guān)于[20231013]為什么刷新緩存后輸出記錄順序發(fā)生變化3.txt的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
