江　駿

文章編號：1672-5913(2009)10-0184-03

摘要：對于SQL語句優(yōu)化，因為會有成千上萬條SQL語句訪問Oracle數(shù)據(jù)庫，所以這是最費時間的調(diào)整操作。所謂的SQL語句優(yōu)化就是選擇最有效的執(zhí)行計劃來執(zhí)行SQL語句的過程。本文對Oracle中SQL的優(yōu)化原理進行了分析。

關(guān)鍵詞：Oracle；優(yōu)化；SQL

中圖分類號：G642

文獻標識碼：B

1數(shù)據(jù)庫優(yōu)化的必要性

隨著數(shù)據(jù)庫技術(shù)應(yīng)用越來越廣泛，用戶日益增長，使數(shù)據(jù)量越來越大，且伴隨數(shù)據(jù)挖掘、數(shù)據(jù)分析不斷深人，數(shù)據(jù)處理也越來越復(fù)雜，龐大數(shù)據(jù)量加上復(fù)雜數(shù)據(jù)處理過程，使數(shù)據(jù)庫服務(wù)器經(jīng)常超負荷運作，甚至出現(xiàn)死鎖現(xiàn)象，嚴重影響使用效果，若不注重SQL 查詢優(yōu)化策略，往往在系統(tǒng)投人初期使用時查詢反應(yīng)迅速，使用流暢，但隨著數(shù)據(jù)庫表中記錄數(shù)據(jù)量越來越大，系統(tǒng)響應(yīng)速度越來越慢，甚至無法正常響應(yīng)，就可能導(dǎo)致系統(tǒng)可用性降低，失去實用價值。因此，科學(xué)地構(gòu)造數(shù)據(jù)庫結(jié)構(gòu)，合理使用查詢語句及查詢方法，是成功開發(fā)和應(yīng)用數(shù)據(jù)庫系統(tǒng)重要環(huán)節(jié)，對于SQL語句優(yōu)化，因為會有成千上萬條SQL語句訪問Oracle數(shù)據(jù)庫，所以這是最費時間的調(diào)整操作。所謂的SQL語句優(yōu)化就是選擇最有效的執(zhí)行計劃來執(zhí)行SQL語句的過程，這是在處理任何數(shù)據(jù)的語句(SELECT，INSERT，UPDATE或DELETE)中的一個重要步驟?？傮w上講，就是需要識別出最常用的SQL語句，通過仔細審查SQL的執(zhí)行計劃以及使用Oracle提示(HINTS)調(diào)整執(zhí)行計劃來調(diào)整各個語句。本文就優(yōu)化Oracle數(shù)據(jù)庫SQL原理做一些分析。

2SQL優(yōu)化目標

一般來說，Oracle的應(yīng)用分為兩種類型:聯(lián)機事務(wù)處理(OLTP)，決策支持系統(tǒng)(DSS)。根據(jù)應(yīng)用類型的不同，性能優(yōu)化的目標有所不同：

2.1在線事務(wù)處理(OLTP=OnlineTransactionProcessing)

該類型的應(yīng)用是高吞吐量，插入、更新、刪除操作比較多的系統(tǒng)，這些系統(tǒng)以不斷增長的大容量數(shù)據(jù)為特征，它們提供給成百用戶同時存取。OTLP的主要目標是可用性、速度、并發(fā)性和可恢復(fù)性。

當設(shè)計這類系統(tǒng)時，必須確保大量的并發(fā)用戶不能干擾系統(tǒng)的性能。還需要避免使用過量的索引與Cluster表，因為這些結(jié)構(gòu)會使插入和更新操作變慢。

此種類型的應(yīng)用程序把吞吐量定義為性能指標。

2.2決策支持系統(tǒng)(DSS=DecisionSupportSystem)

該類型的應(yīng)用將大量信息進行提取形成報告，協(xié)助決策者作出正確的判斷。典型的情況是:決策支持系統(tǒng)將OLTP應(yīng)用收集的大量數(shù)據(jù)進行查詢。典型的應(yīng)用為客戶行為分析系統(tǒng)。

決策支持的關(guān)鍵目標是速度、精確性和可用性。

該種類型的設(shè)計往往與OLTP設(shè)計的理念背道而馳，一般建議使用數(shù)據(jù)冗余、大量索引、Cluster表、并行查詢等。

此種類型的應(yīng)用程序把響應(yīng)時間定義為性能指標。

用戶的應(yīng)用系統(tǒng)屬于典型的決策支持系統(tǒng)，用戶需求的關(guān)鍵就是盡量減少應(yīng)用程序的查詢響應(yīng)時間。

3DML語句的處理原理

用戶的應(yīng)用系統(tǒng)的主要工作就是使用DML語句對數(shù)據(jù)庫進行查詢，要對SQL查詢語句進行優(yōu)化，就必須對DML語句的處理原理有所了解。

DML語句的處理一般分9步：

第1步：創(chuàng)建游標(Create a Cursor)

由程序接口調(diào)用創(chuàng)建一個游標(Cursor)。任何SQL語句都會創(chuàng)建它，特別在運行DML語句時，都是自動創(chuàng)建游標的，不需要開發(fā)人員干預(yù)。多數(shù)應(yīng)用中，游標的創(chuàng)建是自動的。然而，在預(yù)編譯程序Pro*C)中游標的創(chuàng)建，可能是隱含的，也可能顯式的創(chuàng)建。在存儲過程中也是這樣的。

第2步：分析語句(Parse the Statement)

在語法分析期間，SQL語句從用戶進程傳送到Oracle，

SQL語句經(jīng)語法分析后，SQL語句本身與分析的信息都被裝入到共享SQL區(qū)。在該階段中，可以解決許多類型的錯誤。

語法分析分別執(zhí)行下列操作：翻譯SQL語句，驗證它是合法的語句，即書寫正確；實現(xiàn)數(shù)據(jù)字典的查找，以驗證是否符合表和列的定義；今在所要求的對象上獲取語法分析鎖，使得在語句的語法分析過程中不改變這些對象的定義；驗證為存取所涉及的模式對象所需的權(quán)限是否滿足；決定此語句最佳的執(zhí)行計劃；將它裝入共享SQL區(qū)；對分布的語句來說，把語句的全部或部分路由到包含所涉及數(shù)據(jù)的遠程節(jié)點。

以上任何一步出現(xiàn)錯誤，都將導(dǎo)致語句報錯，中止執(zhí)行。

只有在共享池中不存在等價SQL語句的情況下，才對SQL語句作語法分析。在這種情況下，數(shù)據(jù)庫內(nèi)核重新為該語句分配新的共享SQL區(qū)，并對語句進行語法分析。進行語法分析需要耗費較多的資源，所以要盡量避免進行語法分析。

語法分析階段包含了不管此語句將執(zhí)行多少次，而只需分析一次的處理要求。Oracle只對每個SQL語句翻譯一次，在以后再次執(zhí)行該語句時，只要該語句還在共享SQL區(qū)中，就可以避免對該語句重新進行語法分析，也就是此時可以直接使用其對應(yīng)的執(zhí)行計劃對數(shù)據(jù)進行存取。這主要是通過綁定變量(Bind Variable)實現(xiàn)的，也就是共享SQL。

SQL查詢語句與其它類型的SQL語句不同，因為在成功執(zhí)行后作為結(jié)果將返回數(shù)據(jù)。其它語句只是簡單地返回成功或失敗，而查詢則能返回一行或許多行數(shù)據(jù)。查詢的結(jié)果均采用表格形式，結(jié)果行被一次一行或者批量地被檢索出來。從這里可以得知批量的Fetch數(shù)據(jù)可以降低網(wǎng)絡(luò)開銷，所以批量的Fetch也是優(yōu)化的技巧之一。

第3步：描述查詢結(jié)果(Describe Results of a Query)

描述階段只有在查詢結(jié)果的各個列是未知時才需要；例如，當查詢由用戶交互地輸入需要輸出的列名。在這種情況要用描述階段來決定查詢結(jié)果的特征(數(shù)據(jù)類型，長度和名字)。

第4步：定義查詢的輸出數(shù)據(jù)(Define Output of a Query)

在查詢的定義階段，指定與查詢出的列值對應(yīng)的接收變量的位置、大小和數(shù)據(jù)類型，這樣可通過接收變量就可以得到查詢結(jié)果。如果必要的話，Oracle會自動實現(xiàn)數(shù)據(jù)類型的轉(zhuǎn)換。這是將接收變量的類型與對應(yīng)的列類型相比較決定的。

第5步：綁定變量(Bind Any Variables)

此時，Oracle知道了SQL語句的意思，但仍沒有足夠的信息用于執(zhí)行該語句。Oracle需要得到在語句中列出的所有變量的值。得到這個值的過程就叫綁定變量(binding variables)

因為指定了變量名，當再次執(zhí)行之前無須重新捆綁變量。而只需改變綁定變量的值，而Oracle在每次執(zhí)行時，僅僅使用內(nèi)存地址來查找此值。

第6步：并行執(zhí)行語句(Parallelize the Statement)

Oracle可以在SELECT，INSERT，UPDATE，MERGE，DELETE語句中執(zhí)行相應(yīng)并行查詢操作，對于某些DDL操作，如創(chuàng)建索引、用子查詢創(chuàng)建表、在分區(qū)表上的操作，也可以執(zhí)行并行操作。并行化可以導(dǎo)致多個服務(wù)器進程(Oracle Server Processes)為同一個SQL語句工作，使該SQL語句可以快速完成，但是會耗費更多的資源，所以除非很有必要，否則不要使用并行查詢。

第7步：執(zhí)行語句(Run the Statement)

到了現(xiàn)在這個時候，Oracle擁有所有需要的信息與資源，因此可以真正運行SQL語句了。如果該語句為SELECT查詢或工INSERT語句，則不需要鎖定任何行，因為沒有數(shù)據(jù)需要被改變。然而，如果語句為UPDATE或DELETE語句，則該語句影響的所有行都被鎖定，防止該用戶提交或回滾之前，別的用戶對這些數(shù)據(jù)進行修改。這保證了數(shù)據(jù)的一致性。

第8步：取出查詢的行(Fetch Rows of a Query)

在Fetch階段，行數(shù)據(jù)被取出來，每個后續(xù)的存取操作檢索結(jié)果集中的下一行數(shù)據(jù)，直到最后一行被取出來。上面提到過，批量的Fetch是優(yōu)化的技巧之一。

第9步：關(guān)閉游標(Close the Cursor)

SQL語句處理的最后一個階段就是關(guān)閉游標。

4Oracle優(yōu)化器

優(yōu)化器是SQL處理引擎的核心，Oracle服務(wù)器支持兩種優(yōu)化器：基于規(guī)則的優(yōu)化器和基于成本的優(yōu)化器。

基于規(guī)則的優(yōu)化器一Rule Based(Heuristic) Optimization(簡稱RBO)；Oracle在基于規(guī)則的優(yōu)化器中采用啟發(fā)式的方法(Heuristic Approach)或規(guī)則(Rules)來生成執(zhí)行計劃。

基于成本的優(yōu)化器一Cost Based Optimization(簡稱CBO)：Oracle把一個成本引擎(CostEngine)集成到數(shù)據(jù)庫內(nèi)核中，用來估計每個執(zhí)行計劃需要的成本，該成本將每個執(zhí)行計劃所耗費的資源進行量化，從而CBO可以根據(jù)這個成本選擇出最優(yōu)的執(zhí)行計劃。一個查詢耗費的資源可以被分成3個基本組成部分：I/O成本、CPU成本、Network成本。

數(shù)據(jù)庫使用何種優(yōu)化器主要是由可變參數(shù)Optimizer_mode決定的。該參數(shù)可取值為：

RULE為使用RBO優(yōu)化器。

CHOOSE則是根據(jù)實際情況，如果數(shù)據(jù)字典中包含被引用的表的統(tǒng)計數(shù)據(jù)，即引用的對象已經(jīng)被分析，則就使用CBO優(yōu)化器，否則為RBO優(yōu)化器。

ALL_Rows為CBO優(yōu)化器使用的第一種具體的優(yōu)化方法，是以數(shù)據(jù)的吞吐量為主要目標，以便可以使用最少的資源完成語句。

FIRST_Rows為CBO優(yōu)化器使用的第二種具體的優(yōu)化方法，是以數(shù)據(jù)的響應(yīng)時間為主要目標，以便快速查詢出開始的幾行數(shù)據(jù)。

5執(zhí)行計劃

在執(zhí)行一個DML語句之前，Oracle生成一個決定SQL語句將如何執(zhí)行的查詢執(zhí)行計劃。計劃生成過程使用了各種不同信息，包括：生成的統(tǒng)計量、對象尺寸、索引的存在、優(yōu)化器模式、HINT的出現(xiàn)、儲存概要的存在。

執(zhí)行計劃一般由幾個步驟組成。計劃里的每個步驟從數(shù)據(jù)文件物理地讀取塊或從緩存區(qū)讀取塊，然后處理每個塊中的表行或索引數(shù)據(jù)，為下一步做好準備。最后階段是創(chuàng)建結(jié)果集。

6共享多SQL語句

為了不重復(fù)解析相同的SQL語句(因為解析操作比較費資源，會導(dǎo)致性能下降)，在第一次解析之后，Oracle將SQL語句及解析后得到的執(zhí)行計劃存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域SGA的共享存儲區(qū)中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享。因此，當再次執(zhí)行一個SQL語句時，如果該語句和之前的執(zhí)行過的某一語句完全相同，并且之前執(zhí)行的該語句與其執(zhí)行計劃仍然在內(nèi)存中存在，則Oracle就不需要再進行分析，直接得到該語句的執(zhí)行路徑。Oracle的這個功能大大地提高了SQL的執(zhí)行性能并大大節(jié)省了內(nèi)存的使用。使用這個功能的關(guān)鍵是將執(zhí)行過的語句盡可能放到內(nèi)存中，所以這要求有大的共享池和盡可能的使用綁定變量的方法執(zhí)行SQL語句。

7ROWID

ROWID是一個偽列，既然是偽列，那么這個列就不是用戶定義，而是系統(tǒng)自己給加上的。對每個表都有一個ROWID的偽列，但是表中并不物理存儲ROWID列的值。

不過你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對該列的值進行修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫，則ROWID在該行的生命周期內(nèi)是唯一的，即即使該行產(chǎn)生行遷移，行的ROWID也不會改變。

8HINTS

HINTS是Oracle提供的一種機制，用來使優(yōu)化器按照HINTS生成執(zhí)行計劃。HINTS可以實現(xiàn)：

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

(2) 基于代價的優(yōu)化器的優(yōu)化目標，是ALL_Rows還是FIRST_Rows；

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

(4) 表之間的連接類型；

(5) 表之間的連接順序；

(6) 語句的并行程度。

除了RULES提示外，一旦使用的別的提示，語句就會自動的改為使用CBO優(yōu)化器，此時如果Oracle的數(shù)據(jù)字典中沒有統(tǒng)計數(shù)據(jù)，就會使用缺省的統(tǒng)計數(shù)據(jù)。所以在使用CBO或HINTS提示，最好對表和索引進行定期的分析。

9結(jié)束語

在數(shù)據(jù)庫應(yīng)用開發(fā)過程中，優(yōu)化設(shè)計可提高數(shù)據(jù)庫性能，特別是大型數(shù)據(jù)庫，優(yōu)化過程更為重要 當然數(shù)據(jù)庫性能優(yōu)化是一個復(fù)雜過程，本文只是對數(shù)據(jù)庫中SQL的優(yōu)化原理進行了分析并提供了一些優(yōu)化建議，供實際應(yīng)用時參考。

參考文獻：

[1] 李丹. Oracle的性能優(yōu)化[J]. 河北工程技術(shù)職業(yè)學(xué)院學(xué)報,2003(1).

[2] 王倫文,鐘子發(fā). Oracle數(shù)據(jù)庫設(shè)計優(yōu)化與實踐[J]. 電訊技術(shù),2001(1).

[3] 閆偉,張喜民,張軍華. Oracle數(shù)據(jù)庫及應(yīng)用程序優(yōu)化[J]. 內(nèi)蒙古石油化工,2005(1).

[4] 張韜. 淺析Oracle數(shù)據(jù)庫的性能優(yōu)化[J]. 中國科技信息,2005(5).