使用智能最佳化器提高Oracle的效能極限__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提示來手工指定表的串連順序，從而顯著地減少最佳化和執行新查詢所需的時間。