Ｏｒａｃｌｅ中ＳＱＬ優化原理分析

江　駿

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

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

關鍵詞：Oracle；優化；SQL

中圖分類號：G642

文獻標識碼：B

1數據庫優化的必要性

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

2SQL優化目標

一般來說，Oracle的應用分為兩種類型:聯機事務處理(OLTP)，決策支持系統(DSS)。根據應用類型的不同，性能優化的目標有所不同：

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

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

當設計這類系統時，必須確保大量的并發用戶不能干擾系統的性能。還需要避免使用過量的索引與Cluster表，因為這些結構會使插入和更新操作變慢。

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

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

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

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

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

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

用戶的應用系統屬于典型的決策支持系統，用戶需求的關鍵就是盡量減少應用程序的查詢響應時間。

3DML語句的處理原理

用戶的應用系統的主要工作就是使用DML語句對數據庫進行查詢，要對SQL查詢語句進行優化，就必須對DML語句的處理原理有所了解。

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

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

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

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

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

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

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

以上任何一步出現錯誤，都將導致語句報錯，中止執行。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL語句處理的最后一個階段就是關閉游標。

4Oracle優化器

優化器是SQL處理引擎的核心，Oracle服務器支持兩種優化器：基于規則的優化器和基于成本的優化器。

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

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

數據庫使用何種優化器主要是由可變參數Optimizer_mode決定的。該參數可取值為：

RULE為使用RBO優化器。

CHOOSE則是根據實際情況，如果數據字典中包含被引用的表的統計數據，即引用的對象已經被分析，則就使用CBO優化器，否則為RBO優化器。

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

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

5執行計劃

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

執行計劃一般由幾個步驟組成。計劃里的每個步驟從數據文件物理地讀取塊或從緩存區讀取塊，然后處理每個塊中的表行或索引數據，為下一步做好準備。最后階段是創建結果集。

6共享多SQL語句

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

7ROWID

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

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

8HINTS

HINTS是Oracle提供的一種機制，用來使優化器按照HINTS生成執行計劃。HINTS可以實現：

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

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

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

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

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

(6) 語句的并行程度。

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

9結束語

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

參考文獻：

[1] 李丹. Oracle的性能優化[J]. 河北工程技術職業學院學報,2003(1).

[2] 王倫文,鐘子發. Oracle數據庫設計優化與實踐[J]. 電訊技術,2001(1).

[3] 閆偉,張喜民,張軍華. Oracle數據庫及應用程序優化[J]. 內蒙古石油化工,2005(1).

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