摘要：本文通過對(duì)SQL的執(zhí)行計(jì)劃方面進(jìn)行切入，結(jié)合執(zhí)行計(jì)劃的各個(gè)方面來進(jìn)行相應(yīng)的實(shí)驗(yàn)和分析，從而達(dá)到提高ORACLE效率，和解決時(shí)下由于數(shù)據(jù)庫(kù)功能和規(guī)模的擴(kuò)大與數(shù)據(jù)庫(kù)性能調(diào)優(yōu)的復(fù)雜帶來的應(yīng)用的矛盾的效果。

關(guān)鍵詞：Oracle；調(diào)優(yōu)；執(zhí)行計(jì)劃； Cost

中圖分類號(hào)：TP311文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044(2008)28-0013-03

Using SQL Plan to Optimize Oracle Database

TANG Xu-dong

(Tongji University，Shanghai 200093，China)

Abstract: In this paper， the optimized designs are focused on SQL plan， It analyses the implement of the optimization of oracle database by the aspects of SQL plan， to achieve the goal of improvement of the Oracle Database and salvation of The conflict between the expansion of database functionality and scale， and the growing complexity of database tunning.

Key words: oracle; tunning; plan; cost

1 引言

數(shù)據(jù)庫(kù)系統(tǒng)是人們用來管理信息的有效存儲(chǔ)工具，但隨著信息社會(huì)與網(wǎng)絡(luò)應(yīng)用的不斷發(fā)展，數(shù)據(jù)庫(kù)規(guī)模也隨之不斷的擴(kuò)大，數(shù)據(jù)庫(kù)系統(tǒng)的性能問題就越來越突出。優(yōu)化是有目的地更改系統(tǒng)的一個(gè)或多個(gè)組件，使其滿足一個(gè)或多個(gè)目標(biāo)的過程。對(duì)Oracle來說，優(yōu)化是進(jìn)行有目的的調(diào)整組件級(jí)以改善性能，即增加吞吐量，減少響應(yīng)時(shí)間。本文針對(duì)數(shù)據(jù)庫(kù)系統(tǒng)的性能調(diào)優(yōu)問題，通過以O(shè)racle為例，從SQL的執(zhí)行計(jì)劃方面來對(duì)數(shù)據(jù)庫(kù)調(diào)優(yōu)方面做出相應(yīng)的闡述。

2 優(yōu)化器

分析語句的執(zhí)行計(jì)劃的工作是由優(yōu)化器(Optimizer)來完成的。

ORACLE的優(yōu)化器共有3種: 1) RULE (基于規(guī)則); 2) COST (基于成本); 3) CHOOSE (選擇性) 。

如果數(shù)據(jù)庫(kù)的優(yōu)化器模式設(shè)置為選擇性(CHOOSE)，那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān). 如果table已經(jīng)被analyze過， 優(yōu)化器模式將自動(dòng)成為CBO ， 反之，數(shù)據(jù)庫(kù)將采用RULE形式的優(yōu)化器。

在缺省情況下，ORACLE采用CHOOSE優(yōu)化器， 為了避免那些不必要的全表掃描(full table scan) ， 你必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的優(yōu)化器。

優(yōu)化器在形成執(zhí)行計(jì)劃時(shí)需要做的一個(gè)重要選擇是如何從數(shù)據(jù)庫(kù)查詢出需要的數(shù)據(jù)。對(duì)于SQL語句存取的任何表中的任何行，可能存在許多存取路徑(存取方法)，通過它們可以定位和查詢出需要的數(shù)據(jù)。優(yōu)化器選擇其中自認(rèn)為是最優(yōu)化的路徑。

3 執(zhí)行計(jì)劃各參數(shù)分析

我們來看一個(gè)用dbms_system存儲(chǔ)過程生成的執(zhí)行計(jì)劃，因?yàn)槭褂胐bms_system存儲(chǔ)過程可以跟蹤另一個(gè)會(huì)話發(fā)出的sql語句，并記錄所使用的執(zhí)行計(jì)劃，而且還提供其它對(duì)性能調(diào)整有用的信息。

例子：txd_nonauto_agreement_t是一個(gè)較大分區(qū)的表，txd_nonauto_agreement_t是一個(gè)數(shù)據(jù)跟nonauto_agreement_t完全一樣但沒有分區(qū)也沒有索引的表。

1)txd_nonauto_agreement_t的 policy_no列上沒有索引，則運(yùn)行下面的語句：

select * from txd_nonauto_agreement_t where policy_no=’AJINH59A2005B001172J’

在PL/SQL中可以看到如下的執(zhí)行計(jì)劃：

■

圖1 沒有索引的執(zhí)行計(jì)劃結(jié)果

在這個(gè)例子中，TABLE ACCESS FULL是第一個(gè)操作，意思是在txd_nonauto_agreement_t表上做全表掃描。當(dāng)這個(gè)操作完成之后，產(chǎn)生的row source中的數(shù)據(jù)被送往下一步驟進(jìn)行處理，在此例中，SELECT STATEMENT操作是這個(gè)查詢語句的最后一步。

2)nonauto_agreement_t 表的policy_no列上有索引，則運(yùn)行下面的語句：

select * from nonauto_agreement_t where policy_no=’AJINH59A2005B001172J’;

■

圖2 帶有索引的執(zhí)行計(jì)劃結(jié)果

在這個(gè)例子中，INDEX RANGE SCAN是第一個(gè)操作，通過nonauto_agreement_t的索引來掃描 TABLE ACCESS BY LOCAL INDEX ROWID。當(dāng)這個(gè)操作完成之后，產(chǎn)生的row source中的數(shù)據(jù)被送往下一步驟進(jìn)行處理，在此例中，SELECT STATEMENT操作是這個(gè)查詢語句的最后一步。

通過比較顯然我們可以看到通過所以檢索的COST遠(yuǎn)比全表掃描要好很多，當(dāng)然我們也能發(fā)現(xiàn)以下幾個(gè)特點(diǎn)：

決定該語句使用何種優(yōu)化器的唯一方法是看后面的cost部分。例如，如果給出的是下面的形式，則表明使用的是CBO優(yōu)化器，此處的cost表示優(yōu)化器認(rèn)為該執(zhí)行計(jì)劃的代價(jià):

SELECT STATEMENT (Cost=2981 Cardinality=3268 Bytes=607848)

然而假如執(zhí)行計(jì)劃中給出的是類似下面的信息，則表明是使用RBO優(yōu)化器，因?yàn)閏ost部分的值為空，或者壓根就沒有cost部分。

這樣我們從Optimizer的信息中可以得出執(zhí)行該語句時(shí)到底用了什么樣的優(yōu)化器。特別的，如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n(此例中就是用了ALL_ROWS)，則使用的是CBO優(yōu)化器;如果Optimizer=RULE，則使用的是RBO優(yōu)化器。

cost屬性的值是一個(gè)在oracle內(nèi)部用來比較各個(gè)執(zhí)行計(jì)劃所耗費(fèi)的代價(jià)的值，從而使優(yōu)化器可以選擇最好的執(zhí)行計(jì)劃。不同語句的cost值不具有可比性，只能對(duì)同一個(gè)語句的不同執(zhí)行計(jì)劃的cost值進(jìn)行比較。

4 執(zhí)行計(jì)劃分析以及優(yōu)化

4.1 分析執(zhí)行計(jì)劃

考慮下面的查詢：(A(nonauto_claim_folder_t)、B(nonauto_cf_indemnity_t)、C(unit_mapping_t)都不是小表，其中A表的claim_folder_no有索引)

Select (b.indemnity_no) indemno

fromnonauto_claim_folder_t a， nonauto_cf_indemnity_t b， unit_mapping_t c

where (b.check_date between to_date('2008-07-01'，'YYYY-MM-DD hh24:mi:ss') and

to_date('2008-07-11'，'YYYY-MM-DD hh24:mi:ss'))

and a.claim_folder_no = b.claim_folder_no

and c.deptcode = b.department_code

and c.currencycode = '01'

在PL/SQL上查看執(zhí)行計(jì)劃：

■

圖3 基于CBO優(yōu)化器的執(zhí)行計(jì)劃結(jié)果

在表做連接時(shí)，只能2個(gè)表先做連接，然后將連接后的結(jié)果作為一個(gè)row source，與剩下的表做連接，在上面的例子中，連接順序?yàn)锽與C先連接，然后再與A連接：

如下所示：

1. B<---> C

2.<--->A

如果沒有執(zhí)行計(jì)劃，分析一下，上面的3個(gè)表應(yīng)該拿哪一個(gè)作為第一個(gè)驅(qū)動(dòng)表?從SQL語句看來，只有B表與C表上有限制條件，所以第一個(gè)驅(qū)動(dòng)表應(yīng)該為這2個(gè)表中的一個(gè)，到底是哪一個(gè)呢?

B表有謂詞b.check_date between to_date('2008-07-01'，'YYYY-MM-DD hh24:mi:ss') and

to_date('2008-07-11'，'YYYY-MM-DD hh24:mi:ss')，這樣在對(duì)B表做全表掃描的時(shí)候就將where子句中的限制條件用上，從而得到一個(gè)較小的row source， 所以C表應(yīng)該作為第一個(gè)驅(qū)動(dòng)表。而且這樣的話，如果再與A表做關(guān)聯(lián)，可以有效利用A表的索引(因?yàn)锳表的claim_folder_no列為leading column)。

當(dāng)然上面的查詢中C表上也有謂詞(C.currencycode = '01')，有人可能認(rèn)為C表作為第一個(gè)驅(qū)動(dòng)表也能獲得較好的性能。讓我們?cè)賮矸治鲆幌拢喝绻鸆表作為第一個(gè)驅(qū)動(dòng)表，則能保證驅(qū)動(dòng)表生成很小的row source，但它與A表連接時(shí)沒用到A表的索引，因此nested loop的效率很差。

因此上面查詢比較好的連接順序?yàn)?B -- > C) -- > A，由此可以看出基于代價(jià)的優(yōu)化器，它會(huì)利用計(jì)算出的代價(jià)來決定合適的驅(qū)動(dòng)表與合適的連接順序。

4.2 優(yōu)化執(zhí)行計(jì)劃

對(duì)于RBO優(yōu)化器：

在ORACLE文檔上說：對(duì)于RBO來說，以from 子句中從右到左的順序選擇驅(qū)動(dòng)表，即最右邊的表為第一個(gè)驅(qū)動(dòng)表，這是其英文原文：All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT。

對(duì)于CBO優(yōu)化器：

CBO根據(jù)統(tǒng)計(jì)信息選擇驅(qū)動(dòng)表，假如沒有統(tǒng)計(jì)信息，則在from 子句中從左到右的順序選擇驅(qū)動(dòng)表。這與RBO選擇的順序正好相反。這是英文原文(CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause. This is OPPOSITE to the RBO) 。在CBO中，如果有統(tǒng)計(jì)數(shù)據(jù)(即對(duì)表與索引進(jìn)行了分析)，則優(yōu)化器會(huì)自動(dòng)根據(jù)cost值決定采用哪種連接類型，并選擇合適的驅(qū)動(dòng)表，這與where子句中各個(gè)限制條件的位置沒有任何關(guān)系。

一般來說，CBO都會(huì)選擇正確的連接順序，如果CBO選擇了比較差的連接順序，我們還可以使用ORACLE提供的hints來讓CBO采用正確的連接順序。

如果我們要改變優(yōu)化器選擇的連接類型或驅(qū)動(dòng)表，則就需要使用hints了。如下所示：

Select/*+ ordered */(b.indemnity_no) indemno

fromnonauto_cf_indemnity_t b， unit_mapping_t c，nonauto_claim_folder_t a

where (b.check_date between to_date('2008-07-01'，'YYYY-MM-DD hh24:mi:ss') and

to_date('2008-07-11'，'YYYY-MM-DD hh24:mi:ss'))

and a.claim_folder_no = b.claim_folder_no

and c.deptcode = b.department_code

and c.currencycode = '01'

既然選擇正確的驅(qū)動(dòng)表這么重要，那么讓我們?cè)賮砜匆幌聢?zhí)行計(jì)劃，到底各個(gè)表之間是如何關(guān)聯(lián)的，從而得到執(zhí)行計(jì)劃中哪個(gè)表應(yīng)該為驅(qū)動(dòng)表：

■

圖4 帶hints的基于CBO優(yōu)化器的執(zhí)行計(jì)劃結(jié)果

在執(zhí)行計(jì)劃中，需要知道哪個(gè)操作是先執(zhí)行的，哪個(gè)操作是后執(zhí)行的，這對(duì)于判斷哪個(gè)表為驅(qū)動(dòng)表有用處。判斷之前，如果對(duì)表的訪問是通過rowid，且該rowid的值是從索引掃描中得來得，則將該索引掃描先從執(zhí)行計(jì)劃中暫時(shí)去掉。然后在執(zhí)行計(jì)劃剩下的部分中，判斷執(zhí)行順序的指導(dǎo)原則就是：最右、最上的操作先執(zhí)行。具體解釋如下：

看執(zhí)行計(jì)劃的第3列，即字母部分，每列值的左面有空格作為縮進(jìn)字符。在該列值左邊的空格越多，說明該列值的縮進(jìn)越多，該列值也越靠右。

看執(zhí)行計(jì)劃時(shí)，我們的關(guān)鍵不是看哪個(gè)操作先執(zhí)行，哪個(gè)操作后執(zhí)行，而是關(guān)鍵看表之間連接的順序(如得知哪個(gè)為驅(qū)動(dòng)表，這需要從操作的順序進(jìn)行判斷)、使用了何種類型的關(guān)聯(lián)及具體的存取路徑(如判斷是否利用了索引)

在從執(zhí)行計(jì)劃中判斷出哪個(gè)表為驅(qū)動(dòng)表后，根據(jù)我們的知識(shí)判斷該表作為驅(qū)動(dòng)表(就像上面判斷ABC表那樣)是否合適，如果不合適，對(duì)SQL語句進(jìn)行更改，使優(yōu)化器可以選擇正確的驅(qū)動(dòng)表。

4.3 hints的使用

基于代價(jià)的優(yōu)化器是很聰明的，在絕大多數(shù)情況下它會(huì)選擇正確的優(yōu)化器，減輕了DBA的負(fù)擔(dān)。但有時(shí)它也聰明反被聰明誤，選擇了很差的執(zhí)行計(jì)劃，使某個(gè)語句的執(zhí)行變得奇慢無比。此時(shí)就需要DBA進(jìn)行人為的干預(yù)，告訴優(yōu)化器使用我們指定的存取路徑或連接類型生成執(zhí)行計(jì)劃，從而使語句高效的運(yùn)行。例如，如果我們認(rèn)為對(duì)于一個(gè)特定的語句，執(zhí)行全表掃描要比執(zhí)行索引掃描更有效，則我們就可以指示優(yōu)化器使用全表掃描。在ORACLE中，是通過為語句添加hints(提示)來實(shí)現(xiàn)干預(yù)優(yōu)化器優(yōu)化的目的。

hints是oracle提供的一種機(jī)制，用來告訴優(yōu)化器按照我們的告訴它的方式生成執(zhí)行計(jì)劃。我們可以用hints來實(shí)現(xiàn)：

1) 使用的優(yōu)化器的類型

2) 基于代價(jià)的優(yōu)化器的優(yōu)化目標(biāo)，是all_rows還是first_rows。

3) 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。

4) 表之間的連接類型

5) 表之間的連接順序

6) 語句的并行程度

使用全套的hints：

當(dāng)使用hints時(shí)，在某些情況下，為了確保讓優(yōu)化器產(chǎn)生最優(yōu)的執(zhí)行計(jì)劃，我們可能指定全套的hints。例如，如果有一個(gè)復(fù)雜的查詢，包含多個(gè)表連接，如果你只為某個(gè)表指定了INDEX提示(指示存取路徑在該表上使用索引)，優(yōu)化器需要來決定其它應(yīng)該使用的訪問路徑和相應(yīng)的連接方法。因此，即使你給出了一個(gè)INDEX提示，優(yōu)化器可能覺得沒有必要使用該提示。這是由于我們讓優(yōu)化器選擇了其它連接方法和存取路徑，而基于這些連接方法和存取路徑，優(yōu)化器認(rèn)為用戶給出的INDEX提示無用。為了防止這種情況，我們要使用全套的hints，如：不但指定要使用的索引，而且也指定連接的方法與連接的順序等。

5 結(jié)束語

總之，對(duì)所有的性能問題，沒有一個(gè)統(tǒng)一的解決方法，但ORACLE提供了豐富的選擇環(huán)境，而通過執(zhí)行計(jì)劃來優(yōu)化SQL從而達(dá)到優(yōu)化數(shù)據(jù)庫(kù)目的更加要求每個(gè)對(duì)數(shù)據(jù)庫(kù)操作的人都可以通過你寫下的代碼來優(yōu)化數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)的性能調(diào)整是一個(gè)系統(tǒng)工程，涉及的方面很多。如何有效地進(jìn)行調(diào)整，數(shù)據(jù)庫(kù)管理員需要經(jīng)過反反復(fù)復(fù)的過程，且要注重各方面的優(yōu)化才會(huì)取得整體效果。這些都需要在大量的實(shí)踐工作中不斷地積累經(jīng)驗(yàn)，從而更好地進(jìn)行數(shù)據(jù)庫(kù)的調(diào)優(yōu)。

參考文獻(xiàn)：

[1] Donald K.Burleson.ORACLE High-Performance SQL Tunning[M].劉硯，等，譯.北京:機(jī)械工業(yè)出版社，2002.3.

[2] Kenvin L.oracle database 10g - the complete reference[M].McGraw-Hill，2006.

[3] Sheila M.PLSQL Language Reference[M].oracle，2007.9.

[4] Thomas K.Wrox-Oracle專家高級(jí)編程-Expert_one-on-one_Oracle[M].袁勤勇，張玉魁，等.譯.北京:清華大學(xué)出版社，2002.4.

[5] 郭海峰，陽(yáng)國(guó)貴.Oracle數(shù)據(jù)庫(kù)性能調(diào)優(yōu)技術(shù)與實(shí)現(xiàn)[J].計(jì)算機(jī)工程，2006，32(19).82-85.

[6] 楊陽(yáng).Oracle數(shù)據(jù)庫(kù)的調(diào)優(yōu)[J].軟件導(dǎo)刊，2007，11(21):136-138.