Oracle Hash Join 10104 事件追蹤實戰：數據分佈對性能的決定性影響

Oracle Hash Join 10104 事件追蹤實戰

通過設置 10104 trace name context forever, level 2 事件，可輸出 Hash Join 構建階段的桶分佈統計，直觀揭示數據傾斜對 Join 性能的影響。本文對比兩組實驗：低重復鍵 vs 高重復鍵，量化分析桶分佈差異。

1. 實驗環境準備

-- 建表並收集統計
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
insert into t2 select * from dba_objects;
commit;
exec dbms_stats.gather_table_stats(user,'t1');
exec dbms_stats.gather_table_stats(user,'t2');

2. 實驗一：低重復鍵

alter session set tracefile_identifier = 'gg_20150618';
alter session set events '10104 trace name context forever, level 2';
select count(1) from t1,t2 where t1.object_id = t2.object_id;
alter session set events '10104 trace name context off';

關鍵統計：

• Total buckets: 131072
• Empty buckets: 75222 (57.4%)
• Non-empty buckets: 55850
• Total rows: 73076
• Max rows in a bucket: 7
• Avg rows in non-empty buckets: 1.31

結論：數據均勻分佈，桶衝突極少，Hash Join 效率極高。

3. 實驗二：高重復鍵

alter session set tracefile_identifier = 'gg_20150619';
alter session set events '10104 trace name context forever, level 2';
select count(1) from t1,t2 where t1.object_type = t2.object_type;
alter session set events '10104 trace name context off';

關鍵統計：

• Total buckets: 131072
• Empty buckets: 131027 (99.97%)
• Non-empty buckets: 僅 45
• Total rows: 73080
• Max rows in a bucket: 27,803
• Avg rows in non-empty buckets: 1,624

結論：極嚴重的數據傾斜，絕大多數行集中在極少數桶，導致探針階段大量比較，性能急劇下降。

4. 核心指標解讀

5. 針對高傾斜的優化策略

1. 收集直方圖統計：METHOD_OPT=>'FOR COLUMNS SIZE 254 object_type'，讓優化器識別傾斜並選擇 Broadcast/Nested Loop。
2. 手動 Hint：/*+ LEADING(t1) USE_HASH(t2) */ 或 /*+ NO_SWAP_JOIN_INPUTS(t2) */ 控制構建/探針端。
3. 增加 PGA：調大 hash_area_size 或 pga_aggregate_target，減少分區溢寫磁盤。
4. 數據清洗/歸一化：將高基數枚舉值拆分、標準化，降低重復率。
5. 並行執行：PARALLEL Hint 分散構建負載。

6. 實戰檢查清單

• ✅ 開啟 10104 事件觀察桶分佈
• ✅ 關注 Max/Avg rows per bucket
• ✅ 結合 SQL Monitor 確認 Join 耗時佔比
• ✅ 優先優化 Max rows in a bucket 最大的 Join