Oracle如何在SQL不變情況下，替換目前SQL execution plan

 在 Oracle 資料庫中，如果無法或不想修改應用程式發送的原始 SQL 程式碼（例如 SQL 固化在第三方套件或舊系統中），想要強制改變其執行計畫（Execution Plan），主要有以下幾種正統做法 ：

方法一：使用 SQL Plan Baseline（DBMS_SPM）— 官方最推薦

這是 Oracle 11g 以上最標準、穩定的做法。原理是將「目標 SQL」與「帶有期望 Hint（如使用特定 Index）的 SQL」的執行計畫進行綁定 。

操作步驟：

1. 取得原始 SQL 與好 SQL 的 SQL_ID 與 PLAN_HASH_VALUE

   • 原始 SQL（壞計畫）：sql_id_a

   • 手動加上 Hint 改寫後的 SQL（好計畫）：sql_id_b，其 Plan Hash 為 plan_hash_b

2. 將好 SQL 的 Plan 載入為原始 SQL 的 Baseline

   執行以下 PL/SQL：

   SQL

   DECLARE
     l_plans_loaded PLS_INTEGER;
   BEGIN
     l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
       sql_id          => 'sql_id_b',       -- 好 SQL 的 ID
       plan_hash_value => plan_hash_b,      -- 好 SQL 的 Plan Hash
       sql_id          => 'sql_id_a'        -- 綁定給原始 SQL
     );
   END;
   /
   

   此時，當系統再次執行原始 SQL（sql_id_a）時，Oracle 優化器（CBO）就會強制採用指定的 Plan。

方法二：使用 SQL Profile 搭配 force_match（DBMS_SQLTUNE）

如果原始 SQL 中的字面值（Literal values）或變數名稱會動態變化（未統一使用 Bind Variable），使用 SQL Baseline 可能無法匹配 ，此時可以建立 SQL Profile 並開啟 force_match => TRUE 。

操作步驟：

使用 DBMS_SQLTUNE.IMPORT_SQL_PROF 將包含良好 Hint（例如 /*+ INDEX(...) */）的 Hint 集合直接注入到指定的 sql_id 中：

SQL

BEGIN
  DBMS_SQLTUNE.IMPORT_SQL_PROF(
    sql_text    => 'SELECT * FROM my_table WHERE col = :1', -- 原始 SQL 文字
    profile_xml => '...',                                   -- 或利用 Coe_xfr_sql_profile 指令碼產生
    name        => 'PROFILE_FIX_PLAN',
    force_match => TRUE                                     -- 忽略字面值差異強制套用
  );
END;
/

提示： 實務上 DBA 常配合 Oracle 官方提供的 coe_xfr_sql_profile.sql 腳本（來自 Oracle Support Note 271166.1），只要輸入壞 SQL 的 sql_id 與好 Plan 的 plan_hash_value，腳本會自動生成可替換 Plan 的 SQL Profile 檔案。

方法三：使用 SQL Patch（DBMS_SQLDIAG）

如果只想簡單地幫某條固定的 SQL「加上特定的 Hint」（例如強制走特定索引 /*+ INDEX(t my_idx) */ 或平行處理 /*+ PARALLEL(4) */），可以使用 SQL Patch，不需要手動建立新的 SQL。

操作步驟：

SQL

DECLARE
  l_patch_name VARCHAR2(30);
BEGIN
  l_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id    => 'sql_id_a',                         -- 目標 SQL_ID
    hint_text => 'INDEX(a my_index_name)',           -- 強制加入的 Hint
    name      => 'patch_force_index'
  );
END;
/

當原始 SQL 再次執行時，優化器會自動把 hint_text 注入進去並重新編譯出新的 Execution Plan。

方法四：使用 SQL Translation Framework（12c 以上）

如果需要進行真正的 SQL 語法替換（例如原 SQL 寫法極度不佳 ，需要將整個 WHERE 條件結構或 JOIN 方式改寫），可以使用 Oracle 12c 引入的 DBMS_SQL_TRANSLATOR。

操作步驟：

1. 建立 Translation Profile：

   SQL

   BEGIN
     DBMS_SQL_TRANSLATOR.CREATE_PROFILE('MY_SQL_TRANSLATOR');
   END;
   /
   

2. 註冊「原始 SQL」與「替換後 SQL」的對映關係：

   SQL

   BEGIN
     DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
       profile_name => 'MY_SQL_TRANSLATOR',
       sql_text     => 'SELECT * FROM orders WHERE UPPER(status) = ''NEW''', -- 原始 Bad SQL
       translated_text => 'SELECT * FROM orders WHERE status = ''NEW'''      -- 改寫後的 Good SQL
     );
   END;
   /
   

3. 在 Session 或系統層級啟用：

   SQL

   ALTER SESSION SET SQL_TRANSLATION_PROFILE = MY_SQL_TRANSLATOR;
   

各方法適用場景總結

解決方案	適用情境	優點	限制 / 注意事項
SQL Plan Baseline	SQL 語法固定（使用 Bind Variable）	官方標準機制，不會隨統計資訊變動而跑位	必須使用 Exact Match（字元與變數完全一致）
SQL Profile	SQL 帶有動態字面值（Literals）	支援 force_match => TRUE	需維護 Profile，極少數情況下 Optimizer 仍可能忽視
SQL Patch	僅需要為指定 SQL 追加 Hint	簡單快速，直接注入 Hint	只適用於加 Hint，無法改動 SQL 本身結構
SQL Translation	需要修改 SQL 本身語法結構	可做真正的 SQL 重寫替換	需設定 Session/System Profile，適用 12c