用智能優(yōu)化限制提高Oracle數(shù)據(jù)庫(kù)性能

2024-08-29 13:50:43

字體：大中小

供稿：網(wǎng)友

Oracle SQL運(yùn)行時(shí)間的最主要的組成部分是花在為執(zhí)行預(yù)備新的SQL語(yǔ)句上的時(shí)間。不過(guò)，假如了解了可執(zhí)行計(jì)劃產(chǎn)生的內(nèi)在機(jī)制，你就可以控制Oracle花費(fèi)在評(píng)估表的連接順序的時(shí)間，并在總體上提高查詢的性能。預(yù)備為執(zhí)行提供的SQL語(yǔ)句在一個(gè)SQL語(yǔ)句進(jìn)入Oracle庫(kù)的cache之后、而真正被執(zhí)行之前，將會(huì)依次發(fā)生如下事件：語(yǔ)法檢查——檢查該SQL語(yǔ)句的拼寫(xiě)和詞序是否正確。語(yǔ)義解析——Oracle根據(jù)數(shù)據(jù)詞典（data dictionary）來(lái)驗(yàn)證所有的表格（table）和列（column）。已保存綱要檢查——Oracle檢查詞典以確認(rèn)對(duì)應(yīng)該SQL語(yǔ)句是否已存在已保存的綱要（Stored Outline）。產(chǎn)生執(zhí)行計(jì)劃——Oracle根據(jù)一種罰值（cost-based）優(yōu)化算法和數(shù)據(jù)詞典中的統(tǒng)計(jì)數(shù)據(jù)來(lái)決定如何生成最優(yōu)執(zhí)行計(jì)劃。產(chǎn)生二進(jìn)制代碼——Oracle在執(zhí)行計(jì)劃的基礎(chǔ)上生成可執(zhí)行的二進(jìn)制代碼。一旦開(kāi)始預(yù)備執(zhí)行SQL語(yǔ)句，上述的過(guò)程很快就會(huì)執(zhí)行，這是因?yàn)镺racle可以識(shí)別出同樣的SQL語(yǔ)句并對(duì)同樣的SQL語(yǔ)句重復(fù)使用對(duì)應(yīng)的可執(zhí)行代碼。然而，對(duì)產(chǎn)生ad hoc SQL的系統(tǒng)以及SQL中嵌入文本值（literal value）的情況，SQL執(zhí)行計(jì)劃的生成時(shí)間就會(huì)變得相當(dāng)長(zhǎng)，而且以前的執(zhí)行計(jì)劃也經(jīng)常不能被再次利用。對(duì)那些牽涉到許多表格的查詢，Oracle可能要花上很長(zhǎng)的時(shí)間來(lái)決定把連接這些表格的順序。評(píng)估連接表格的順序生成可執(zhí)行計(jì)劃的時(shí)間往往是SQL的預(yù)備過(guò)程中最大的開(kāi)銷(xiāo)組成部分，尤其是在處理有多個(gè)表的連接的查詢的情況下。當(dāng)Oracle評(píng)估表的連接順序時(shí)，它必須考慮每一種可能的排序。例如，當(dāng)有六個(gè)表格需要連接時(shí)，Oracle需要考慮720種（6的排列數(shù)，即6×5×4×3×2×1＝720）可能的連接排序。當(dāng)需要連接的表的數(shù)量超過(guò)10時(shí)，這個(gè)排列問(wèn)題將變得非常突出：假如需要連接的表格有15個(gè)，那么需要考慮的可能的查詢排列順序超過(guò)一萬(wàn)億種（精確值為1,307,674,368,000）。在optimizer_search_limit參數(shù)中設(shè)置限制你可以通過(guò)optimizer_search_limit參數(shù)來(lái)控制上述問(wèn)題的發(fā)生，該參數(shù)用來(lái)指定優(yōu)化器評(píng)估的表格連接順序的最大數(shù)目。利用這個(gè)參數(shù)，就可以防止優(yōu)化器在評(píng)估所有可能的表格連接順序中所花費(fèi)的多余時(shí)間。假如查詢中的表的數(shù)量少于或者等于optimizer_search_limit，那么優(yōu)化器檢查所有的可能表的連接方式。例如，涉及了五個(gè)表的查詢一共有120種(5！＝5×4×3×2×1＝120)可能的連接順序，所以假如參數(shù)optimizer_search_limit的值設(shè)置為5（默認(rèn)值），那么優(yōu)化器就會(huì)考慮所有的這120種可能的連接順序。optimizer_search_limit參數(shù)還用來(lái)控制啟動(dòng)開(kāi)始連接指示（star join hint）的閾值。當(dāng)查詢所涉及的表格數(shù)量少于參數(shù)optimizer_search_limit的設(shè)定值，開(kāi)始連接指示將被設(shè)置。另一個(gè)工具：optimizer_max_permutations參數(shù) optimizer_max_permutations初始參數(shù)用來(lái)設(shè)定優(yōu)化器優(yōu)化范圍的上界（即最多考慮多少種表格連接順序），它依靠于初始參數(shù)optimizer_search_limit。參數(shù)optimizer_max_permutations的默認(rèn)值為8000。參數(shù)optimizer_search_limit 和optimizer_max_permutations一同用來(lái)設(shè)置優(yōu)化器所考慮的排列數(shù)的上限。優(yōu)化器不斷的產(chǎn)生可能的表的連接的排列，直到排列數(shù)達(dá)到參數(shù)optimizer_search_limit或者optimizer_max_permutations為止。一旦優(yōu)化器停止產(chǎn)生新的可能連接排列，它將會(huì)從中選擇出耗費(fèi)最小的排列。用已排序指示來(lái)指定一種連接排序你可以設(shè)定優(yōu)化器評(píng)估的排列數(shù)的上限。但是對(duì)復(fù)雜的情況下，即使答應(yīng)的排列數(shù)很大，優(yōu)化器也很可能在遠(yuǎn)遠(yuǎn)沒(méi)有找到一個(gè)比較合適的排列之間就已經(jīng)停止優(yōu)化了。你不妨回頭看看我前面舉的那個(gè)例子（15個(gè)需要連接的表有超過(guò)一萬(wàn)億種排列）。假如設(shè)定優(yōu)化器考慮80，000種排列，那么這僅僅考慮了所有可能性的0.000006％，優(yōu)化器極可能沒(méi)有達(dá)到最佳的排列。在Oracle SQL中解決這個(gè)問(wèn)題的最好方法就是手工指定一種表格連接順序。這里需要遵循的大原則就是表格連接順序應(yīng)該使得查詢計(jì)劃盡快得以建立，通常在SQL語(yǔ)句中使用WHERE限制子句。下面以一個(gè)對(duì)名為emp的表格的并行查詢?yōu)槔又械拇a強(qiáng)制查詢計(jì)劃執(zhí)行一個(gè)嵌套循環(huán)連接（nested loop join）。注重，我使用了已排序指示來(lái)引導(dǎo)優(yōu)化器來(lái)評(píng)估WHERE子句中給出的表格的連接順序。

　　　　select /*+ ordered use_nl(bonus) parallel(e, 4) */　　e.ename,　　hiredate,　　b.comm.　　from　　emp e,　　bonus b　　where　　e.ename = b.ename　　;

上面的例子要求優(yōu)化器按照SQL語(yǔ)句中FROM子句指定的順序連接表格，F(xiàn)ROM子句中第一個(gè)的表格指定為驅(qū)動(dòng)表格（driving table）。已排序指示經(jīng)常與其它指示聯(lián)合使用以確保多個(gè)表格按照適當(dāng)?shù)捻樞蜻B接起來(lái)。在碰到涉及四個(gè)以上表格的數(shù)據(jù)倉(cāng)庫(kù)查詢時(shí)經(jīng)常也是這樣處理。下面另給出一個(gè)例子，在這個(gè)例子中，我們使用一個(gè)已排序指示（ordered hint）來(lái)把表格按照一個(gè)特定的順序（先是emp，然后是dep和sal，最后是bonus）連接起來(lái)。進(jìn)一步改進(jìn)執(zhí)行計(jì)劃，我指定emp表格到dept表格的連接使用hash連接，sal表格到bonus表格使用嵌套循環(huán)連接。

　　　　select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */　　from　　emp,　　dept,　　sal,　　bonus　　where . . .

對(duì)實(shí)際應(yīng)用的建議在實(shí)際應(yīng)用場(chǎng)合下，減小optimizer_max_permutations參數(shù)并使用已保存的優(yōu)化計(jì)劃或者已保存綱要（這樣在查詢涉及到許多表格時(shí)，就可以避免重新解析查詢所花費(fèi)的實(shí)際）會(huì)更有效率。一旦找到最好的表格連接順序，你可以手工指定表格的連接順序（通過(guò)已排序指示）并保存綱要，這樣就永久保存該表格連接順序。當(dāng)執(zhí)行一個(gè)新的查詢時(shí)，你可以首先把optimizer_search_limit設(shè)置為該查詢所涉及的表格數(shù)，這樣優(yōu)化器將從所有的連接順序中找出最佳的那種。以后執(zhí)行該查詢時(shí)，你就可以在WHERE子句中按照最佳連接順序排列表格名稱，并設(shè)置已保存指示和已保存綱要，這樣就可以按照最佳順序連接表格而無(wú)需重復(fù)評(píng)估各種可能排序。這樣查詢的速度將會(huì)得到顯著的提高。已排序指示的優(yōu)先級(jí)高于optimizer_search_limit和 optimizer_max_permutations參數(shù)。假如設(shè)置了已排序指示，那么表格就會(huì)按照查詢命令中的FROM子句給出的順序連接，這樣這個(gè)過(guò)程就沒(méi)有優(yōu)化器優(yōu)化表格的連接順序這一步驟了。作為Oracle的專業(yè)人士，你應(yīng)該知道SQL語(yǔ)句進(jìn)入庫(kù)cache中有一個(gè)明顯的起始延時(shí)。但是聰明的Oracle數(shù)據(jù)庫(kù)治理員以及Oracle開(kāi)發(fā)者能改變表格的搜索限制參數(shù)或者利用已排序指示來(lái)手工指定表格的連接順序，這樣可以極大的降低優(yōu)化以及執(zhí)行新查詢所花費(fèi)的時(shí)間。

上一篇：Oracle9i中數(shù)據(jù)倉(cāng)庫(kù)的增強(qiáng)及其價(jià)值

下一篇：Oracle數(shù)據(jù)完整性嵌套事務(wù)調(diào)用的研究