利用執行計劃調優ＯＲＡＣＬＥ數據庫

摘要：本文通過對SQL的執行計劃方面進行切入，結合執行計劃的各個方面來進行相應的實驗和分析，從而達到提高ORACLE效率，和解決時下由于數據庫功能和規模的擴大與數據庫性能調優的復雜帶來的應用的矛盾的效果。

關鍵詞：Oracle；調優；執行計劃； Cost

中圖分類號：TP311文獻標識碼：A 文章編號：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 引言

數據庫系統是人們用來管理信息的有效存儲工具，但隨著信息社會與網絡應用的不斷發展，數據庫規模也隨之不斷的擴大，數據庫系統的性能問題就越來越突出。優化是有目的地更改系統的一個或多個組件，使其滿足一個或多個目標的過程。對Oracle來說，優化是進行有目的的調整組件級以改善性能，即增加吞吐量，減少響應時間。本文針對數據庫系統的性能調優問題，通過以Oracle為例，從SQL的執行計劃方面來對數據庫調優方面做出相應的闡述。

2 優化器

分析語句的執行計劃的工作是由優化器(Optimizer)來完成的。

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

如果數據庫的優化器模式設置為選擇性(CHOOSE)，那么實際的優化器模式將和是否運行過analyze命令有關. 如果table已經被analyze過， 優化器模式將自動成為CBO ， 反之，數據庫將采用RULE形式的優化器。

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

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

3 執行計劃各參數分析

我們來看一個用dbms_system存儲過程生成的執行計劃，因為使用dbms_system存儲過程可以跟蹤另一個會話發出的sql語句，并記錄所使用的執行計劃，而且還提供其它對性能調整有用的信息。

例子：txd_nonauto_agreement_t是一個較大分區的表，txd_nonauto_agreement_t是一個數據跟nonauto_agreement_t完全一樣但沒有分區也沒有索引的表。

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

select * from txd_nonauto_agreement_t where policy_no=’AJINH59A2005B001172J’

在PL/SQL中可以看到如下的執行計劃：

■

圖1 沒有索引的執行計劃結果

在這個例子中，TABLE ACCESS FULL是第一個操作，意思是在txd_nonauto_agreement_t表上做全表掃描。當這個操作完成之后，產生的row source中的數據被送往下一步驟進行處理，在此例中，SELECT STATEMENT操作是這個查詢語句的最后一步。

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

select * from nonauto_agreement_t where policy_no=’AJINH59A2005B001172J’;

■

圖2 帶有索引的執行計劃結果

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

通過比較顯然我們可以看到通過所以檢索的COST遠比全表掃描要好很多，當然我們也能發現以下幾個特點：

決定該語句使用何種優化器的唯一方法是看后面的cost部分。例如，如果給出的是下面的形式，則表明使用的是CBO優化器，此處的cost表示優化器認為該執行計劃的代價:

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

然而假如執行計劃中給出的是類似下面的信息，則表明是使用RBO優化器，因為cost部分的值為空，或者壓根就沒有cost部分。

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

cost屬性的值是一個在oracle內部用來比較各個執行計劃所耗費的代價的值，從而使優化器可以選擇最好的執行計劃。不同語句的cost值不具有可比性，只能對同一個語句的不同執行計劃的cost值進行比較。

4 執行計劃分析以及優化

4.1 分析執行計劃

考慮下面的查詢：(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上查看執行計劃：

■

圖3 基于CBO優化器的執行計劃結果

在表做連接時，只能2個表先做連接，然后將連接后的結果作為一個row source，與剩下的表做連接，在上面的例子中，連接順序為B與C先連接，然后再與A連接：

如下所示：

1. B<---> C

2.<--->A

如果沒有執行計劃，分析一下，上面的3個表應該拿哪一個作為第一個驅動表?從SQL語句看來，只有B表與C表上有限制條件，所以第一個驅動表應該為這2個表中的一個，到底是哪一個呢?

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')，這樣在對B表做全表掃描的時候就將where子句中的限制條件用上，從而得到一個較小的row source， 所以C表應該作為第一個驅動表。而且這樣的話，如果再與A表做關聯，可以有效利用A表的索引(因為A表的claim_folder_no列為leading column)。

當然上面的查詢中C表上也有謂詞(C.currencycode = '01')，有人可能認為C表作為第一個驅動表也能獲得較好的性能。讓我們再來分析一下：如果C表作為第一個驅動表，則能保證驅動表生成很小的row source，但它與A表連接時沒用到A表的索引，因此nested loop的效率很差。

因此上面查詢比較好的連接順序為(B -- > C) -- > A，由此可以看出基于代價的優化器，它會利用計算出的代價來決定合適的驅動表與合適的連接順序。

4.2 優化執行計劃

對于RBO優化器：

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

對于CBO優化器：

CBO根據統計信息選擇驅動表，假如沒有統計信息，則在from 子句中從左到右的順序選擇驅動表。這與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中，如果有統計數據(即對表與索引進行了分析)，則優化器會自動根據cost值決定采用哪種連接類型，并選擇合適的驅動表，這與where子句中各個限制條件的位置沒有任何關系。

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

如果我們要改變優化器選擇的連接類型或驅動表，則就需要使用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'

既然選擇正確的驅動表這么重要，那么讓我們再來看一下執行計劃，到底各個表之間是如何關聯的，從而得到執行計劃中哪個表應該為驅動表：

■

圖4 帶hints的基于CBO優化器的執行計劃結果

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

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

看執行計劃時，我們的關鍵不是看哪個操作先執行，哪個操作后執行，而是關鍵看表之間連接的順序(如得知哪個為驅動表，這需要從操作的順序進行判斷)、使用了何種類型的關聯及具體的存取路徑(如判斷是否利用了索引)

在從執行計劃中判斷出哪個表為驅動表后，根據我們的知識判斷該表作為驅動表(就像上面判斷ABC表那樣)是否合適，如果不合適，對SQL語句進行更改，使優化器可以選擇正確的驅動表。

4.3 hints的使用

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

hints是oracle提供的一種機制，用來告訴優化器按照我們的告訴它的方式生成執行計劃。我們可以用hints來實現：

1) 使用的優化器的類型

2) 基于代價的優化器的優化目標，是all_rows還是first_rows。

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

4) 表之間的連接類型

5) 表之間的連接順序

6) 語句的并行程度

使用全套的hints：

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

5 結束語

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

參考文獻：

[1] Donald K.Burleson.ORACLE High-Performance SQL Tunning[M].劉硯，等，譯.北京:機械工業出版社，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專家高級編程-Expert_one-on-one_Oracle[M].袁勤勇，張玉魁，等.譯.北京:清華大學出版社，2002.4.

[5] 郭海峰，陽國貴.Oracle數據庫性能調優技術與實現[J].計算機工程，2006，32(19).82-85.

[6] 楊陽.Oracle數據庫的調優[J].軟件導刊，2007，11(21):136-138.