課題名稱(chēng):Ｏｒａｃｌｅ中ＳＱＬ優(yōu)化原理分析
摘要：對(duì)于SQL語(yǔ)句優(yōu)化，因?yàn)闀?huì)有成千上萬(wàn)條SQL語(yǔ)句訪問(wèn)Oracle數(shù)據(jù)庫(kù)，所以這是最費(fèi)時(shí)間的調(diào)整操作。所謂的SQL語(yǔ)句優(yōu)化就是選擇最有效的執(zhí)行計(jì)劃來(lái)執(zhí)行SQL語(yǔ)句的過(guò)程。本文對(duì)Oracle中SQL的優(yōu)化原理進(jìn)行了分析。
關(guān)鍵詞：Oracle；優(yōu)化；SQL
1數(shù)據(jù)庫(kù)優(yōu)化的必要性
隨著數(shù)據(jù)庫(kù)技術(shù)應(yīng)用越來(lái)越廣泛，用戶日益增長(zhǎng)，使數(shù)據(jù)量越來(lái)越大，且伴隨數(shù)據(jù)挖掘、數(shù)據(jù)分析不斷深人，數(shù)據(jù)處理也越來(lái)越復(fù)雜，龐大數(shù)據(jù)量加上復(fù)雜數(shù)據(jù)處理過(guò)程，使數(shù)據(jù)庫(kù)服務(wù)器經(jīng)常超負(fù)荷運(yùn)作，甚至出現(xiàn)死鎖現(xiàn)象，嚴(yán)重影響使用效果，若不注重SQL 查詢(xún)優(yōu)化策略，往往在系統(tǒng)投人初期使用時(shí)查詢(xún)反應(yīng)迅速，使用流暢，但隨著數(shù)據(jù)庫(kù)表中記錄數(shù)據(jù)量越來(lái)越大，系統(tǒng)響應(yīng)速度越來(lái)越慢，甚至無(wú)法正常響應(yīng)，就可能導(dǎo)致系統(tǒng)可用性降低，失去實(shí)用價(jià)值。因此，科學(xué)地構(gòu)造數(shù)據(jù)庫(kù)結(jié)構(gòu)，合理使用查詢(xún)語(yǔ)句及查詢(xún)方法，是成功開(kāi)發(fā)和應(yīng)用數(shù)據(jù)庫(kù)系統(tǒng)重要環(huán)節(jié)，對(duì)于SQL語(yǔ)句優(yōu)化，因?yàn)闀?huì)有成千上萬(wàn)條SQL語(yǔ)句訪問(wèn)Oracle數(shù)據(jù)庫(kù)，所以這是最費(fèi)時(shí)間的調(diào)整操作。所謂的SQL語(yǔ)句優(yōu)化就是選擇最有效的執(zhí)行計(jì)劃來(lái)執(zhí)行SQL語(yǔ)句的過(guò)程，這是在處理任何數(shù)據(jù)的語(yǔ)句(SELECT，INSERT，UPDATE或DELETE)中的一個(gè)重要步驟?？傮w上講，就是需要識(shí)別出最常用的SQL語(yǔ)句，通過(guò)仔細(xì)審查SQL的執(zhí)行計(jì)劃以及使用Oracle提示(HINTS)調(diào)整執(zhí)行計(jì)劃來(lái)調(diào)整各個(gè)語(yǔ)句。本文就優(yōu)化Oracle數(shù)據(jù)庫(kù)SQL原理做一些分析。
2SQL優(yōu)化目標(biāo)
一般來(lái)說(shuō)，Oracle的應(yīng)用分為兩種類(lèi)型:聯(lián)機(jī)事務(wù)處理(OLTP)，決策支持系統(tǒng)(DSS)。根據(jù)應(yīng)用類(lèi)型的不同，性能優(yōu)化的目標(biāo)有所不同：
2.1在線事務(wù)處理(OLTP=OnlineTransactionProcessing)
該類(lèi)型的應(yīng)用是高吞吐量，插入、更新、刪除操作比較多的系統(tǒng)，這些系統(tǒng)以不斷增長(zhǎng)的大容量數(shù)據(jù)為特征，它們提供給成百用戶同時(shí)存取。OTLP的主要目標(biāo)是可用性、速度、并發(fā)性和可恢復(fù)性。
當(dāng)設(shè)計(jì)這類(lèi)系統(tǒng)時(shí)，必須確保大量的并發(fā)用戶不能干擾系統(tǒng)的性能。還需要避免使用過(guò)量的索引與Cluster表，因?yàn)檫@些結(jié)構(gòu)會(huì)使插入和更新操作變慢。
此種類(lèi)型的應(yīng)用程序把吞吐量定義為性能指標(biāo)。
2.2決策支持系統(tǒng)(DSS=DecisionSupportSystem)
該類(lèi)型的應(yīng)用將大量信息進(jìn)行提取形成報(bào)告，協(xié)助決策者作出正確的判斷。典型的情況是:決策支持系統(tǒng)將OLTP應(yīng)用收集的大量數(shù)據(jù)進(jìn)行查詢(xún)。典型的應(yīng)用為客戶行為分析系統(tǒng)。
決策支持的關(guān)鍵目標(biāo)是速度、精確性和可用性。
該種類(lèi)型的設(shè)計(jì)往往與OLTP設(shè)計(jì)的理念背道而馳，一般建議使用數(shù)據(jù)冗余、大量索引、Cluster表、并行查詢(xún)等。
此種類(lèi)型的應(yīng)用程序把響應(yīng)時(shí)間定義為性能指標(biāo)。
用戶的應(yīng)用系統(tǒng)屬于典型的決策支持系統(tǒng)，用戶需求的關(guān)鍵就是盡量減少應(yīng)用程序的查詢(xún)響應(yīng)時(shí)間。
3DML語(yǔ)句的處理原理
用戶的應(yīng)用系統(tǒng)的主要工作就是使用DML語(yǔ)句對(duì)數(shù)據(jù)庫(kù)進(jìn)行查詢(xún)，要對(duì)SQL查詢(xún)語(yǔ)句進(jìn)行優(yōu)化，就必須對(duì)DML語(yǔ)句的處理原理有所了解。
DML語(yǔ)句的處理一般分9步：
第1步：創(chuàng)建游標(biāo)(Create a Cursor)
由程序接口調(diào)用創(chuàng)建一個(gè)游標(biāo)(Cursor)。任何SQL語(yǔ)句都會(huì)創(chuàng)建它，特別在運(yùn)行DML語(yǔ)句時(shí)，都是自動(dòng)創(chuàng)建游標(biāo)的，不需要開(kāi)發(fā)人員干預(yù)。多數(shù)應(yīng)用中，游標(biāo)的創(chuàng)建是自動(dòng)的。然而，在預(yù)編譯程序Pro*C)中游標(biāo)的創(chuàng)建，可能是隱含的，也可能顯式的創(chuàng)建。在存儲(chǔ)過(guò)程中也是這樣的。
第2步：分析語(yǔ)句(Parse the Statement)
在語(yǔ)法分析期間，SQL語(yǔ)句從用戶進(jìn)程傳送到Oracle，
SQL語(yǔ)句經(jīng)語(yǔ)法分析后，SQL語(yǔ)句本身與分析的信息都被裝入到共享SQL區(qū)。在該階段中，可以解決許多類(lèi)型的錯(cuò)誤。
語(yǔ)法分析分別執(zhí)行下列操作：翻譯SQL語(yǔ)句，驗(yàn)證它是合法的語(yǔ)句，即書(shū)寫(xiě)正確；實(shí)現(xiàn)數(shù)據(jù)字典的查找，以驗(yàn)證是否符合表和列的定義；今在所要求的對(duì)象上獲取語(yǔ)法分析鎖，使得在語(yǔ)句的語(yǔ)法分析過(guò)程中不改變這些對(duì)象的定義；驗(yàn)證為存取所涉及的模式對(duì)象所需的權(quán)限是否滿足；決定此語(yǔ)句最佳的執(zhí)行計(jì)劃；將它裝入共享SQL區(qū)；對(duì)分布的語(yǔ)句來(lái)說(shuō)，把語(yǔ)句的全部或部分路由到包含所涉及數(shù)據(jù)的遠(yuǎn)程節(jié)點(diǎn)。
以上任何一步出現(xiàn)錯(cuò)誤，都將導(dǎo)致語(yǔ)句報(bào)錯(cuò)，中止執(zhí)行。
只有在共享池中不存在等價(jià)SQL語(yǔ)句的情況下，才對(duì)SQL語(yǔ)句作語(yǔ)法分析。在這種情況下，數(shù)據(jù)庫(kù)內(nèi)核重新為該語(yǔ)句分配新的共享SQL區(qū)，并對(duì)語(yǔ)句進(jìn)行語(yǔ)法分析。進(jìn)行語(yǔ)法分析需要耗費(fèi)較多的資源，所以要盡量避免進(jìn)行語(yǔ)法分析。
語(yǔ)法分析階段包含了不管此語(yǔ)句將執(zhí)行多少次，而只需分析一次的處理要求。Oracle只對(duì)每個(gè)SQL語(yǔ)句翻譯一次，在以后再次執(zhí)行該語(yǔ)句時(shí)，只要該語(yǔ)句還在共享SQL區(qū)中，就可以避免對(duì)該語(yǔ)句重新進(jìn)行語(yǔ)法分析，也就是此時(shí)可以直接使用其對(duì)應(yīng)的執(zhí)行計(jì)劃對(duì)數(shù)據(jù)進(jìn)行存取。這主要是通過(guò)綁定變量(Bind Variable)實(shí)現(xiàn)的，也就是共享SQL。
SQL查詢(xún)語(yǔ)句與其它類(lèi)型的SQL語(yǔ)句不同，因?yàn)樵诔晒?zhí)行后作為結(jié)果將返回?cái)?shù)據(jù)。其它語(yǔ)句只是簡(jiǎn)單地返回成功或失敗，而查詢(xún)則能返回一行或許多行數(shù)據(jù)。查詢(xún)的結(jié)果均采用表格形式，結(jié)果行被一次一行或者批量地被檢索出來(lái)。從這里可以得知批量的Fetch數(shù)據(jù)可以降低網(wǎng)絡(luò)開(kāi)銷(xiāo)，所以批量的Fetch也是優(yōu)化的技巧之一。
第3步：描述查詢(xún)結(jié)果(Describe Results of a Query)
描述階段只有在查詢(xún)結(jié)果的各個(gè)列是未知時(shí)才需要；例如，當(dāng)查詢(xún)由用戶交互地輸入需要輸出的列名。在這種情況要用描述階段來(lái)決定查詢(xún)結(jié)果的特征(數(shù)據(jù)類(lèi)型，長(zhǎng)度和名字)。
第4步：定義查詢(xún)的輸出數(shù)據(jù)(Define Output of a Query)
在查詢(xún)的定義階段，指定與查詢(xún)出的列值對(duì)應(yīng)的接收變量的位置、大小和數(shù)據(jù)類(lèi)型，這樣可通過(guò)接收變量就可以得到查詢(xún)結(jié)果。如果必要的話，Oracle會(huì)自動(dòng)實(shí)現(xiàn)數(shù)據(jù)類(lèi)型的轉(zhuǎn)換。這是將接收變量的類(lèi)型與對(duì)應(yīng)的列類(lèi)型相比較決定的。
第5步：綁定變量(Bind Any Variables)
此時(shí)，Oracle知道了SQL語(yǔ)句的意思，但仍沒(méi)有足夠的信息用于執(zhí)行該語(yǔ)句。Oracle需要得到在語(yǔ)句中列出的所有變量的值。得到這個(gè)值的過(guò)程就叫綁定變量(binding variables)
因?yàn)橹付俗兞棵?，?dāng)再次執(zhí)行之前無(wú)須重新捆綁變量。而只需改變綁定變量的值，而Oracle在每次執(zhí)行時(shí)，僅僅使用內(nèi)存地址來(lái)查找此值。
第6步：并行執(zhí)行語(yǔ)句(Parallelize the Statement)
Oracle可以在SELECT，INSERT，UPDATE，MERGE，DELETE語(yǔ)句中執(zhí)行相應(yīng)并行查詢(xún)操作，對(duì)于某些DDL操作，如創(chuàng)建索引、用子查詢(xún)創(chuàng)建表、在分區(qū)表上的操作，也可以執(zhí)行并行操作。并行化可以導(dǎo)致多個(gè)服務(wù)器進(jìn)程(Oracle Server Processes)為同一個(gè)SQL語(yǔ)句工作，使該SQL語(yǔ)句可以快速完成，但是會(huì)耗費(fèi)更多的資源，所以除非很有必要，否則不要使用并行查詢(xún)。
第7步：執(zhí)行語(yǔ)句(Run the Statement)
到了現(xiàn)在這個(gè)時(shí)候，Oracle擁有所有需要的信息與資源，因此可以真正運(yùn)行SQL語(yǔ)句了。如果該語(yǔ)句為SELECT查詢(xún)或工INSERT語(yǔ)句，則不需要鎖定任何行，因?yàn)闆](méi)有數(shù)據(jù)需要被改變。然而，如果語(yǔ)句為UPDATE或DELETE語(yǔ)句，則該語(yǔ)句影響的所有行都被鎖定，防止該用戶提交或回滾之前，別的用戶對(duì)這些數(shù)據(jù)進(jìn)行修改。這保證了數(shù)據(jù)的一致性。
第8步：取出查詢(xún)的行(Fetch Rows of a Query)
在Fetch階段，行數(shù)據(jù)被取出來(lái)，每個(gè)后續(xù)的存取操作檢索結(jié)果集中的下一行數(shù)據(jù)，直到最后一行被取出來(lái)。上面提到過(guò)，批量的Fetch是優(yōu)化的技巧之一。
第9步：關(guān)閉游標(biāo)(Close the Cursor)
SQL語(yǔ)句處理的最后一個(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(簡(jiǎn)稱(chēng)RBO)；Oracle在基于規(guī)則的優(yōu)化器中采用啟發(fā)式的方法(Heuristic Approach)或規(guī)則(Rules)來(lái)生成執(zhí)行計(jì)劃。
基于成本的優(yōu)化器一Cost Based Optimization(簡(jiǎn)稱(chēng)CBO)：Oracle把一個(gè)成本引擎(CostEngine)集成到數(shù)據(jù)庫(kù)內(nèi)核中，用來(lái)估計(jì)每個(gè)執(zhí)行計(jì)劃需要的成本，該成本將每個(gè)執(zhí)行計(jì)劃所耗費(fèi)的資源進(jìn)行量化，從而CBO可以根據(jù)這個(gè)成本選擇出最優(yōu)的執(zhí)行計(jì)劃。一個(gè)查詢(xún)耗費(fèi)的資源可以被分成3個(gè)基本組成部分：I/O成本、CPU成本、Network成本。
數(shù)據(jù)庫(kù)使用何種優(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)，以便可以使用最少的資源完成語(yǔ)句。
FIRST_Rows為CBO優(yōu)化器使用的第二種具體的優(yōu)化方法，是以數(shù)據(jù)的響應(yīng)時(shí)間為主要目標(biāo)，以便快速查詢(xún)出開(kāi)始的幾行數(shù)據(jù)。
5執(zhí)行計(jì)劃
在執(zhí)行一個(gè)DML語(yǔ)句之前，Oracle生成一個(gè)決定SQL語(yǔ)句將如何執(zhí)行的查詢(xún)執(zhí)行計(jì)劃。計(jì)劃生成過(guò)程使用了各種不同信息，包括：生成的統(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語(yǔ)句
為了不重復(fù)解析相同的SQL語(yǔ)句(因?yàn)榻馕霾僮鞅容^費(fèi)資源，會(huì)導(dǎo)致性能下降)，在第一次解析之后，Oracle將SQL語(yǔ)句及解析后得到的執(zhí)行計(jì)劃存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域SGA的共享存儲(chǔ)區(qū)中的內(nèi)存可以被所有的數(shù)據(jù)庫(kù)用戶共享。因此，當(dāng)再次執(zhí)行一個(gè)SQL語(yǔ)句時(shí)，如果該語(yǔ)句和之前的執(zhí)行過(guò)的某一語(yǔ)句完全相同，并且之前執(zhí)行的該語(yǔ)句與其執(zhí)行計(jì)劃仍然在內(nèi)存中存在，則Oracle就不需要再進(jìn)行分析，直接得到該語(yǔ)句的執(zhí)行路徑。Oracle的這個(gè)功能大大地提高了SQL的執(zhí)行性能并大大節(jié)省了內(nèi)存的使用。使用這個(gè)功能的關(guān)鍵是將執(zhí)行過(guò)的語(yǔ)句盡可能放到內(nèi)存中，所以這要求有大的共享池和盡可能的使用綁定變量的方法執(zhí)行SQL語(yǔ)句。
7ROWID
ROWID是一個(gè)偽列，既然是偽列，那么這個(gè)列就不是用戶定義，而是系統(tǒng)自己給加上的。對(duì)每個(gè)表都有一個(gè)ROWID的偽列，但是表中并不物理存儲(chǔ)ROWID列的值。
不過(guò)你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對(duì)該列的值進(jìn)行修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫(kù)，則ROWID在該行的生命周期內(nèi)是唯一的，即即使該行產(chǎn)生行遷移，行的ROWID也不會(huì)改變。
8HINTS
HINTS是Oracle提供的一種機(jī)制，用來(lái)使優(yōu)化器按照HINTS生成執(zhí)行計(jì)劃。HINTS可以實(shí)現(xiàn)：
(1) 使用的優(yōu)化器的類(lèi)型；
(2) 基于代價(jià)的優(yōu)化器的優(yōu)化目標(biāo)，是ALL_Rows還是FIRST_Rows；
(3) 表的訪問(wèn)路徑，是全表掃描，還是索引掃描，還是直接利用ROWID；
(4) 表之間的連接類(lèi)型；
(5) 表之間的連接順序；
(6) 語(yǔ)句的并行程度。
除了RULES提示外，一旦使用的別的提示，語(yǔ)句就會(huì)自動(dòng)的改為使用CBO優(yōu)化器，此時(shí)如果Oracle的數(shù)據(jù)字典中沒(méi)有統(tǒng)計(jì)數(shù)據(jù)，就會(huì)使用缺省的統(tǒng)計(jì)數(shù)據(jù)。所以在使用CBO或HINTS提示，最好對(duì)表和索引進(jìn)行定期的分析。
9結(jié)束語(yǔ)
在數(shù)據(jù)庫(kù)應(yīng)用開(kāi)發(fā)過(guò)程中，優(yōu)化設(shè)計(jì)可提高數(shù)據(jù)庫(kù)性能，特別是大型數(shù)據(jù)庫(kù)，優(yōu)化過(guò)程更為重要 當(dāng)然數(shù)據(jù)庫(kù)性能優(yōu)化是一個(gè)復(fù)雜過(guò)程，本文只是對(duì)數(shù)據(jù)庫(kù)中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ù)庫(kù)設(shè)計(jì)優(yōu)化與實(shí)踐[J]. 電訊技術(shù),2001(1).
[3] 閆偉,張喜民,張軍華. Oracle數(shù)據(jù)庫(kù)及應(yīng)用程序優(yōu)化[J]. 內(nèi)蒙古石油化工,2005(1).
[4] 張韜. 淺析Oracle數(shù)據(jù)庫(kù)的性能優(yōu)化[J]. 中國(guó)科技信息,2005(5).
[1]
聯(lián)系客服
