[20231013]为什么刷新缓存后输出记录顺序发生变化3.txt
--//当年提的问题，链接http://blog.itpub.net/267265/viewspace-2763181/=> [20210316]为什么刷新缓存后输出记录顺序发生变化.txt，
--//正好别人问我，顺便我重复看了一下，顺便解答这个问题，实际上也许解答不对，许多行为我仅仅是猜测。
--//实际上这个问题在于oracle 在索引范围扫描时如果数据块不在缓存，有可能采用db file parallel read的方式读取数据块。
--//最直观的表现在执行计划上出现TABLE ACCESS BY INDEX ROWID BATCHED。
--//这个特性仅仅出现在12c以后，另外windows下我的测试有点奇怪，大家可以自行测试。我不再说明，我的测试在linux环境进行。
1.环境：
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.建立测试例子：
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.测试：
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.
--//执行计划选择索引范围扫描与全部数据块在缓存的情况下按照id顺序输出,如果不出现上面的执行输出，你可以再执行1次看看是否出
--//现.
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的数据块,跑到前面去好理解,因为其它数据块不再缓存里面,db file parallel read操作对于上面的情况,
--//我的理解先通过rowid确定那些数据块需要读取,然后按照文件号以及数据块号排序,从小到大读取.id=6数据已经在数据缓存,
--//这样读取操作已经进入fetch池(这个是我乱想的),优先输出.
--//你还可以看到id=2,3的记录在id=6的前面,为什么呢?实际上当数据不在缓存时并不是马上采用db file parallel read操作,
--//而是先采用db file sequential read(当然在数据缓存情况除外)2次,再有读取操作才有可能采用db file parallel read.
--//注:有1个特例,如果要访问的数据块是连续的读取出现的等待事件是db file scattered read.
--//你可以建表时order by 1测试的等待事件是db file scattered read.
--//问题在于为什么刷新数据缓存后,id=10的记录显示在前面.
4.继续测试:
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的数据块.
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.
--//总之是先出现1,2次db file sequential read,才会有可能采用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的数据块.
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.
--//但是还是无法理解为什么这样的情况下id=10优先输出.
5.继续分析:
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下执行fetch的数量总是1,array,array,..,剩下的数据.
--//注:前面的1=rowprefetch,rowprefetch与array的关系有点复杂,我不建议设置rowprefetch>=arraysize的情况.
--//rowprefetch 的设置仅仅sqlplus 12c以上版本才能设置.
--//这样看来id=10的优先输出一定与db file parallel read操作有关.
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 跟踪 确定。
--//我以前做过类似测试，不再赘述。
--//我当时做到这里db file parallel read最大请求blocks的数量是127(注:这个很容易测试出来)，而且还与arraysize参数有关。
--//oracle执行时先从索引收集rowid,确定那些文件与数据块要读取,按照文件号与块号排序,抽取数据块进入缓存后,按照索引的读取顺
--//序数据块(不然不是这样的顺序输出),当然已经在数据缓存的块记录优先填充.当读取数量达到arraysize或者没有剩余记录时输出结
--//果,而最后1条记录会优先输出,这样就会出现前面id=10优先输出的情况.
--//注:解析的非常牵强，那位能给出更好更合理的解析。
--//会不会要判断是否达到arraysize数量，我的sql语句没有加入过滤，下个星期继续测试看看。
--//设置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.
--//如果数据全部在缓存输出顺序
1
2,3,4,5,6
7,8,9,10,10(vc=011)
--//全部不再缓存顺序应该是
1
2,3,6,4,5
10(vc=011),7,8,9,10
--//与真实的测试结果能对上。
--//如果arraysize=4呢?
--//如果数据全部在缓存输出顺序
1
2,3,4,5
6,7,8,9
10,10(vc=011)
--//全部不再缓存顺序应该是
1
2,3,5,4
9,6,7,8
10(vc=011),10
--//验证我的判断是否正确
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.
--//你可以发现能与我的猜测对上.
--//10046跟踪内容如下:
$ 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设置与db file parallel read的最大读取块数量有关.
7.总结:
--//实际上这些细节并不重要,说明几点:
1.sqlplus arraysize设置与db file parallel read的最大读取块数量有关.
2.要排序输出一定显示加入order by操作.
3.我给再好好看看我以前的链接:[20200824]12c sqlplus rowprefetch arraysize 显示行数量的关系.txt =>http://blog.itpub.net/267265/viewspace-2714661/
4.我前面的查询没有出现过滤,下个星期应该补充测试出现过滤的情况。
8.补充测试：
--//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)列，显示的时间顺序增加的。
$ 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的数量是 1,4,4,2.
--//而显示的数量(看前面的时间) 4,4,3。
--//参考：[20200824]12c sqlplus rowprefetch arraysize 显示行数量的关系.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
--//我不展开分析了。仅仅7,6 9,8 10(vc=011),10 输出顺序反了。
--//补充加入过滤的情况,还原id=11的记录。
update t set id=11 where substr(vc,3498,3)='011';
commit ;
--//加入过滤条件：
$ 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优先输出。
WAIT #140509161269912: nam='db file sequential read' ela= 669 file#=41 block#=782880 blocks=1 obj#=422758 tim=11905896267269 --//id＝8
--//因为我加入过滤条件substr(vc,3498,3)<>'005',这样没有达到fetch=6的情况，单独做了一次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
--//可以看出一个规律，在做db file parallel read操作后，最后的满足输出条件的记录总是优先输出。
--//至于为什么我不知道。
相关推荐
-
Oracle视图支持哪些操作 2024年10月22日 -
oracle事务的特性有哪些 2024年10月22日 -
oracle resource与性能的关系 2024年10月22日 - 避免Oracle drop database操作的常见误区 2024年10月21日
-
oracle如何防止sql注入 2024年10月15日 -
Oracle initrans和性能优化 2024年10月21日 -
Oracle catalog的作用是什么 2024年10月21日 -
oracle tnsping返回结果解读 2024年10月21日 - Oracle动态 SQL 执行效率如何 2024年10月21日
- oracle中nclob怎么转化成字符串 2024年10月21日
