Oracle 並行查詢實戰：大表 Hash Join 從 19 分鐘優化到 20 秒

前言

面對百萬級大表 Hash Join 耗時 19 分鐘，透過並行查詢 (PARALLEL Hint) 結合多核 CPU，將執行時間壓縮至 **20 秒**，Consistent Gets 降低一個數量級。本文完整記錄優化過程與關鍵參數調整。

原始慢 SQL 與統計

SELECT COUNT(ui.usin_uid_fk)
FROM table1 av, table2 ui
WHERE av.av_usse_activatedate >= TO_DATE('20090102','YYYYMMDD')
  AND av.av_usse_activatedate <  TO_DATE('20090401','YYYYMMDD')
  AND av.av_usse_uid_fk = ui.usin_uid_fk
  AND ui.usin_mcnc_fk = 'XXX%';

• table1 (av) ~200 萬筆，table2 (ui) ~1,100 萬筆 (符合條件)。
• 執行計畫：**HASH JOIN**，全表掃描兩張大表。
• 耗時 **19 分 03 秒**，Consistent Gets **3,292 萬**，Physical Reads **35 萬**。

優化嘗試 1：並行度 4

SELECT /*+ PARALLEL(ui, 4) */ COUNT(ui.usin_uid_fk)
FROM table1 av, table2 ui
WHERE ... (同上)

• 耗時 **1 分 45 秒** (快 10 倍)。
• Consistent Gets **257 萬** (降 92%)，Physical Reads **12 萬**。

優化嘗試 2：並行度 8 (匹配 2×4 核 CPU = 8 核心)

SELECT /*+ PARALLEL(ui, 8) */ COUNT(ui.usin_uid_fk) ...

• 耗時 **20.6 秒** (再快 5 倍，總計快 55 倍)。
• Consistent Gets **260 萬**，Physical Reads **5.5 萬**。

優化嘗試 3：並行度 16 (超過核心數)

• 耗時 **20.6 秒** (無提升，略高)。
• 說明：並行度超過物理核心數反而增加協調開銷 (PX Coordinator、Granule 分配、合併排序)。

關鍵心得與最佳實務

1. 適用場景：大表全掃描、Hash Join、Sort Merge、資料倉庫、批次報表、建立索引、分區表操作。
2. 不適用場景：OLTP 小範圍查詢、高併發交易、索引範圍掃描為主 (並行索引掃描效果有限)。
3. 並行度設定：PARALLEL_DEGREE_POLICY=AUTO (11gR2+) 讓 Oracle 自動依物件大小與 CPU 核心數決定；手動指定建議 **等於 CPU 核心數** (非超執行緒數)。
4. 資源消耗：並行查詢佔用大量 PGA、臨時表空間、CPU，需監控 V$PX_SESSION, V$SQL_WORKAREA_ACTIVE 避免資源耗盡影響其他業務。
5. 參數微調：PARALLEL_MAX_SERVERS, PARALLEL_MIN_SERVERS, PGA_AGGREGATE_TARGET 需配合負載規劃。

驗證並行執行計畫

EXPLAIN PLAN FOR SELECT /*+ PARALLEL(ui, 8) */ ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 關鍵字：PX SEND, PX RECEIVE, PX BLOCK ITERATOR, HASH JOIN BUFFERED