oracle資料庫調優描述（五）.txt

效能優化的第二部分是sql語句的優化。

輸入給oracle的程式碼，oracle並不會直接執行。
通常來說，oracle定期自動進行統計資訊的收集，10g以後的版本通常是每天晚上對資料變化量超過10%的表進行資料收集（避免統計資訊過舊造成的執行計劃不夠優化）。如果新建的表沒來得及收集統計資訊就執行了，則oracle採用動態取樣的方法，儘量保證統計較少的資料而得到較準確的統計結果。
執行一條語句時，首先用查詢優化器對語句進行修改，生成儘可能多的執行計劃。其目的是提高得到最優執行計劃的概率。最簡單的例子，比如將對檢視的操作修改為對基表的操作，將由union連線的表上的條件修改為對及表上的條件限制，將可以修改的巢狀操作改為非巢狀連線，對可以通過修改提高效能的物化檢視進行修改。

在9i及之前版本中普遍採用的RBO優化方式採用的就是一套固定的規則，有十六種可能的路徑。oracle認為全表掃描的效率最低，而通過rowid掃描效率最高。
10g後普遍採用的CBO優化方式下，oracle對各個執行計劃的成本（cost）進行比較，使用成本最低的執行計劃完成sql語句的執行。oracle對執行計劃的評估參考如下結果：待查詢的結果distinct值佔到該列所有distinct值的比例（selectivity）、待查詢的條件產生的結果集佔待查表的比例（cardinality）、完成此工作所需要花費的i/o、cpu、記憶體等資源的綜合成本（cost）。cost和執行路徑的選擇有很大關係，完成同樣操作可以既可以選擇全表掃描、也可以走索引，而索引並不一定是最好的方式。

前面提到為提高得到最優執行計劃的概率，oracle會產生儘可能多的計劃。這個工作主要是plan generation完成的。多表連線時，一般選擇資料量少的表作為驅動表，保證每步操作都產生及可能少的結果集（並行執行時選擇大表為驅動表）；多層巢狀的環境下，oracle從內到外生成子計劃；比較各個執行計劃的cost值時，如果發現當前待比較的計劃已經超過了已知最優計劃的cost，就不再繼續比較此計劃了。

最簡單的統計資訊包括了行數、範圍、最大值、最小值等資訊，如果資料嚴重傾斜，自動或手工指定收集柱狀圖資訊有助於生成最優執行計劃。對於資料分佈嚴重傾斜的列，統計資訊中包含了柱狀圖。重複執行包含對該列查詢的語句時，會根據查詢的值selectivity、柱狀圖等資訊來決定採用的執行計劃，該語句被標記為bind-sensitive cursors；由於查詢值的不同而產生了多個執行計劃，該語句還會被標記為bind-aware cursors，每次執行時根據新的變數值生成新的計劃；bind-aware cursors不斷生成新的計劃，則以前用過的相同執行計劃就需要被刪除，刪除後，與其相同的新計劃被標記為不可共享，稱為cursor merging。當使用繫結變數時，生成執行計劃過程中無法確定變數值，也就不可能知道列是否傾斜，無法使用柱狀圖。因此使用繫結變數有時會生成不夠優化的執行計劃。

執行計劃對於語句的效能影響巨大，出現問題時就需要首先檢視執行計劃是否符合預期。通過explain plan語句可以得到評估出來的執行計劃，再從plan_table輸出；而對語句進行自動跟蹤（autotrace），再提取跟蹤結果的方式檢視執行計劃則更加準確。

為了提高效能，應該根據需要使用索引、簇。
基於函式的索引是對某些列處理後的結果加索引，可以認為是在資料插入時生成的不可見的列，一般是在維護階段不能改變應用的情況下采用的，開發階段應該通過更好的設計來避免；
分割槽索引類似於分割槽表，分為全域性索引和本地索引，根據業務合理分割槽或指定hash分割槽；
索引組織表適合於存在父子關係的資料，按照索引列將所有資料排列起來，按照範圍查詢時可能更方便；
應用點陣圖索引一般在可選擇性較低的列上，且結果集較大的olap系統；
點陣圖連線索引bitmap join index是對多個表上的點陣圖索引；
域索引是以使用者自定義的索引型別來索引資料的，比較複雜。
表簇是由於共享相同的行並且通常同時出現在sql語句中的一系列表，建立表簇可以提高效能和空間使用率。
hash簇是將表的列分開儲存，一般將一起查詢的列放在一起。

hint可以直接指定使用執行計劃訪問資料的路徑、方法等資訊，而baselines則儲存這些資訊。
應該使用sql 計劃管理器來管理sql執行計劃。計劃管理器避免了系統環境、版本、優化引數的突然改變造成的執行計劃更改，可以對語句進行記錄、演化，為語句建立一系列執行良好的計劃，即sql plan baseline。對於索引刪除等情況，baseline則無能為力。本質上講，baseline是一系列的hint、plan hase value、plan-related information的結合。
用sql tuning advisor調優時，若發現當前的計劃比plan baseline中的某個計劃效能好，將自動將其加入到baseline中；可以手動修改baseline或顯示baseline內容，將baseline匯出並匯入到其他資料庫。sql plan baseline與語句日誌、計劃歷史資訊、sql profiles共同儲存在sql management base中，佔用sysaux的空間。可以設定該佔用範圍。

對oracle的調優包括定位top sql語句、檢視其是否可接受、為效能差的語句調優三方面。目標是減少使用者響應時間或減少完成相同工作消耗的資源。
定位top sql可以通過addm報告、自動sql調優、檢視v$sql檢視、執行awr報告、sql trace跟蹤等方式。
在開發時，應該關注執行計劃、重構效能差的語句、用hint控制訪問路徑和連線方式，刪除不必要的索引併合理使用，減少觸發器和約束的應用，重構資料，儘可能減少資料訪問次數。

[sql access advisor的相關內容還需要仔細研究]

在自動調優的同時oracle提供了hint來進行人工干預或效能比對測試。hints分為單表、多表、查詢塊、語句幾種，也可以分為優化目標類（all_rows）、開啟優化特性類（optimizer_features_enable（'11.1.0.6'））、訪問路徑類（FULL、INDEX）、連線順序類（leading）、連線操作型別類（use_nl、use_merge）、線上應用升級類（change_dupkey_error_index）、並行執行類（parallel、no_parallel）、查詢事務類（use_concat、rewrite）、其他（append、qb_name）。

使用plan stability可以避免環境變化對應用效能的影響。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-752331/，如需轉載，請註明出處，否則將追究法律責任。