超長 SQL 調優實戰：從綁定執行計畫到 UNION ALL 拆解、並行 DML 三步走

半夜被叫起優化一條 250 行、含 137 個 UNION ALL 的超長 INSERT ... SELECT。開發反映「以前快，現在慢」，且從未單獨測試 SELECT 效能。本文記錄現場急救與後續深度調優三階段策略。

現場狀況

• SQL ID: 0ah5a8dbk28fh，Plan Hash: 2746060288
• 執行計畫：INSERT → LOAD TABLE CONVENTIONAL → UNION ALL → 137 個分支，每分支含多層 HASH JOIN OUTER、NESTED LOOPS
• 成本：258 萬，預估 4+ 小時
• AWR 顯示同一 SQL 有**三個計畫**，成本分別為 202K、558K、2580K，目前跑的是最差者
• 無並行、無 Hint、DML 回滾風險極大

第一階段：現場止血（綁定最優計畫）

-- 1. 確認最優計畫的 PHV（Plan Hash Value）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('0ah5a8dbk28fh', NULL, NULL, 'ADVANCED'));

-- 2. 使用 Oracle 官方腳本綁定 SQL Profile
@?/rdbms/admin/coe_xfr_sql_profile.sql
-- 依提示輸入 SQL_ID、選擇最優 PHV（如 202K 那個）、生成並執行腳本

⚠ 決策依據： 非現場、無維護窗口、DML 回滾成本高 → **不 Kill、不硬解析、直接綁定已驗證的好計畫**。領採納，暫緩 Kill。

第二階段：根因分析（下週深度優化）

1. 統計資料：檢查涉及表/索引 LAST_ANALYZED、NUM_ROWS、HISTOGRAM，確認是否過期導致誤判選擇率
2. Bind Variable Peeking / Adaptive Cursor Sharing：三個計畫差異極大，極可能是綁定窺探導致首次解析鎖定劣質計畫，後續不共用
3. UNION ALL 分支獨立調優：將 137 分支拆解，逐一 EXPLAIN PLAN，找出高成本分支共性（如缺索引、型別隱性轉換、笛卡爾積）

第三階段：重構與並行化

1. WITH 子查詢因子化

WITH
base AS (SELECT ... FROM core_table WHERE ...),
agg1 AS (SELECT ... FROM base GROUP BY ...),
agg2 AS (SELECT ... FROM base GROUP BY ...)
SELECT ... FROM agg1 UNION ALL SELECT ... FROM agg2 ...;

將重複掃描提升為共用 CTE，減少 I/O。

2. 分支並行化

ALTER SESSION ENABLE PARALLEL DML;  -- Session 級開啟 PDML
INSERT /*+ PARALLEL(8) */ INTO target_table
SELECT ... FROM ... UNION ALL ...;

⚠ 必要條件： 目標表啟用 PARALLEL 屬性、無觸發器、無外鍵、Enterprise Edition。

3. 索引與分區

• 針對高成本 JOIN 欄位建立覆蓋索引
• 大表考慮範圍分區，配合分區裁剪