不修改sql文本情況下，改變其執行計劃

不修改sql文本情況下，改變其執行計劃

可以使用sql profile和spm(sql plan manage)，本文主要介紹SPM方式

sql profile參考sql profile固定執行計劃

步驟

1.手動生成SQL Plan Baseline

DECLARE
   V_TEMP   NUMBER;
BEGIN
   V_TEMP :=
      DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID            => '原目标的sql_id',
                                             PLAN_HASH_VALUE   => 原目标sql的执行计划HASH值);
END;

2.改寫原SQL，加入hint，生成正確的執行計劃

-- SQL_ID和PLAN HASH VALUE可以在v$sql查找
-- SQL_HANDLE可以在DBA_DBA_SQL_PLAN_BASELINES中查找
DECLARE
   V_TEMP   NUMBER;
BEGIN
   V_TEMP :=
      DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
         SQL_ID            => '改写SQL的SQL_ID',
         PLAN_HASH_VALUE   => 改写SQL执行计划HASH值,
         SQL_HANDLE        => '第一步生成的HANDLE');
END;

3.刪除第一步生成的SQL Plan Baseline

DECLARE
   V_TEMP   NUMBER;
BEGIN
   V_TEMP :=
      DBMS_SPM.DROP_SQL_PLAN_BASELINE (
         SQL_HANDLE   => '原目标HANDLE',
         PLAN_NAME    => '原目标PLAN_NAME');
END;

實驗

1.創建測試環境

CREATE TABLE TEST_SPM AS SELECT * FROM DBA_OBJECTS;

CREATE INDEX IX_TEST_SPM_OBJECT_ID ON TEST_SPM(OBJECT_ID);

2.構造原SQL並查看執行計劃

SELECT /*+NO_INDEX(TEST_SPM IX_TEST_SPM_OBJECT_ID)*/* FROM TEST_SPM WHERE OBJECT_ID=10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

3.生成原SQL的SQL Plan Baseline

-- 生成
DECLARE
   V_TEMP   NUMBER;
BEGIN
   V_TEMP :=
      DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID            => 'cjdf02m61dsd9',
                                             PLAN_HASH_VALUE   => 1145642998);
END;
-- 查看，因為數據庫的cursor_sharing為force，所以值被替代為變量
SELECT SQL_HANDLE,PLAN_NAME,SUBSTR(SQL_TEXT,0,100) SQL_TEXT FROM DBA_SQL_PLAN_BASELINES 

4.修改sql並查看執行計劃

SELECT /*+INDEX(TEST_SPM IX_TEST_SPM_OBJECT_ID)*/* FROM TEST_SPM WHERE OBJECT_ID=10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

5.用修改後的SQL的SQL_ID，PLAN HASH VALUE和原SQL的SQL HANDLE生成新的SQL PLAN BASELINE

-- 生成
DECLARE
   V_TEMP   NUMBER;
BEGIN
   V_TEMP :=
      DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
         SQL_ID            => 'cju4dakqwvft7',
         PLAN_HASH_VALUE   => 3777949824,
         SQL_HANDLE        => 'SQL_9fa71106593b209e');
END;
-- 查看
SELECT SQL_HANDLE,PLAN_NAME,TO_CHAR(SUBSTR(SQL_TEXT,0,100)) SQL_TEXT FROM DBA_SQL_PLAN_BASELINES;

6.刪除原SQL的SQL PLAN BASELINE

DECLARE
   V_TEMP   NUMBER;
BEGIN
   V_TEMP :=
      DBMS_SPM.DROP_SQL_PLAN_BASELINE (
         SQL_HANDLE   => 'SQL_9fa71106593b209e',
         PLAN_NAME    => 'SQL_PLAN_9z9sj0tcmq84yeb1890ae');
END;

7.查看原SQL的執行計劃

SELECT /*+NO_INDEX(TEST_SPM IX_TEST_SPM_OBJECT_ID)*/* FROM TEST_SPM WHERE OBJECT_ID=10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());