用智能最佳化限制提高Oracle資料庫效能

Oracle SQL已耗用時間的最主要的組成部分是花在為執行準備新的SQL語句上的時間。不過，如果瞭解了可執行計畫產生的內在機制，你就可以控制Oracle花費在評估表的串連順序的時間，並在總體上提高查詢的效能。

準備為執行提供的SQL語句

在一個SQL語句進入Oracle庫的cache之後、而真正被執行之前，將會依次發生如下事件：

語法檢查——檢查該SQL語句的拼字和詞序是否正確。

語義解析——Oracle根據資料詞典（data dictionary）來驗證所有的表格（table）和列（column）。

已儲存綱要檢查——Oracle檢查詞典以確認對應該SQL語句是否已存在已儲存的綱要（Stored Outline）。

產生執行計畫——Oracle根據一種罰值（cost-based）最佳化演算法和資料詞典中的統計資料來決定如何產生最優執行計畫。

產生二進位代碼——Oracle在執行計畫的基礎上產生可執行檔二進位代碼。

一旦開始準備執行SQL語句，上述的過程很快就會執行，這是因為Oracle可以識別出同樣的SQL語句並對同樣的SQL語句重複使用對應的可執行代碼。然而，對產生ad hoc SQL的系統以及SQL中嵌入文本值（literal value）的情況，SQL執行計畫的產生時間就會變得相當長，而且以前的執行計畫也常常不能被再次利用。對那些牽涉到許多表格的查詢，Oracle可能要花上很長的時間來決定把串連這些表格的順序。

評估串連表格的順序

產生可執行計畫的時間往往是SQL的準備過程中最大的開銷組成部分，尤其是在處理有多個表的串連的查詢的情況下。當Oracle評估表的串連順序時，它必須考慮每一種可能的排序。例如，當有六個表格需要串連時，Oracle需要考慮720種（6的排列數，即6×5×4×3×2×1＝720）可能的串連排序。當需要串連的表的數量超過10時，這個排列問題將變得非常突出：如果需要串連的表格有15個，那麼需要考慮的可能的查詢排列順序超過一萬億種（精確值為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參數還用來控制啟動開始串連指示（star join hint）的閾值。當查詢所涉及的表格數量少於參數optimizer_search_limit的設定值，開始串連指示將被設定。

另一個工具：optimizer_max_permutations參數

optimizer_max_permutations初始參數用來設定最佳化器最佳化範圍的上界（即最多考慮多少種表格串連順序），它依賴於初始參數optimizer_search_limit。參數optimizer_max_permutations的預設值為8000。

參數optimizer_search_limit 和optimizer_max_permutations一同用來設定最佳化器所考慮的排列數的上限。最佳化器不斷的產生可能的表的串連的排列，直到排列數達到參數optimizer_search_limit或者optimizer_max_permutations為止。一旦最佳化器停止產生新的可能串連排列，它將會從中選擇出耗費最小的排列。