Oracle11g中SQL Plan Management （一）

 

對資料庫系統而言，SQL語句的效能是每一個開發和運維DBA格外關注的細節點。前端應用效能中有很大部分是依賴資料庫SQL，特別是Query的執行效率。所以，對於資料庫調優而言，很大的功夫就在於進行SQL的調優。

 

SQL語句的執行是依賴最佳化器生成的執行計劃。執行計劃SQL Plan是真正獲取到結果集合的步驟方法。為SQL生成一個更“好”的執行計劃，是所有DBMS最佳化器的重要職責。

 

1、Oracle中的CBO和統計量困局

 

Oracle最佳化器Optimizer經過RBO到CBO時代的轉化，應該說是向著智慧化和靈活化實現了飛躍。從簡單的規則序列生成執行計劃，轉而參考資料物件的統計資訊。CBO透過統計資訊來進行執行計劃生成，依據成本cost對比來確定最優的執行計劃。

 

伴隨週期性的統計量收集，CBO的工作似乎無懈可擊。但是，很多時候，即使在CBO時代，SQL的執行計劃還是存在效能問題。注意，我們這裡並不涉及CBO本身演算法的缺陷。

 

最直接的症狀，我們經常可以聽到這樣的抱怨：原本一條SQL或者SP一直或者大部分時間執行良好，但是“時不時”速度變慢。聯絡DBA進行Tuning後，常常是重新收集一次統計量（增加直方圖或者提高取樣率），SQL執行恢復正常。之後，在某一個時間點，問題照舊。

 

出現這種問題的根本原因在於：後設資料中統計量資訊同實際資料表的差異導致了錯誤執行計劃的生成。導致不一致的原因很多，比如統計量收集（自動）時間點不合適、大批次資料操作後沒有及時收集或者統計量異常。

 

簡單的說，就是CBO對於統計量的變化過於“敏感”，而統計量收集又是和資料表資料不能做到完全同步。

 

對於週期性收集的統計量資訊，SQL執行計劃的偶然性蛻變似乎是不能解決的困局。因為我們不能做到每次執行SQL前都收集統計量。所以，我們需要做的就是避免異常SQL執行計劃的生成，對執行計劃生成進行控制。

 

從一些文獻看，Oracle對於統計量收集頻度問題是比較矛盾的。一種推薦的做法是設定假設，認為在一個時間段內，正常資料表的資料列分佈是穩定的，不會有大的變化。在一次穩定的統計量收集之後，就將統計量固定“Freeze”住。這樣就不會有一場SQL執行計劃的生成。

 

另一種做法，是藉助SQL BaseLine，儲存一份“健康”時刻的關鍵SQL執行計劃，作為一個留存。當出現緊急的SQL效能問題時，現將儲存的執行計劃baseline替換上去，支援系統執行。之後再分析出現問題的原因。

 

SQL執行計劃異常的常見場景還包括系統升級。不同版本的Oracle最佳化器，特別是CBO的變化很大，一個在舊版本中工作良好的SQL可能新版本中生成一個很有問題的執行計劃。所以，一些系統的移植升級過程中，現有執行計劃的保留移植也是重要的考慮因素之一。

 

在11g中，Oracle開始嘗試解決這種問題。11g推出了專門針對執行計劃管理的元件內容SPM（SQL Plan Management）。在Baseline技術基礎上提供了執行計劃自動、手動最佳化進化的功能。

 

 

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE     11.2.0.1.0       Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

 

SQL> select parameter ,value from v$option where parameter like '%SQL%';

 

PARAMETER                      VALUE

------------------------------ ----------

SQL Plan Management            TRUE

 

 

2、SPM執行計劃“三部曲”

 

我們說，SQL執行計劃是和統計量緊密相關，偶然性的統計量錯誤或者不完全是造成執行計劃Degrade的本質原因。SPM（SQL Plan Mangement）期望實現的在執行執行時能夠保證SQL效能不會存在退化的情況，而且可以保證不斷的推進執行計劃效能。

 

簡單的說：SPM保證只有那些被接受“Accepted”的可信執行計劃才會被使用到，其他生成的執行計劃都需要進行確認驗證“Verified”。只有那些之後驗證效能最佳化好的執行計劃才會最終被接受為執行。

 

SPM主要包括三個關鍵元件，分別扮演不同的角色：

 

ü        SQL執行Baseline捕獲

 

對所有相關的SQL，都生成專門的SQL Baseline，儲存在資料庫中。注意，Baseline是佔用SYSAUX表空間的。

 

ü        SQL Baseline選擇

 

在系統的執行時，Oracle要保證每次執行SQL的執行計劃都是使用SQL Baseline中的確定執行計劃。同時，跟蹤所有該statement執行中使用的新執行計劃，作為Plan Histroy資訊儲存下來。

 

Plan Histroy是有不同的狀態的。執行計劃中包括的狀態有“Accepted”和“Unaccepted”兩種。非接受的狀態SQL可能是由於收集捕獲之後沒有進行驗證“Unvertified”或者被拒絕“Rejected”。

 

ü        SQL執行計劃Baseline進化

 

將Plan History中的未驗證“Unvertified”狀態的執行計劃進行判斷，判斷為“Accepted”或者“Rejected”。

 

從上面的介紹看，SPM本質上就是進行執行計劃不斷進化upgrade的工具元件。從Oracle 10g開始，自動化和智慧化就開始滲透到Oracle各個體系環節。自適應元件的頻頻推出，將過去一些難以確定、經驗密集型的問題轉為到Oracle自己解決。SPM就是Oracle在執行計劃管理上的一個推進階段。

 

下面，我們分別介紹三個元件的使用和功能。