使用SQL tuning advisor(STA)自動最佳化SQL

Oracle 10g之後的最佳化器支援兩種模式，一個是normal模式，一個是tuning模式。在大多數情況下，最佳化器處於normal模式。基於CBO的normal模式只考慮很小部分的執行計畫集合用於選擇哪個執行計畫，因為它需要在儘可能短的時間，通常是幾秒或毫秒級來對當前的SQL語句進行解析並產生執行計畫。因此並不能保證SQL語句每次都是使用最佳的執行計畫。而tuning模式則將高負載的SQL語句直接扔給最佳化器，最佳化器來自動對其進行詳細的分析，調試並給出建議，這就是Oracle 提供的Automatic Tuning Optimizer，即自動調整最佳化器。Oracle 自動調整最佳化器通過SQL調優建議器(SQL tuning advisor)來體現。 

1、SQL tuning的基本步驟
a、鑒別需要調整的高負載SQL或者Top SQL
b、尋找可改進的執行計畫
c、實施能夠改進的執行計畫以提高SQL效率

2、如何tuning SQL
a、檢查是否為最佳化器設定了合理的參數(optimizer_mode,optimizer_index_caching,optimizer_index_cost_adj,以及相關cache size)
b、檢查SQL語句所涉及的對象是否存在過時的統計資訊或者傾斜列是否缺少長條圖等
c、通過添加提示來引導SQL語句使用正確的訪問路徑，以及串連方式等
d、重構等價的SQL語句以使得SQL更高效(如最小化基表及中間結果集，避免列運算，列上的函數，null值，不等運算使得索引失效)
e、添加合理的索引或物化視圖以及移除冗餘索引,分散I/O等

3、Automatic Tuning Optimizer 做什麼?
a、分析統計資訊
最佳化器執行計畫產生期間記錄當前SQL語句涉及對象的統計資訊的類型以及哪些被使用或哪些是需要的
當統計資訊記錄完成後自動調整最佳化器會比對與查詢相關的這些對象的統計資訊是否可用或過時或非均衡列缺少長條圖等
針對上述的操作之後得到哪些對象沒有統計資訊以及哪些對象缺少統計資訊以及額外的統計資訊用於產生report
b、分析訪問路徑
最佳化器會分析當前SQL所使用的訪問路徑是否合理，也就是分析基於表的訪問方式，如全表掃描，索引掃描等
自動調整最佳化器會基於謂詞嘗試假設性的推斷來建立合理的索引，也就是建議通過添加或修改相應的索引來提高效能
c、SQL結構分析
最佳化器會建議對於一些具有較大影響的SQL語句作結構性調整及轉換(基於內部規則)，如未嵌套的子查詢，重寫物化視圖，視圖合并等
基於文法以及語義結構的分析與調整，如謂詞列上的運算，UNION與UNION ALL的使用，NOT IN, NOT EXIST之間替換等
對中間結果集以及串連方式等實現一些預估的分析
d、SQL profiling
SQL profiling 內建於最佳化器，就是一個剖析工具，基於上述得到的資訊對當前的SQL進行剖析，以檢查出導致效能糟糕的故障點
所有上述分析得到的結果以及輔助資訊最後以sql profile的形式表現出來，供使用者來判斷是否接受
當使用者接受這些profile，下次處於normal模式時，相同的sql語句會使用這個profile
可以對profile進行啟用，停用，以及修改，因此即使表發生較大的變化，profile依舊能使得SQL受益

 

4、Automatic Tuning Optimizer與SQL tuning advisor結構圖

1 2 3 下一頁