[20231013]为什么刷新缓存后输出记录顺序发生变化3.txt

来源：这里教程网时间：2026-03-03 19:00:26 作者：

[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操作后，最后的满足输出条件的记录总是优先输出。 --//至于为什么我不知道。

编辑推荐：

返回列表

[20231013]为什么刷新缓存后输出记录顺序发生变化3.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推