前言
最近接到個查詢任務,需要根據交易類型表(type_order)關聯交易表(main_order)進行統計202407-202506時間段的筆數。開發提供的SQL在備庫查詢了2個小時才有的結果,通過在主庫查看執行計劃兩張表全部為全表掃描,為了不影響主庫負載,于是把表導到壓測庫看看加個索引是不是能提供效率。但結果出乎意料…

示例操作:
- 表數據介紹:
SYS@twodb:1634> select count(*) from type_order;
COUNT(*)
----------
95
SYS@twodb:1634> select count(*) from main_order ;
COUNT(*)
----------
188936311
- 原SQL及執行計劃
SYS@twodb:1634> select count(*) from main_order t
2 join type_order d on t.txn_code = d.txn_code and t.txn_channel = d.txn_channel
3 where t.trans_time >= TO_TIMESTAMP('20240701', 'yyyymmdd')
4 and t.trans_time < TO_TIMESTAMP('20250701', 'yyyymmdd')
5 and t.txn_status = 1000
6 and d.prod_id in ('p1','p2','p3','p4','p5','p6','p7', 'p8','p9');
Elapsed: 00:02:07.13
Execution Plan
----------------------------------------------------------
Plan hash value: 1221874830
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 1320K (2)| 05:08:10 | | |
| 1 | SORT AGGREGATE | | 1 | 43 | | | | |
|* 2 | HASH JOIN | | 4499K| 184M| 1320K (2)| 05:08:10 | | |
|* 3 | TABLE ACCESS FULL | type_order | 9 | 171 | 3 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ITERATOR| | 145M| 3341M| 1319K (2)| 05:07:55 | 106 | 117 |
|* 5 | TABLE ACCESS FULL | main_order | 145M| 3341M| 1319K (2)| 05:07:55 | 106 | 117 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."txn_code"="D"."txn_code" AND "T"."txn_channel"="D"."txn_channel")
3 - filter("D"."prod_id"='p3' OR "D"."prod_id"='p4' OR
"D"."prod_id"='p6' OR "D"."prod_id"='p7' OR "D"."prod_id"='p2' OR
"D"."prod_id"='p5' OR "D"."prod_id"='p1' OR "D"."prod_id"='p8' OR
"D"."prod_id"='p9')
5 - filter("T"."txn_status"=1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3933934 consistent gets
3933857 physical reads
0 redo size
530 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
- 創建索引
SYS@twodb:1634> create index idx_order_1 on main_order (txn_code, txn_channel) nologging local;
Index created
- 創建索引后的執行計劃
SYS@twodb:1634> /
Elapsed: 00:10:45.61
Execution Plan
----------------------------------------------------------
Plan hash value: 2168199550
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 74806 (1)| 00:17:28 | | |
| 1 | SORT AGGREGATE | | 1 | 43 | | | | |
| 2 | NESTED LOOPS | | 4499K| 184M| 74806 (1)| 00:17:28 | | |
| 3 | NESTED LOOPS | | 4499K| 184M| 74806 (1)| 00:17:28 | | |
|* 4 | TABLE ACCESS FULL | type_order | 9 | 171 | 3 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR | | 266K| | 676 (1)| 00:00:10 | 106 | 117 |
|* 6 | INDEX RANGE SCAN | IDX_ORDER_1 | 266K| | 676 (1)| 00:00:10 | 106 | 117 |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| main_order | 499K| 11M| 21328 (1)| 00:04:59 | 1 | 1 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("D"."prod_id"='p3' OR "D"."prod_id"='p4' OR "D"."prod_id"='p6' OR
"D"."prod_id"='p7' OR "D"."prod_id"='p2' OR "D"."prod_id"='p5' OR
"D"."prod_id"='p1' OR "D"."prod_id"='p8' OR "D"."prod_id"='p9')
6 - access("T"."txn_code"="D"."txn_code" AND "T"."txn_channel"="D"."txn_channel")
7 - filter("T"."txn_status"=1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10146784 consistent gets
4662946 physical reads
8116 redo size
530 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
前后對比
- 全表掃描要比走索引效率高80%

HASH JOIN
-
HASH連接的算法:
1、Build 階段:讀取小表(Build Input)生成Hash表;
2、Probe 階段:讀取大表(Probe Input)探查Hash表并進行連接;
3、Build 操作在PGA中進行,不夠時使用臨時表空間;
4、被驅動表(被驅動表不需要讀入PGA中),對被驅動表的連接列也進行hash運算,然后到PGA中去探測Hash表進行連接; -
HINT用法:
SELECT /*+leading(t1) use_hash(t2)*/ *
FROM T1
INNER JOIN ON T1.ID = T2.ID;
- 注意
1、當表數據太大PGA放不下,會使用臨時表空間,從而影響性能。示例:Oracle HASH JOIN 引起的TEMP爆滿分析總結;
2、驅動順序:小的結果集先訪問,大的結果集后訪問,才能保證被驅動表的訪問次數降到最低,才能保障性能;
3、驅動表和被驅動表都只會訪問0次(索引能返回數據,就不需要再回表)或者1次;
4、HASH連接的驅動表與被驅動表的連接列都不需要創建索引;
5、HASH連接主要用于處理兩表等值關聯;
6、海量數據連接非??欤?/li>
NESTED LOOPS
- 嵌套循環的算法:
有驅動順序,驅動表返回多少條記錄,被驅動表就訪問多少次,嵌套循環連接中無須排序。 - HINT用法:t1為驅動表,t2為被驅動表
select /*+leading(t1) use_nl(t2)*/ *
from t1
join t2 on t1.id=t2.id;
- 注意:
1、非常依賴索引,被驅動表索引只能:INDEX UNIQUE SCAN、INDEX RANGE SCAN。不能:TABLE ACCESS FULL、INDEX FULL SCAN、INDEX SKIP SCAN、INDEX FAST FULL SCAN,否則消耗太高,容易跑不出數據。
2、DBLINK永遠不能作為NL的被驅動表。
3、小結果集做驅動表,大結果集做被驅動表,才能保障性能。
4、兩表使用外連接進行關聯,如果是NESTED LOOPS,那么無法更改驅動表,驅動表將會被固定在主表。
5、連接條件是instr、LIKE、substr、regexp_like只能走NESTED LOOPS
小結:
- 兩表關聯返回少量數據應該走嵌套循環,兩表關聯返回大量數據應該走HASH連接。
- 此示例:type_order表1條數據對應main_order表多條數據,關聯返回的結果集1.34億條數據,因此HASH連接相對效率要高。
- HASH看體積,NL看行數。HASH看體積,因為HASH是要全部放內存的。HASH因為單個進程最大2G,所以要看體積。
- HASH連接驅動表非常大優化方案:開并行,并行之后就不是一個進程在HASH。
查詢為什么產生redo?
在Oracle數據庫中,盡管SELECT語句通常是只讀操作,理論上不應對數據產生修改,但在特定場景下仍可能觸發redo日志的生成。這些情況主要源于Oracle內部機制對數據一致性、事務管理和存儲結構的維護需求。以下是SELECT產生redo的主要原因:
- 1、延遲塊清除(Delayed Block Cleanout)?
當事務提交后,如果修改的數據塊已被刷出內存(如因DBWR進程寫入磁盤),后續SELECT讀取這些塊時,Oracle需要清除塊上的事務信息(如ITL條目中的鎖標志和行頭鎖定位),此清理操作會生成redo日志?。這在處理大事務修改的塊時尤為常見。 - 2、維護讀一致性(Read Consistency)?
為實現查詢開始時刻的數據一致性視圖,SELECT可能訪問undo數據塊來重建舊版本數據。如果undo數據塊首次被加載到內存,此過程可能記錄undo表空間的變化產生redo?。 - 3、直接路徑讀取(Direct Path Reads)?
在處理大批量數據掃描(如全表掃描或排序操作)時,Oracle可能繞過buffer cache采用直接路徑讀取。若此過程需要分配臨時段(例如用于排序或哈希連接),臨時段的分配動作會生成redo日志?。 - 4、遞歸SQL操作(Recursive SQL)?
Oracle內部自動執行的維護操作(如數據字典統計信息更新或索引重建),可能在SELECT訪問對象時觸發后臺修改系統表,間接產生redo日志?。 - 5、Lost Write Detection機制?
為防止數據寫入丟失,Oracle在特定場景(如啟用自動檢測功能)下,SELECT讀取塊時會校驗塊完整性,若檢測到潛在丟失寫入風險,則生成redo日志以記錄校驗信息,導致大量redo產生?。 - 6、塊清理操作(Block Cleanout)?
對于延遲清理狀態的“臟塊”(如先前DML操作未完全提交的塊),SELECT讀取時需要完成清理事務標記(如設置commit SCN),該操作會修改塊頭部并生成redo?。
盡管這些情況在OLTP系統中相對少見,但高并發事務或大負載查詢仍可能顯著增加redo日志量。優化建議包括減少大事務提交頻率、避免不必要的全表掃描,或調整參數如_db_block_checking以減輕檢測機制的開銷?。
為什么INDEX RANGE SCAN + NESTED LOOPS 會產生REDO?未找到對應的情況,后續如果有思路再補充。
- ash報告截圖:沒有“Direct Path Reads”事件

總結&疑問
- 1、索引優化不是萬能的;
- 2、沒想明白為什么在備庫查需要2個小時,導出來放壓測庫只需要2分種?
- 3、此次的查詢為什么會產生redo,沒有搞明白,和總結的特征都不對癥!
希望大家有思路的話,可以留言溝通,成長于在不斷的學習。
引用
2019云和恩墨大講堂:8.1 Join原理與優化
Oracle執行select會產生redo嗎?
Oracle表連接優化思路-嵌套查詢/哈希連接/排序合并連接等
測試文檔下載
Oracle 表連接NESTED LOOPS、HASH JOIN、排序合并.pdf
歡迎贊賞支持或留言指正





