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

2024-08-29 13:28:46

字體：大中小

來源：轉載

供稿：網友

最大的網站源碼資源下載站，

使用智能優化器提高oracle的性能極限
消耗在準備新的sql語句的時間是oracle sql語句執行時間的最重要的組成部分。但是通過理解oracle內部產生執行計劃的機制，你能夠控制oracle花費在評估連接順序的時間數量，并且能在大體上提高查詢性能。
準備執行sql語句
       當sql語句進入oracle的庫緩存后，在該語句準備執行之前，將執行下列步驟：

1)        語法檢查：檢查sql語句拼寫是否正確和詞序。

2)        語義分析：核實所有的與數據字典不一致的表和列的名字。

3)        輪廓存儲檢查：檢查數據字典，以確定該sql語句的輪廓是否已經存在。

4)        生成執行計劃：使用基于成本的優化規則和數據字典中的統計表來決定最佳執行計劃。

5)        建立二進制代碼：基于執行計劃，oracle生成二進制執行代碼。

一旦為執行準備好了sql語句，以后的執行將很快發生，因為oracle認可同一個sql語句，并且重用那些語句的執行。然而，對于生成特殊的sql語句，或嵌入了文字變量的sql語句的系統，sql執行計劃的生成時間就很重要了，并且前一個執行計劃通常不能夠被重用。對那些連接了很多表的查詢，oracle需要花費大量的時間來檢測連接這些表的適當順序。
評估表的連接順序
       在sql語句的準備過程中，花費最多的步驟是生成執行計劃，特別是處理有多個表連接的查詢。當oracle評估表的連接順序時，它必須考慮到表之間所有可能的連接。例如：六個表的之間連接有720（6的階乘，或6 * 5 * 4 * 3 * 2 * 1 = 720）種可能的連接線路。當一個查詢中含有超過10個表的連接時，排列的問題將變得更為顯著。對于15個表之間的連接，需要評估的可能查詢排列將超過1萬億（準確的數字是1,307,674,368,000）種。
使用optimizer_search_limit參數來設定限制
       通過使用optimizer_search_limit參數，你能夠指定被優化器用來評估的最大的連接組合數量。使用這個參數，我們將能夠防止優化器消耗不定數量的時間來評估所有可能的連接組合。如果在查詢中表的數目小于optimizer_search_limit的值，優化器將檢查所有可能的連接組合。

       例如：有五個表連接的查詢將有120（5! = 5 * 4 * 3 * 2 * 1 = 120）種可能的連接組合，因此如果optimizer_search_limit等于5（默認值），則優化器將評估所有的120種可能。optimizer_search_limit參數也控制著調用帶星號的連接提示的閥值。當查詢中的表的數目比optimizer_search_limit小時，帶星號的提示將被優先考慮。
另一個工具：參數optimizer_max_permutations
初始化參數optimizer_max_permutations定義了優化器所考慮組合數目的上限，且依賴于初始參數optimizer_search_limit。optimizer_max_permutations的默認值是80,000。

參數optimizer_search_limit和optimizer_max_permutations一起來確定優化器所考慮的組合數目的上限：除非（表或組合數目）超過參數optimizer_search_limit 或者 optimizer_max_permutations設定的值，否則優化器將生成所有可能的連接組合。一旦優化器停止評估表的連接組合，它將選擇成本最低的組合。
使用ordered提示指定連接順序
       你能夠設定優化器所執行的評估數目的上限。但是即使采用有很高價值的排列評估，我們仍然擁有使優化器可以盡早地放棄復雜的查詢的重要機會。回想一下含有15個連接查詢的例子，它將有超過1萬億種的連接組合。如果優化器在評估了80,000個組合后停止，那么它才僅僅評估了0.000006%的可能組合，而且或許還沒有為這個巨大的查詢找到最佳的連接順序。

       在oracle sql中解決此問題的最好的方法是手工指定表的連接順序。為了盡快創建最小的解決方案集，這里所遵循的規則是將表結合起來，通常優先使用限制最嚴格的where子句來連接表。

       下面的代碼是一個查詢執行計劃的例子，該例子在emp表的關聯查詢上強制執行了嵌套的循環連接。注意，我已經使用了ordered提示來直接最優化表的評估順序，最終它們表現在where子句上。

select /*+ ordered use_nl(bonus) parallel(e, 4) */

           e.ename,

hiredate,

b.comm.

from

           emp e,

           bonus b

where

           e.ename = b.ename

       這個例子要求優化器按順序連接在sql語句的from子句中指定的表，在from子句中的第一個表指定了驅動表。ordered提示通常被用來與其它的提示聯合起來來保證采用正確的順序連接多個表。它的用途更多的是在扭轉連接表數在四個以上的數據倉庫的查詢方面。

       另外一個例子，下面的查詢使用ordered提示按照指定的順序來連接表：emp、dept、sal，最后是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提示來手工指定表的連接順序，從而顯著地減少優化和執行新查詢所需的時間。

上一篇：Oracle數據庫的ORA-00257故障解決過程

下一篇：在linux8.0下，oracle9i的配置說明