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

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

[20231020]为什么刷新缓存后输出记录顺序发生变化5.txt --//前几天做了单表刷新缓存后输出记录顺序发生变化的情况，今天测试2个表的情况。 --//我遇到一个奇怪的现象,做一个记录,我无法使用10046跟踪. 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.建立测试例子： create table t1 pctfree 99 as select level id, lpad(level, 3500, 'T1') vc from dual connect by level <= 500 order by dbms_random.random; create table t2 pctfree 99 as select level id, lpad(level, 3500, 'T2') vc from dual connect by level <= 500 order by dbms_random.random; --//建立的表T1,T2非常特殊1块1条记录,这样便于后面的分析。 create index i_t1_id on t1(id) ; create index i_t2_id on t2(id) ; exec dbms_stats.gather_table_stats(null, 'T1', cascade=>true); exec dbms_stats.gather_table_stats(null, 'T2', cascade=>true); 3.测试: --//sqlplus 的版本18c. TTT@127.0.0.1:1521/orcl> show sqlpluscompatibility sqlpluscompatibility 18.0.0 TTT@127.0.0.1:1521/orcl> alter system flush buffer_cache; System altered. TTT@127.0.0.1:1521/orcl> SELECT /*+ leading (t1 t2) use_nl(t2) */ t1.id,substr(t1.vc,3496,5) vc1,substr(t2.vc,3496,5) vc2 FROM t1 , t2 WHERE t1.id = t2.id AND t1.id between 1 and 10; ID VC1 VC2 --- ---------- ---------- 1 1T1T1 2T2T1 2 1T1T2 2T2T2 10 1T110 2T210 9 1T1T9 2T2T9 3 1T1T3 2T2T3 4 1T1T4 2T2T4 5 1T1T5 2T2T5 6 1T1T6 2T2T6 7 1T1T7 2T2T7 8 1T1T8 2T2T8 10 rows selected. --//这次又与单表扫描不同id=10,9出现在前面.id=3,4出现在后面. --//执行计划如下: Plan hash value: 2852340061 ------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 33 (100)| | 10 |00:00:00.01 | 32 | | 1 | NESTED LOOPS | | 1 | 9 | 63090 | 33 (0)| 00:00:01 | 10 |00:00:00.01 | 32 | | 2 | NESTED LOOPS | | 1 | 10 | 63090 | 33 (0)| 00:00:01 | 10 |00:00:00.01 | 22 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 10 | 35050 | 13 (0)| 00:00:01 | 10 |00:00:00.01 | 13 | |* 4 | INDEX RANGE SCAN | I_T1_ID | 1 | 10 | | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | |* 5 | INDEX RANGE SCAN | I_T2_ID | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 9 | | 6 | TABLE ACCESS BY INDEX ROWID | T2 | 10 | 1 | 3505 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 10 | ------------------------------------------------------------------------------------------------------------------------------------------- TTT@192.168.2.7:1521/orcl> @ oid 423778,423776,423779,423777 owner object_name object_type SUBOBJECT_NAME CREATED LAST_DDL_TIME status DATA_OBJECT_ID OBJECT_ID ----- ----------- ----------- -------------- ------------------- ------------------- --------- -------------- ---------- TTT T1 TABLE 2023-10-16 11:05:05 2023-10-16 11:05:17 VALID 423776 423776 TTT T2 TABLE 2023-10-16 11:05:09 2023-10-16 11:05:22 VALID 423777 423777 TTT I_T1_ID INDEX 2023-10-16 11:05:17 2023-10-16 11:05:17 VALID 423778 423778 TTT I_T2_ID INDEX 2023-10-16 11:05:22 2023-10-16 11:05:22 VALID 423779 423779 TTT@127.0.0.1:1521/orcl> select /*+ index(t1) */ id,substr(vc,3498,3),rowid,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from t1 where id between 1 and 10; ID SUBSTR ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ---------- ------ ------------------ ------------------------------------ 1 1T1 AABndgAAMAAC9GjAAA 774563 2 1T2 AABndgAAMAAC+6cAAA 781980 3 1T3 AABndgAAMAAC9ElAAA 774437 4 1T4 AABndgAAMAAC9EKAAA 774410 5 1T5 AABndgAAMAAC9CHAAA 774279 6 1T6 AABndgAAMAAC9H+AAA 774654 7 1T7 AABndgAAMAAC+6EAAA 781956 8 1T8 AABndgAAMAAC9DPAAA 774351 9 1T9 AABndgAAMAAC9GVAAA 774549 10 110 AABndgAAMAAC9GiAAA 774562 10 rows selected. TTT@127.0.0.1:1521/orcl> select /*+ index(t2) */ id,substr(vc,3498,3),rowid,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from t2 where id between 1 and 10; ID SUBSTR ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ---------- ------ ------------------ ------------------------------------ 1 2T1 AABndhAAMAAC9RMAAA 775244 2 2T2 AABndhAAMAAC9PjAAA 775139 3 2T3 AABndhAAMAAC9RUAAA 775252 4 2T4 AABndhAAMAAC9QoAAA 775208 5 2T5 AABndhAAMAAC9OOAAA 775054 6 2T6 AABndhAAMAAC9MDAAA 774915 7 2T7 AABndhAAMAAC9M4AAA 774968 8 2T8 AABndhAAMAAC9MNAAA 774925 9 2T9 AABndhAAMAAC9PWAAA 775126 10 210 AABndhAAMAAC9NUAAA 774996 10 rows selected. 4.继续: --//但是当我打开10046跟踪时候发现,输出顺序变了.我反复测试多次,结果都一样. TTT@127.0.0.1:1521/orcl> alter system flush buffer_cache; System altered. TTT@127.0.0.1:1521/orcl> alter session set events '10046 trace name context forever, level 12'; Session altered. TTT@127.0.0.1:1521/orcl> SELECT /*+ leading (t1 t2) use_nl(t2) */ t1.id,substr(t1.vc,3496,5) vc1,substr(t2.vc,3496,5) vc2 FROM t1 , t2 WHERE t1.id = t2.id AND t1.id between 1 and 10; ID VC1 VC2 ---------- ---------- ---------- 1 1T1T1 2T2T1 2 1T1T2 2T2T2 3 1T1T3 2T2T3 10 1T110 2T210 4 1T1T4 2T2T4 5 1T1T5 2T2T5 6 1T1T6 2T2T6 7 1T1T7 2T2T7 8 1T1T8 2T2T8 9 1T1T9 2T2T9 10 rows selected. --//仅仅id=10记录在前.我反复多次结果都是一样. TTT@127.0.0.1:1521/orcl> alter session set events '10046 trace name context off'; Session altered. $ egrep "db file|FETCH" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_3512.trc WAIT #140372804078232: nam='db file sequential read' ela= 17576 file#=41 block#=774867 blocks=1 obj#=423778 tim=11988387762053 --//I_T1_ID 的root WAIT #140372804078232: nam='db file sequential read' ela= 737 file#=41 block#=774868 blocks=1 obj#=423778 tim=11988387762973 --//I_T1_ID 的leaf WAIT #140372804078232: nam='db file sequential read' ela= 7799 file#=41 block#=774563 blocks=1 obj#=423776 tim=11988387770948 --//T1 id=1 WAIT #140372804078232: nam='db file sequential read' ela= 554 file#=41 block#=774875 blocks=1 obj#=423779 tim=11988387771727 --//I_T2_ID 的root WAIT #140372804078232: nam='db file sequential read' ela= 372 file#=41 block#=774876 blocks=1 obj#=423779 tim=11988387772246 --//I_T2_ID 的leaf WAIT #140372804078232: nam='db file sequential read' ela= 13610 file#=41 block#=775244 blocks=1 obj#=423777 tim=11988387785993 --//T2 id=1 FETCH #140372804078232:c=3623,e=41865,p=6,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=2852340061,tim=11988387786137 WAIT #140372804078232: nam='db file sequential read' ela= 13947 file#=41 block#=781980 blocks=1 obj#=423776 tim=11988387801056 --//T1 id=2 WAIT #140372804078232: nam='db file sequential read' ela= 7307 file#=41 block#=775139 blocks=1 obj#=423777 tim=11988387808529 --//T2 id=2 WAIT #140372804078232: nam='db file sequential read' ela= 11484 file#=41 block#=774437 blocks=1 obj#=423776 tim=11988387820204 --//T1 id=3 WAIT #140372804078232: nam='db file sequential read' ela= 646 file#=41 block#=775252 blocks=1 obj#=423777 tim=11988387821049 --//T2 id=3 WAIT #140372804078232: nam='db file parallel read' ela= 30816 files=1 blocks=7 requests=7 obj#=423776 tim=11988387852962 --//T1 读取id=4..10 WAIT #140372804078232: nam='db file sequential read' ela= 6846 file#=41 block#=774996 blocks=1 obj#=423777 tim=11988387860062 --//T2 id=10 WAIT #140372804078232: nam='db file sequential read' ela= 314 file#=41 block#=775208 blocks=1 obj#=423777 tim=11988387860618 --//T2 id=4 WAIT #140372804078232: nam='db file sequential read' ela= 254 file#=41 block#=775054 blocks=1 obj#=423777 tim=11988387861046 --//T2 id=5 WAIT #140372804078232: nam='db file sequential read' ela= 260 file#=41 block#=774915 blocks=1 obj#=423777 tim=11988387861469 --//T2 id=6 WAIT #140372804078232: nam='db file sequential read' ela= 260 file#=41 block#=774968 blocks=1 obj#=423777 tim=11988387861902 --//T2 id=7 WAIT #140372804078232: nam='db file sequential read' ela= 311 file#=41 block#=774925 blocks=1 obj#=423777 tim=11988387862396 --//T2 id=8 WAIT #140372804078232: nam='db file sequential read' ela= 218 file#=41 block#=775126 blocks=1 obj#=423777 tim=11988387862765 --//T2 id=9 FETCH #140372804078232:c=4384,e=75795,p=18,cr=26,cu=0,mis=0,r=9,dep=0,og=1,plh=2852340061,tim=11988387862837

编辑推荐：

返回列表

[20231020]为什么刷新缓存后输出记录顺序发生变化5.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推