[20231020]為什麼重新整理快取後輸出記錄順序發生變化5.txt
[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2990586/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210316]為什麼重新整理快取後輸出記錄順序發生變化.txt快取
- [20231023]為什麼重新整理快取後輸出記錄順序發生變化6.txt快取
- nginx快取優先順序Nginx快取
- 美團二面：SpringBoot讀取配置優先順序順序是什麼？Spring Boot
- PHP 輸出快取PHP快取
- Laravel Passport 使用快取優化記錄LaravelPassport快取優化
- 什麼是Web快取，為什麼要使用它Web快取
- 為什麼要使用Redis做快取Redis快取
- 微服務化後快取怎麼做微服務快取
- 記憶體耗盡後Redis會發生什麼記憶體Redis
- 面試題。為什麼輸出BBBBB?面試題
- 扯什麼kafka順序消費，然後呢？古爾丹，代價是什麼Kafka
- 將字串中的每個單詞順序進行顛倒，單詞還是原來的單詞，字母順序不發生變化字串
- 系統快取全解析2：頁面輸出快取快取
- laravel 按照whereIn中給定陣列順序輸出Laravel陣列
- 剖析Disruptor:為什麼會這麼快？（二）神奇的快取行填充快取
- 快取穿透、快取雪崩和快取擊穿是什麼？快取穿透
- 什麼是redis快取雪崩、快取穿透、快取擊穿Redis快取穿透
- React元件：為什麼呼叫順序是constructor -> willMount -> render -> DidMountReact元件Struct
- ppt動畫出現順序怎麼設定 PPT設定動畫文字順序動畫
- 演算法題———————輸入棧的入棧順序和出棧順序判斷是否合理演算法
- “上下求索”的人工智慧市場了發生什麼變化？人工智慧
- 為什麼output type condition記錄已經維護,但輸出不能自動建立IDOC
- 快取和web快取分別是什麼?快取Web
- 為什麼處理有序陣列比無序陣列快？陣列
- JavaScript萬物產生順序JavaScript
- 為什麼刪除記錄表檔案不會減小？(記錄的插入與刪除在磁碟上的變化)
- Kafka 為什麼快Kafka
- redis為什麼快Redis
- C++輸出流cout的執行順序問題C++
- 讓 排序 按照 in 列表的的顯示順序排序輸出。排序
- 高速輸出-我們戲說快取快取
- asp.net輸出快取的使用ASP.NET快取
- 查詢列表連結串列加排序 每次重新整理順序都不同是什麼情況？排序
- 解決邏輯匯出後SEQUENCE的值發生變化的問題
- 當你在瀏覽器中輸入URL回車後會發生什麼？瀏覽器
- 面試官：快取穿透、快取雪崩和快取擊穿是什麼？面試快取穿透
- 類的成員變數的初始化順序變數