Ｏｒａｃｌｅ中ＳＱＬ優(yōu)化原理分析

江　駿

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

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

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

中圖分類號(hào)：G642

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

2SQL優(yōu)化目標(biāo)

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

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

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

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

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

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

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

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

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

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

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

3DML語句的處理原理

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

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

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

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

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

在語法分析期間，SQL語句從用戶進(jìn)程傳送到Oracle，

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

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

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

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

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

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

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

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

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

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

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

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

因?yàn)橹付俗兞棵?，?dāng)再次執(zhí)行之前無須重新捆綁變量。而只需改變綁定變量的值，而Oracle在每次執(zhí)行時(shí)，僅僅使用內(nèi)存地址來查找此值。

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

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

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

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

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

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

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

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

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í)行計(jì)劃。

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

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

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

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

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

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

5執(zhí)行計(jì)劃

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

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

6共享多SQL語句

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

7ROWID

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

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

8HINTS

HINTS是Oracle提供的一種機(jī)制，用來使優(yōu)化器按照HINTS生成執(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) 語句的并行程度。

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

9結(jié)束語

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

參考文獻(xiàn)：

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

[2] 王倫文,鐘子發(fā). Oracle數(shù)據(jù)庫設(shè)計(jì)優(yōu)化與實(shí)踐[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).