消耗在准備新的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_limit
optimizer_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
) */
e
ename
hiredate
m
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提示來手工指定表的連接順序
從而顯著地減少優化和執行新查詢所需的時間
From:http://tw.wingwit.com/Article/program/Oracle/201311/18848.html
