利用Oracle執(zhí)行計(jì)劃機(jī)制提高查詢性能的方法
Oracle執(zhí)行計(jì)劃機(jī)制對(duì)于提高查詢性能有很大的幫組，下文對(duì)利用Oracle執(zhí)行計(jì)劃機(jī)制提高查詢性能的方法作了詳細(xì)的介紹，供您參考。
消耗在準(zhǔn)備利用Oracle執(zhí)行計(jì)劃機(jī)制提高查詢性能新的SQL語句的時(shí)間是Oracle SQL語句執(zhí)行時(shí)間的最重要的組成部分。但是通過理解Oracle內(nèi)部產(chǎn)生Oracle執(zhí)行計(jì)劃的機(jī)制，你能夠控制Oracle花費(fèi)在評(píng)估連接順序的時(shí)間數(shù)量，并且能在大體上提高查詢性能。
準(zhǔn)備執(zhí)行SQL語句
當(dāng)SQL語句進(jìn)入Oracle的庫緩存后，在該語句準(zhǔn)備執(zhí)行之前，將執(zhí)行下列步驟：
1) 語法檢查：檢查SQL語句拼寫是否正確和詞序。
2) 語義分析：核實(shí)所有的與數(shù)據(jù)字典不一致的表和列的名字。
3) 輪廓存儲(chǔ)檢查：檢查數(shù)據(jù)字典，以確定該SQL語句的輪廓是否已經(jīng)存在。
4) 生成執(zhí)行計(jì)劃：使用基于成本的優(yōu)化規(guī)則和數(shù)據(jù)字典中的統(tǒng)計(jì)表來決定最佳執(zhí)行計(jì)劃。
5) 建立二進(jìn)制代碼：基于執(zhí)行計(jì)劃，Oracle生成二進(jìn)制執(zhí)行代碼。
一旦為執(zhí)行準(zhǔn)備好了SQL語句，以后的執(zhí)行將很快發(fā)生，因?yàn)镺racle認(rèn)可同一個(gè) SQL語句，并且重用那些語句的執(zhí)行。然而，對(duì)于生成特殊的SQL語句，或嵌入了文字變量的SQL語句的系統(tǒng)，SQL執(zhí)行計(jì)劃的生成時(shí)間就很重要了，并且前一個(gè)執(zhí)行計(jì)劃通常不能夠被重用。對(duì)那些連接了很多表的查詢，Oracle需要花費(fèi)大量的時(shí)間來檢測連接這些表的適當(dāng)順序。
評(píng)估表的連接順序
在SQL語句的準(zhǔn)備過程中，花費(fèi)最多的步驟是生成執(zhí)行計(jì)劃，特別是處理有多個(gè)表連接的查詢。當(dāng)Oracle評(píng)估表的連接順序時(shí)，它必須考慮到表之間所有可能的連接。例如：六個(gè)表的之間連接有720（6的階乘，或6 * 5 * 4 * 3 * 2 * 1 = 720）種可能的連接線路。當(dāng)一個(gè)查詢中含有超過10個(gè)表的連接時(shí)，排列的問題將變得更為顯著。對(duì)于15個(gè)表之間的連接，需要評(píng)估的可能查詢排列將超過1 萬億（準(zhǔn)確的數(shù)字是1,307,674,368,000）種。
使用optimizer_search_limit參數(shù)來設(shè)定限制
通過使用optimizer_search_limit參數(shù)，你能夠指定被優(yōu)化器用來評(píng)估的最大的連接組合數(shù)量。使用這個(gè)參數(shù)，我們將能夠防止優(yōu)化器消耗不定數(shù)量的時(shí)間來評(píng)估所有可能的連接組合。如果在查詢中表的數(shù)目小于 optimizer_search_limit的值，優(yōu)化器將檢查所有可能的連接組合。
例如：有五個(gè)表連接的查詢將有120（5! = 5 * 4 * 3 * 2 * 1 = 120）種可能的連接組合，因此如果optimizer_search_limit等于5（默認(rèn)值），則優(yōu)化器將評(píng)估所有的120種可能。 optimizer_search_limit參數(shù)也控制著調(diào)用帶星號(hào)的連接提示的閥值。當(dāng)查詢中的表的數(shù)目比 optimizer_search_limit小時(shí)，帶星號(hào)的提示將被優(yōu)先考慮。
另一個(gè)工具：參數(shù)optimizer_max_permutations
初始化參數(shù)optimizer_max_permutations定義了優(yōu)化器所考慮組合數(shù)目的上限，且依賴于初始參數(shù)optimizer_search_limit。optimizer_max_permutations的默認(rèn)值是80,000。
參數(shù)optimizer_search_limit和 optimizer_max_permutations一起來確定優(yōu)化器所考慮的組合數(shù)目的上限：除非（表或組合數(shù)目）超過參數(shù) optimizer_search_limit 或者 optimizer_max_permutations設(shè)定的值，否則優(yōu)化器將生成所有可能的連接組合。一旦優(yōu)化器停止評(píng)估表的連接組合，它將選擇成本最低的組合。
使用ordered提示指定連接順序
你能夠設(shè)定優(yōu)化器所執(zhí)行的評(píng)估數(shù)目的上限。但是即使采用有很高價(jià)值的排列評(píng)估，我們?nèi)匀粨碛惺箖?yōu)化器可以盡早地放棄復(fù)雜的查詢的重要機(jī)會(huì)?；叵胍幌潞?5個(gè)連接查詢的例子，它將有超過1萬億種的連接組合。如果優(yōu)化器在評(píng)估了 80,000個(gè)組合后停止，那么它才僅僅評(píng)估了0.000006%的可能組合，而且或許還沒有為這個(gè)巨大的查詢找到最佳的連接順序。
在Oracle SQL中解決此問題的最好的方法是手工指定表的連接順序。為了盡快創(chuàng)建最小的解決方案集，這里所遵循的規(guī)則是將表結(jié)合起來，通常優(yōu)先使用限制最嚴(yán)格的WHERE子句來連接表。
下面的代碼是一個(gè)查詢執(zhí)行計(jì)劃的例子，該例子在emp表的關(guān)聯(lián)查詢上強(qiáng)制執(zhí)行了嵌套的循環(huán)連接。注意，我已經(jīng)使用了ordered提示來直接最優(yōu)化表的評(píng)估順序，最終它們表現(xiàn)在WHERE子句上。
- select /*+ ordered use_nl(bonus) parallel(e, 4) */
- e.ename, hiredate, b.comm from emp e,
- bonus b where e.ename = b.ename
這個(gè)例子要求優(yōu)化器按順序連接在SQL語句的FROM子句中指定的表，在FROM子句中的第一個(gè)表指定了驅(qū)動(dòng)表。ordered提示通常被用來與其它的提示聯(lián)合起來來保證采用正確的順序連接多個(gè)表。它的用途更多的是在扭轉(zhuǎn)連接表數(shù)在四個(gè)以上的數(shù)據(jù)倉庫的查詢方面。
另外一個(gè)例子，下面的查詢使用ordered提示按照指定的順序來連接表：emp、dept、sal，最后是bonus。我通過指定emp到dept使用哈希連接和sal到bonus使用嵌套循環(huán)連接，來進(jìn)一步精煉執(zhí)行計(jì)劃。
- select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
- from emp, dept,sal,bonus where . . .
實(shí)踐建議
實(shí)際上，更有效率的做法是在產(chǎn)品環(huán)境中減小 optimizer_max_permutations參數(shù)的大小，并且總是使用穩(wěn)定的優(yōu)化計(jì)劃或存儲(chǔ)輪廓來防止出現(xiàn)耗時(shí)的含有大量連接的查詢。一旦找到最佳的連接順序，您就可以通過增加ordered提示到當(dāng)前的查詢中，并保存它的存儲(chǔ)輪廓，來為這些表手工指定連接順序，從而使其持久化。
當(dāng)你打算使用優(yōu)化器來穩(wěn)定計(jì)劃，則可以照下面的方法使執(zhí)行計(jì)劃持久化，臨時(shí)將 optimizer_search_limit設(shè)置為查詢中的表的數(shù)目，從而允許優(yōu)化器考慮所有可能的連接順序。然后，通過重新編排WHERE子句中表的名字，并使用ordered提示，與存儲(chǔ)輪廓一起使變更持久化，來調(diào)整查詢。在查詢中包含四個(gè)以上的表時(shí)，ordered提示和存儲(chǔ)輪廓將排除耗時(shí)的評(píng)估 SQL連接順序解析的任務(wù)，從而提高查詢的速度。
一旦檢測到最佳的連接順序，我們就可以使用ordered提示來重載 optimizer_search_limit和optimizer_max_permutations參數(shù)。ordered提示要求表按照它們出現(xiàn)在 FROM子句中的順序進(jìn)行連接，所以優(yōu)化器沒有加入描述。
作為一個(gè)Oracle專業(yè)人員，你應(yīng)該知道在SQL語句第一次進(jìn)入庫緩存時(shí)可能存在重大的啟動(dòng)延遲。但是聰明的Oracle DBA和開發(fā)人員能夠改變表的搜索限制參數(shù)或者使用ordered提示來手工指定表的連接順序，從而顯著地減少優(yōu)化和執(zhí)行新查詢所需的。
【編輯推薦】
