熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

使用智能優化器提高Oracle的性能極限

2013-11-13 22:21:08  來源: Oracle 

  消耗在准備新的SQL語句的時間是Oracle SQL語句執行時間的最重要的組成部分但是通過理解Oracle內部產生執行計劃的機制你能夠控制Oracle花費在評估連接順序的時間數量並且能在大體上提高查詢性能
  
  准備執行SQL語句
   當SQL語句進入Oracle的庫緩存後在該語句准備執行之前將執行下列步驟
  
  ) 語法檢查檢查SQL語句拼寫是否正確和詞序
  
  ) 語義分析核實所有的與數據字典不一致的表和列的名字
  
  ) 輪廓存儲檢查檢查數據字典以確定該SQL語句的輪廓是否已經存在
  
  ) 生成執行計劃使用基於成本的優化規則和數據字典中的統計表來決定最佳執行計劃
  
  ) 建立二進制代碼基於執行計劃Oracle生成二進制執行代碼
  
  一旦為執行准備好了SQL語句以後的執行將很快發生因為Oracle認可同一個SQL語句並且重用那些語句的執行然而對於生成特殊的SQL語句或嵌入了文字變量的SQL語句的系統SQL執行計劃的生成時間就很重要了並且前一個執行計劃通常不能夠被重用對那些連接了很多表的查詢Oracle需要花費大量的時間來檢測連接這些表的適當順序
  
  評估表的連接順序
   在SQL語句的准備過程中花費最多的步驟是生成執行計劃特別是處理有多個表連接的查詢當Oracle評估表的連接順序時它必須考慮到表之間所有可能的連接例如六個表的之間連接有的階乘 * * * * * = )種可能的連接線路當一個查詢中含有超過個表的連接時排列的問題將變得更為顯著對於個表之間的連接需要評估的可能查詢排列將超過萬億(准確的數字是)種
  
  使用optimizer_search_limit參數來設定限制
   通過使用optimizer_search_limit參數你能夠指定被優化器用來評估的最大的連接組合數量使用這個參數我們將能夠防止優化器消耗不定數量的時間來評估所有可能的連接組合如果在查詢中表的數目小於optimizer_search_limit的值優化器將檢查所有可能的連接組合
  
   例如有五個表連接的查詢將有! = * * * * = )種可能的連接組合因此如果optimizer_search_limit等於(默認值)則優化器將評估所有的種可能optimizer_search_limit參數也控制著調用帶星號的連接提示的閥值當查詢中的表的數目比optimizer_search_limit小時帶星號的提示將被優先考慮
  
  另一個工具參數optimizer_max_permutations
  初始化參數optimizer_max_permutations定義了優化器所考慮組合數目的上限且依賴於初始參數optimizer_search_limitoptimizer_max_permutations的默認值是
  
  參數optimizer_search_limit和optimizer_max_permutations一起來確定優化器所考慮的組合數目的上限除非(表或組合數目)超過參數optimizer_search_limit 或者 optimizer_max_permutations設定的值否則優化器將生成所有可能的連接組合一旦優化器停止評估表的連接組合它將選擇成本最低的組合
  
  使用ordered提示指定連接順序
   你能夠設定優化器所執行的評估數目的上限但是即使采用有很高價值的排列評估我們仍然擁有使優化器可以盡早地放棄復雜的查詢的重要機會回想一下含有個連接查詢的例子它將有超過萬億種的連接組合如果優化器在評估了個組合後停止那麼它才僅僅評估了%的可能組合而且或許還沒有為這個巨大的查詢找到最佳的連接順序
  
   在Oracle SQL中解決此問題的最好的方法是手工指定表的連接順序為了盡快創建最小的解決方案集這裡所遵循的規則是將表結合起來通常優先使用限制最嚴格的WHERE子句來連接表
  
   下面的代碼是一個查詢執行計劃的例子該例子在emp表的關聯查詢上強制執行了嵌套的循環連接注意我已經使用了ordered提示來直接最優化表的評估順序最終它們表現在WHERE子句上
  
  select /*+ ordered use_nl(bonus) parallel(e ) */
  
   eename
  
  hiredate
  
  m
  
  from
  
   emp e
  
   bonus b
  
  where
  
   eename = bename
  
   這個例子要求優化器按順序連接在SQL語句的FROM子句中指定的表在FROM子句中的第一個表指定了驅動表ordered提示通常被用來與其它的提示聯合起來來保證采用正確的順序連接多個表它的用途更多的是在扭轉連接表數在四個以上的數據倉庫的查詢方面
  
   另外一個例子下面的查詢使用ordered提示按照指定的順序來連接表empdeptsal最後是bonus我通過指定emp到dept使用哈希連接和sal到bonus使用嵌套循環連接來進一步精煉執行計劃
  
  select /*+ ordered use_hash (emp dept) use_nl (sal bonus) */
  
  from
  
  emp
  
   dept
  
   sal
  
   bonus
  
  where
  
  實踐建議
   實際上更有效率的做法是在產品環境中減小optimizer_max_permutations參數的大小並且總是使用穩定的優化計劃或存儲輪廓來防止出現耗時的含有大量連接的查詢一旦找到最佳的連接順序您就可以通過增加ordered提示到當前的查詢中並保存它的存儲輪廓來為這些表手工指定連接順序從而使其持久化
  
   當你打算使用優化器來穩定計劃則可以照下面的方法使執行計劃持久化臨時將optimizer_search_limit設置為查詢中的表的數目從而允許優化器考慮所有可能的連接順序然後通過重新編排WHERE子句中表的名字並使用ordered提示與存儲輪廓一起使變更持久化來調整查詢在查詢中包含四個以上的表時ordered提示和存儲輪廓將排除耗時的評估SQL連接順序解析的任務從而提高查詢的速度
  
   一旦檢測到最佳的連接順序我們就可以使用ordered提示來重載optimizer_search_limit和optimizer_max_permutations參數ordered提示要求表按照它們出現在FROM子句中的順序進行連接所以優化器沒有加入描述
  
   作為一個Oracle專業人員你應該知道在SQL語句第一次進入庫緩存時可能存在重大的啟動延遲但是聰明的Oracle DBA和開發人員能夠改變表的搜索限制參數或者使用ordered提示來手工指定表的連接順序從而顯著地減少優化和執行新查詢所需的時間
  

From:http://tw.wingwit.com/Article/program/Oracle/201311/18848.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.