【DB筆試面試609】在Oracle中，SPM（SQL Plan Management，SQL計劃管理）是什麼？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，SPM（SQL Plan Management，SQL計劃管理）是什麼？

♣

答案部分

Outline的缺點是太過死板，當數據量大幅度變化時無法做出相應的改變。SQL Proifle的缺點是，當數據量變化時，STA會不可預知地去更改執行計劃，而SPM則會提供幾個完整的PLAN供選擇。SPM是一種隨Oracle 11g引入的新功能，通過維護所謂的「SQL計劃基準線（SQL Plan Baseline）」來使系統能夠自動控制SQL計劃演變。啟用此功能後，只要證明新生成的SQL計劃與SQL計劃基準線相集成不會導致性能回歸，就可以進行此項集成。因此，在執行某個SQL語句時，只能使用對應的SQL計劃基準線中包括的計劃。可以使用SQL優化集自動載入或植入SQL計劃基準線。SPM的主要優點是系統性能穩定，不會出現計劃回歸。此外，該功能還可以節省DBA的許多時間，這些時間通常花費在確定和分析SQL性能回歸以及尋找可用的解決方案上。在Oracle 11g中，Oracle提供DBMS_SPM包來管理SQL Plan，SPM是一種預防機制，它記錄並評估SQL的執行計劃，將已知的高效的SQL執行計劃建立為SQL計劃基準線。

在SQL計劃基準線捕獲階段，Oracle記錄SQL的執行計劃並檢測該執行計劃是否已經改變。如果SQL改變後的執行計劃是安全的，那麼SQL就使用新的執行計劃，因此，Oracle維護單個SQL執行計劃的歷史資訊，Oracle維護的SQL執行計劃的歷史僅僅針對重複執行的SQL，SQL計劃基準線可以手工LOAD，也可以設置為自動捕獲。

載入SQL計劃基準線的方式有兩種，下面分別介紹。

（1）即時捕獲，自動捕獲（Automatic Plan Capture）：

使用自動計劃捕獲，方法如下：設置初始化參數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES為TRUE（默認值為FALSE）。將該參數設置為TRUE將打開自動標識可重複SQL語句，以及自動為此類語句創建計劃歷史記錄的功能。如果要激活自動的SQL PLAN CAPTURE，那麼需要設置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES為TRUE（默認值為FALSE），然後自動捕獲SQL PLAN就會生效，系統會自動創建並維護SQL PLAN HISTORY，SQL PLAN HISTORY包括優化器關注的一些參數，例如：AN EXECUTION PLAN、SQL TEXT、OUTLINE、BIND VARIABLES、和COMPILATION ENVIRONMENT。

（2）成批載入（Manual Plan Loading）：

使用DBMS_SPM程式包可以載入SQL計劃基準線。該程式包支援手動管理SQL計劃基準線。使用此程式包，可以將SQL計劃從游標高速快取或現有的SQL優化集（SQL Tuning SET，STS）直接載入到SQL計劃基準線中。對於要從STS載入到SQL計劃基準線的SQL語句，需要將其SQL計劃存儲在STS中。使用DBMS_SPM可以將基準線計劃的狀態從已接受更改為未接受或者從未接受更改為已接受。

從SQL優化集中裝載的示例如下所示：

DECLARE    MY_PLANS PLS_INTEGER;  BEGIN    MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1');  END;  /  

從游標高速快取中裝載的示例如下所示：

DECLARE     MY_PLANS PLS_INTEGER;  BEGIN    MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7qqnad1j615m7');  END;  /  

在SQL計劃基準線演化階段，Oracle會按常規方式評估新計劃的性能，並將性能較好的計劃集成到SQL計劃基準線中。當優化程式為SQL語句找到新的計劃時，會將該計劃作為未接受的計劃添加到計劃歷史記錄中。然後，相對於SQL計劃基準線的性能，驗證該計劃的性能。如果經驗證某個未接受的計劃不會導致性能回歸（手動或自動），那麼該計劃會被更改為已接受計劃，並集成到SQL計劃基準線中。

演化SQL計劃基準線的方式有兩種：

（1）使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE函數。該函數將返回一個報表，顯示是否已將一些現有的歷史記錄計劃移到了計劃基準線中。也可以在歷史記錄中指定要測試的特定計劃。

（2）運行SQL優化指導：通過使用SQL優化指導手動或自動優化SQL語句，演化SQL計劃基準線。當SQL優化指導發現已優化的計劃，並確認其性能優於從相應的SQL計劃基準線中選擇的計劃的性能時，就會生成一個建議案以接受SQL概要文件。

在SQL計劃基準線的演變階段，Oracle評估新的計劃的性能並將性能較好的計劃存放在SQL計劃基準線中，可以使用DBMS_SPM包的EVOLVE_SQL_PLAN_BASELINE過程用戶將新的SQL PLAN存入已經存在的SQL計劃基準線中，新的PLAN將會作為已經ACCEPT PLAN加入到SQL計劃基準線中。程式碼如下所示：

SET SERVEROUTPUT ON  SET LONG 10000  DECLARE     REPORT CLOB;  BEGIN     REPORT := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle => 'SYS_SQL_593bc74fca8e6738');     DBMS_OUTPUT.PUT_LINE(report);  END;  /  

SPM主要有下面幾個相關的數據字典視圖：

SELECT * FROM DBA_SQL_PLAN_BASELINES;  SELECT * FROM DBA_SQLSET_PLANS;  SELECT * FROM DBA_ADVISOR_SQLPLANS;  

如果將計劃添加到計劃歷史記錄中，那麼該計劃將與一些重要的屬性關聯：

（1）SIGNATURE、SQL_HANDLE、SQL_TEXT和PLAN_NAME是搜索操作的重要標識符。

（2）使用ORIGIN可以確定計劃是自動捕獲的（AUTO-CAPTURE）、手動演化的（MANUALLOAD）、通過SQL優化指導自動演化的（MANUAL-SQLTUNE）還是通過自動SQL優化自動演化的（AUTO-SQLTUNE）。

（3）ENABLED和ACCEPTED：ENABLED屬性表示計劃已啟用，可供優化程式使用。如果未設置ENABLED，那麼系統將不考慮此計劃。ACCEPTED屬性表示用戶在將計劃更改為ACCEPTED時計劃已經過驗證為有效計劃（系統自動進行的或用戶手動進行的）。如果將某個計劃更改為ACCEPTED，那麼僅當使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE()更改其狀態時，該計劃才是非ACCEPTED的。可以通過刪除ENABLED設置暫時禁用ACCEPTED計劃。計劃必須為ENABLED和ACCEPTED，優化程式才會考慮使用它。

（4）FIXED表示優化程式僅考慮標記為FIXED的計劃，而不考慮其它計劃。例如，如果有10個基準線計劃，其中的三個計劃被標記為FIXED，那麼優化程式將僅使用這三個計劃中的最佳計劃，而忽略其它所有計劃。如果某個SQL計劃基準線至少包含一個已啟用的已修復計劃，則該SQL計劃基準線就是FIXED的。如果在修復的SQL計劃基準線中添加了新計劃，則在手動將這些新計劃聲明為FIXED之前，無法使用這些新計劃。

可以使用DBA_SQL_PLAN_BASELINES視圖查看每個計劃的屬性。然後，可以使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE函數更改其中的某些屬性，可以使用DBMS_SPM.DROP_SQL_PLAN_BASELINE函數刪除計劃或整個計劃歷史記錄。

註：DBA_SQL_PLAN_BASELINES視圖包含了一些附加屬性；使用這些屬性可以確定各個計劃的上次使用時間，以及是否應自動清除某個計劃。

如果使用的是自動計劃捕獲，那麼第一次將某個SQL語句標識為可重複時，其最佳成本計劃將被添加到對應的SQL計劃基準線中。然後，該計劃將用於執行相應的語句。

如果某個SQL語句存在計劃基準線，並且初始化參OPTIMIZER_USE_SQL_PLAN_BASELINES被設置為TRUE（默認為TRUE），那麼優化程式將使用比較計劃選擇策略。每次編譯SQL語句時，優化程式都會先使用傳統的基於成本的搜索方法建立一個最佳成本計劃，然後嘗試在SQL計劃基準線中找到一個匹配的計劃。如果找到了匹配的計劃，則優化程式將照常繼續運行。如果未找到匹配的計劃，那麼優化程式會先將新計劃添加到計劃歷史記錄中，然後計算SQL計劃基準線中各個已接受的計劃的成本，並選擇成本最低的那個計劃。使用隨各個已接受的計劃存儲的大綱複製這些已接受的計劃。因此，對於SQL語句來說，擁有一個SQL計劃基準線的好處就是：優化程式始終選擇該SQL計劃基準線中的一個已接受的計劃。

通過SQL計劃管理，優化程式可以生成最佳成本計劃，也可以生成基準線計劃。此資訊將被轉儲在有關解釋計劃的PLAN_TABLE的OTHER_XML列中。此外，還可以使用新的DBMS_XPLAIN.DISPLAY_SQL_PLAN_BASELINE函數，顯示某個計劃基準線中給定SQL_HANDLE的一個或多個執行計劃。如果還指定了PLAN_NAME，那麼將顯示相應的執行計劃。

註：為了保留向後兼容性，如果用戶會話的某個SQL語句的存儲大綱對是活動的，那麼將使用此存儲大綱編譯該語句。此外，即使為會話啟用了自動計劃捕獲，也不將優化程式使用存儲大綱生成的計劃存儲在SMB中。

雖然存儲大綱沒有任何顯式遷移過程，但可使用DBMS_SPM程式包中的LOAD_PLAN_FROM_CURSOR_CACHE過程或LOAD_PLAN_FROM_SQLSET過程將其遷移到SQL計劃基準線。遷移完成時，應禁用或刪除原始的存儲大綱。

在SQL Plan選擇階段，SQL每一次編繹，優化器使用基於成本的方式，建立一下best-cost的執行計劃，然後去匹配SQL Plan Baselines中的SQL Plan，如果找到了匹配的SQL Plan，則會使用這個執行計劃，如果沒有找到匹配的SQL Plan,優化器就會去SQL Plan History中去搜索成本最低的SQL Plan，如果優化器在SQL Plan History中找不到任務匹配的SQL Plan，那麼該SQL Plan被作為一個Non-Accept Plan被存入SQL Plan History，新的SQL Plan直到它被驗證不會引起一下性能問題才會被使用。

DROP_SQL_PLAN_BASELINE函數可以從BASELINES中DROP某個執行的執行計劃，如果不執行PLAN NAME，那麼會DROP所有的PLAN，即DROP了BASELINE。

刪除某個SQL的BASELINE：

SET SERVEROUTPUT ON  DECLARE   l_plans_dropped  PLS_INTEGER;  BEGIN   l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (     sql_handle => 'SQL_7b76323ad90440b9',     plan_name  => NULL);     DBMS_OUTPUT.put_line(l_plans_dropped);  END;  /  

刪除所有baseline：

declare    v_plan_num PLS_INTEGER;  begin      for cur in (SELECT * FROM dba_sql_plan_baselines) loop        begin        v_plan_num := dbms_spm.drop_sql_plan_baseline(sql_handle => cur.sql_handle);      exception        when others then          null;      end;      end loop;    end;  /  

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。