前言：為何 Hash Join 是大表關聯的王者？
Hash Join 以 CPU (雜湊計算) 與記憶體 (建立 Hash Table) 為代價，換取接近大表全表掃描的極致效能。其高效源於兩點：O(1) 雜湊查找避免遍歷、記憶體存取速度遠超磁碟。適用場景：大表 (Probe) 與小表 (Build/Hash Cluster) 關聯。本文結合 10g 追蹤事件 (10104) 實測，深入剖析內部運作與調優關鍵。
效能對比實證：三種 Join 模式
| Join 模式 | Cost | 耗時 | 備註 |
|---|
| HASH JOIN (預設) | 221 | 00:00:03 | 小表 5,000 行建 Hash Cluster，大表 100 萬行 Probe |
| MERGE JOIN | 3,507 | 00:00:43 | 需雙邊排序，Temp 空間 31MB |
| NESTED LOOPS | 828K | 02:45:41 | 無索引時極慢；建唯一索引後降至 106K，仍為 Hash 千倍 |
結論：大數據量等值關聯，Hash Join 碾壓其他模式。
10g 記憶體管理新範式：PGA 自動管理與 Large Pool
- 9i 及以前：
HASH_AREA_SIZE 手動控制 Hash Join 記憶體。
- 10g 建議：啟用
PGA_AGGREGATE_TARGET + WORKAREA_SIZE_POLICY=AUTO 自動管理。
- MTS/Shared Server 環境：UGA 在 Large Pool，受
SGA_TARGET 影響，彈性分配。
Hash Join 三種執行模式
- Optimal：記憶體完全容納 Build 表，全內存完成，效能最佳。
- One-Pass：記憶體不足，部分分區溢寫臨時表空間，單次 I/O。
- Multi-Pass：記憶體嚴重不足，多輪溢寫/讀取，效能急劇下降。
10104 事件追蹤實測：分區、桶與 Kept 旗標
測試環境：大表 100 萬行，小表分別 50/500/5,000 行。關鍵觀察指標：
關鍵發現
- 預設分區數 = 8 (Hash-join fanout)，與記憶體是否足夠無關，10g 行為異於舊版文檔「記憶體不足才分區」的說法。
- Kept=1 表駐留記憶體，0 表溢寫磁碟。所有測試中 Kept 均為 1，確認 Optimal 模式。
- 桶數量動態擴展：50 行 → 128 桶，500 行 → 1,024 桶，5,000 行 → 8,192 桶。桶數約為
0.8 * HASH_AREA_SIZE / (MULTIBLOCK_IO * DB_BLOCK_SIZE) 但 10g 已優化調整。
- 桶內行數極少：最大桶深度 50 行時僅 2 行，500 行時 4 行，5,000 行時 5 行。桶越淺 = 遍歷開銷越小 = 效能越好。
- Non-empty bucket 平均行數：1.19 → 1.24 → 1.33，隨資料量緩慢增長，仍極低。
調優核心建議
- 硬體紅利下，記憶體不再是首要瓶頸：現代伺服器 64GB+ 記憶體常見，Optimal 模式易達成。
- 聚焦分區與桶分配均勻度：透過 10104 追蹤觀察
Partition Distribution 與 Hash table statistics，關注 Kept 旗標、桶深度分佈。
- 避免資料傾斜導致熱點分區/桶：關聯鍵分佈不均會造成特定分區膨脹，觸發 One-Pass/Multi-Pass。
- 統計資訊準確性：CBO 依賴統計資訊估算 Build 表大小決定記憶體授予，
DBMS_STATS 定期收集至關重要。
