[20231020]为什么刷新缓存后输出记录顺序发生变化5.txt
--//前几天做了单表刷新缓存后输出记录顺序发生变化的情况，今天测试2个表的情况。
--//我遇到一个奇怪的现象,做一个记录,我无法使用10046跟踪.
1.环境：
[email protected]:1521/orcl> @ ver1
[email protected]: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.
[email protected]:1521/orcl> show sqlpluscompatibility
sqlpluscompatibility 18.0.0
[email protected]:1521/orcl> alter system flush buffer_cache;
System altered.
[email protected]: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 |
-------------------------------------------------------------------------------------------------------------------------------------------
[email protected]: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
[email protected]: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.
[email protected]: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跟踪时候发现,输出顺序变了.我反复测试多次,结果都一样.
[email protected]:1521/orcl> alter system flush buffer_cache;
System altered.
[email protected]:1521/orcl> alter session set events '10046 trace name context forever, level 12';
Session altered.
[email protected]: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记录在前.我反复多次结果都是一样.
[email protected]: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