用智能優化限制來提高Oracle性能
2024-08-29 13:41:45
供稿：網友
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為止。一旦優化器停止產生新的可能連接排列，它將會從中選擇出耗費最小的排列。
用已排序指示來指定一種連接排序
你可以設定優化器評估的排列數的上限。但是對復雜的情況下，即使答應的排列數很大，優化器也很可能在遠遠沒有找到一個比較合適的排列之間就已經停止優化了。你不妨回頭看看我前面舉的那個例子（15個需要連接的表有超過一萬億種排列）。假如設定優化器考慮80，000種排列，那么這僅僅考慮了所有可能性的0.000006％，優化器極可能沒有達到最佳的排列。
在Oracle SQL中解決這個問題的最好方法就是手工指定一種表格連接順序。這里需要遵循的大原則就是表格連接順序應該使得查詢計劃盡快得以建立，通常在SQL語句中使用WHERE限制子句。
下面以一個對名為emp的表格的并行查詢為例，例子中的代碼強制查詢計劃執行一個嵌套循環連接（nested loop join）。注重，我使用了已排序指示來引導優化器來評估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子句中第一個的表格指定為驅動表格（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開發者能改變表格的搜索限制參數或者利用已排序指示來手工指定表格的連接順序，這樣可以極大的降低優化以及執行新查詢所花費的時間。
