[SQL Tune] 新工具(SPM)- 避免 SQL Execution Plan 迷路

很多Oracle的使用者都曾經經歷過以下痛苦經驗:

• Database升級完之後，某些SQL突然變的慢得不行.
• Table加了Partition後，本來跑3秒的SQL變成30分鍾也跑不完.

諸如此類不勝枚舉，大致和系統改變有關. Oracle optimizer是Oracle得以強過其他關連式資料庫的利器，但也因為它太過強大, 太過聰明，偶而會秀逗. 當然SQL的Execution plan跑掉不見得是壞事，因為大部分可能變得比較好，例如，資料內容有大幅變動後反映在Statistics上時，這時Execution Plan當然要跟著改變. 這種改變可能是好的改變. 但是如果不是，那就不好了，正式環境的SQL那裡會允許SQL的效能一下子掉得天差地遠. 所以針對Optimizer對Execution Plan的改變，當然只能接受變好不能變差.

在11G之前, 管理 Execution plan的方式是用 stored outline或 SQL profile. 但是這兩個工具相對對DBA而言, 比較需要手動的介入. 相對而言, SPM是smart得多了.

Oracle瞭解大家的痛苦，在11g 出現了一個新功能叫做：SQL Plan Management (SPM)，SPM允許使用者針對指定SQL維持一個穩定的效能. 有了SPM後，SQL變成 'Managed SQL' . 所謂'Managed SQL'就是說SPM會針對'Managed SQL'去偵測Execution Plan的改變，為了這個目的，SPM會維護所有'Managed SQL'的Execution Plan歷史紀錄. 這時又有一個 'SPM aware optimizer'負責存取，使用和管理SQL Management Base (SMB)的資訊.

而SMB是負責儲存一組被接受的Plan，而何謂可接受(Accepted)當然要透過SPM去判斷，確定效能沒有問題才能加入SMB. 這樣大概可以瞭解，SPM就是透過將特定SQL的所有Execution Plan儲存起來後，在執行階段去判斷哪一個Plan才是效能最好的. 這時若有一組新的Plan產生，就不會被 'SPM aware optimizer'所考慮因為他還沒有機會進入SMB當中.

下面這個圖說明3個SQL的Plan歷史如何被儲存和被SPM使用.

再來就是要維護一個正確的SMB，有以下幾個方式. 基本上，Oracle在SPM啟動後，它會自動偵測重複性(Repeated)的SQL，將其Execution plan記錄下來，並決定接受與否. 至於非重複性也就是Ad-hoc的SQL就不會被紀錄.

• 自動偵測： 將參數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES設為True，此時所有SQL的Plan歷史會記錄Optimizer執行時的相關資訊，例如SQL本身，SQL compile時的環境等. 記錄的資訊未來就可以用來重新產生Execution Plan. 所有該SQL執行產生Plan的歷史紀錄都會被紀錄下來，如果效能被認可，就會被視為可接受.
• 手動偵測：如果有使用SQL Tuning Set (STS)，則SPM也可以手動更新SMB使用以下功能 ： dbms_spm.load_plans_from_sqlset 去指定特定的SQL給SMB.
• 手動偵測：將Cursor cache手動更新SMB，使用以下功能:dbms_spm.load_plans_from_cursor_cache

在SMB建立之後, 每一個SQL在執行之後, 當Optimizer產生一個新的 Execution plan後, 就會和SMB裡面的 Execution plan作比較, 如果有一樣的就直接採用. 如果沒有, 則在SMB裡找一個Cost最低的Execution plan來執行. 而針對未被使用的Execution plan則會被放置到Un-accepted區域. 一直到未來也許環境改變後, 該Execution plan有機會被選取.

至於Execution plan被記錄下來後, 如何管理這裡被記錄且驗證過的Execution plan. Oracle 提供了package DBMS_SPM和 view dba_sql_plan_baseline 供DBA管理之用.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optplanmgmt.htm