用智能優化限制來提高Oracle性能

　　Oracle SQL運行時間的最主要的組成部分是花在為執行准備新的SQL語句上的時間不過如果了解了可執行計劃產生的內在機制你就可以控制Oracle花費在評估表的連接順序的時間並在總體上提高查詢的性能
　　
　　准備為執行提供的SQL語句
　　
　　
　　在一個SQL語句進入Oracle庫的cache之後而真正被執行之前將會依次發生如下事件
　　
　　語法檢查——檢查該SQL語句的拼寫和詞序是否正確
　　語義解析——Oracle根據數據詞典（data dictionary）來驗證所有的表格（table）和列（column）
　　已保存綱要檢查——Oracle檢查詞典以確認對應該SQL語句是否已存在已保存的綱要（Stored Outline）
　　產生執行計劃——Oracle根據一種罰值（costbased）優化算法和數據詞典中的統計數據來決定如何生成最優執行計劃
　　產生二進制代碼——Oracle在執行計劃的基礎上生成可執行的二進制代碼
　　一旦開始准備執行SQL語句上述的過程很快就會執行這是因為Oracle可以識別出同樣的SQL語句並對同樣的SQL語句重復使用對應的可執行代碼然而對產生ad hoc SQL的系統以及SQL中嵌入文本值（literal value）的情況SQL執行計劃的生成時間就會變得相當長而且以前的執行計劃也常常不能被再次利用對那些牽涉到許多表格的查詢Oracle可能要花上很長的時間來決定把連接這些表格的順序
　　
　　評估連接表格的順序
　　生成可執行計劃的時間往往是SQL的准備過程中最大的開銷組成部分尤其是在處理有多個表的連接的查詢的情況下當Oracle評估表的連接順序時它必須考慮每一種可能的排序例如當有六個表格需要連接時Oracle需要考慮種（的排列數即×××××＝）可能的連接排序當需要連接的表的數量超過時這個排列問題將變得非常突出如果需要連接的表格有個那麼需要考慮的可能的查詢排列順序超過一萬億種（精確值為）
　　
　　在optimizer_search_limit參數中設置限制
　　你可以通過optimizer_search_limit參數來控制上述問題的發生該參數用來指定優化器評估的表格連接順序的最大數目利用這個參數就可以防止優化器在評估所有可能的表格連接順序中所花費的多余時間如果查詢中的表的數量少於或者等於optimizer_search_limit那麼優化器檢查所有的可能表的連接方式
　　
　　例如涉及了五個表的查詢一共有種(！＝××××＝)可能的連接順序所以如果參數optimizer_search_limit的值設置為（默認值）那麼優化器就會考慮所有的這種可能的連接順序optimizer_search_limit參數還用來控制啟動開始連接指示（star join hint）的阈值當查詢所涉及的表格數量少於參數optimizer_search_limit的設定值開始連接指示將被設置
　　另一個工具optimizer_max_permutations參數
　　optimizer_max_permutations初始參數用來設定優化器優化范圍的上界（即最多考慮多少種表格連接順序）它依賴於初始參數optimizer_search_limit參數optimizer_max_permutations的默認值為
　　
　　
　　
　　參數optimizer_search_limit 和optimizer_max_permutations一同用來設置優化器所考慮的排列數的上限優化器不斷的產生可能的表的連接的排列直到排列數達到參數optimizer_search_limit或者optimizer_max_permutations為止一旦優化器停止產生新的可能連接排列它將會從中選擇出耗費最小的排列
　　
　　用已排序指示來指定一種連接排序
　　你可以設定優化器評估的排列數的上限但是對復雜的情況下即使允許的排列數很大優化器也很可能在遠遠沒有找到一個比較合適的排列之間就已經停止優化了你不妨回頭看看我前面舉的那個例子（個需要連接的表有超過一萬億種排列）如果設定優化器考慮種排列那麼這僅僅考慮了所有可能性的％優化器極可能沒有達到最佳的排列
　　
　　在Oracle SQL中解決這個問題的最好方法就是手工指定一種表格連接順序這裡需要遵循的大原則就是表格連接順序應該使得查詢計劃盡快得以建立通常在SQL語句中使用WHERE限制子句
　　
　　下面以一個對名為emp的表格的並行查詢為例例子中的代碼強制查詢計劃執行一個嵌套循環連接（nested loop join）注意我使用了已排序指示來引導優化器來評估WHERE子句中給出的表格的連接順序
　　
　　select /*+ ordered use_nl(bonus) parallel(e ) */
　　eename
　　hiredate
　　m
　　from
　　emp e
　　bonus b
　　where
　　eename = bename
　　;
　　
　　上面的例子要求優化器按照SQL語句中FROM子句指定的順序連接表格FROM子句中第一個的表格指定為驅動表格（driving table）已排序指示常常與其它指示聯合使用以確保多個表格按照適當的順序連接起來在遇到涉及四個以上表格的數據倉庫查詢時常常也是這樣處理
　　
　　下面另給出一個例子在這個例子中我們使用一個已排序指示（ordered hint）來把表格按照一個特定的順序（先是emp然後是dep和sal最後是bonus）連接起來進一步改進執行計劃我指定emp表格到dept表格的連接使用hash連接sal表格到bonus表格使用嵌套循環連接
　　
　　select /*+ ordered use_hash (emp dept) use_nl (sal bonus) */
　　from
　　emp
　　dept
　　sal
　　bonus
　　where
　　
　　對實際應用的建議
　　在實際應用場合下減小optimizer_max_permutations參數並使用已保存的優化計劃或者已保存綱要（這樣在查詢涉及到許多表格時就可以避免重新解析查詢所花費的實際）會更有效率一旦找到最好的表格連接順序你可以手工指定表格的連接順序（通過已排序指示）並保存綱要這樣就永久保存該表格連接順序
　　
　　
　　
　　當執行一個新的查詢時你可以首先把optimizer_search_limit設置為該查詢所涉及的表格數這樣優化器將從所有的連接順序中找出最佳的那種以後執行該查詢時你就可以在WHERE子句中按照最佳連接順序排列表格名稱並設置已保存指示和已保存綱要這樣就可以按照最佳順序連接表格而無需重復評估各種可能排序這樣查詢的速度將會得到顯著的提高
　　
　　已排序指示的優先級高於optimizer_search_limit和 optimizer_max_permutations參數如果設置了已排序指示那麼表格就會按照查詢命令中的FROM子句給出的順序連接這樣這個過程就沒有優化器優化表格的連接順序這一步驟了
　　
　　作為Oracle的專業人士你應該知道SQL語句進入庫cache中有一個明顯的起始延時但是聰明的Oracle數據庫管理員以及Oracle開發者能改變表格的搜索限制參數或者利用已排序指示來手工指定表格的連接順序這樣可以極大的降低優化以及執行新查詢所花費的時間

From:http://tw.wingwit.com/Article/program/Oracle/201311/17427.html