基于Oracle數(shù)據(jù)庫(kù)的SQL語(yǔ)句優(yōu)化

李展?jié)苡⒅?/p>

(桂林理工大學(xué) 信息科學(xué)與工程學(xué)院，廣西 桂林 541004)

隨著信息化技術(shù)在各行業(yè)的廣泛應(yīng)用，Oracle數(shù)據(jù)庫(kù)也越來越多地被使用到很多關(guān)鍵領(lǐng)域，成為國(guó)內(nèi)高端數(shù)據(jù)庫(kù)市場(chǎng)的主流產(chǎn)品和眾多行業(yè)信息化系統(tǒng)的主要支柱。如何充分利用Oracle的各種功能來提高數(shù)據(jù)庫(kù)的可用性，如何提高數(shù)據(jù)庫(kù)的數(shù)據(jù)查詢響應(yīng)時(shí)間以及如何診斷數(shù)據(jù)庫(kù)出現(xiàn)的問題已經(jīng)成為不斷提高Oracle應(yīng)用水平和提高Oracle數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)性能的關(guān)鍵[1]。

1 SQL查詢過程及優(yōu)化器

1.1 SQL查詢語(yǔ)句的執(zhí)行過程[2]

查詢優(yōu)化最重要的就是對(duì)SQL語(yǔ)句進(jìn)行優(yōu)化。調(diào)整SQL對(duì)性能的改善要比調(diào)整其他方面明顯得多。理解SQL語(yǔ)句的執(zhí)行過程有助于更好地對(duì)其進(jìn)行優(yōu)化。SQL語(yǔ)句在Oracle中是自動(dòng)執(zhí)行的，絕大多數(shù)用戶不需要關(guān)心各個(gè)階段的執(zhí)行細(xì)節(jié)。但是，對(duì)執(zhí)行的各個(gè)階段的了解會(huì)有助于快速找到性能低下的SQL語(yǔ)句，幫助書寫出更高效的SQL語(yǔ)句，進(jìn)而解決問題。幾乎所有的SQL語(yǔ)句都分為語(yǔ)法分析、執(zhí)行、讀取數(shù)據(jù)三大階段進(jìn)行處理[3]。SQL查詢語(yǔ)句的執(zhí)行過程如圖1所示。

1.2 Oracle查詢優(yōu)化器[4]

SQL是一種非過程化的語(yǔ)言，用戶只需要發(fā)送取出數(shù)據(jù)的命令，對(duì)于數(shù)據(jù)的取出方式(如是通過索引還是全表掃描)，則由數(shù)據(jù)庫(kù)的優(yōu)化器決定。Oracle的優(yōu)化器用來決定SQL訪問數(shù)據(jù)的有效路徑，使語(yǔ)句執(zhí)行所需要的開銷最小。在Oracle的發(fā)展過程中，一共開發(fā)過兩類優(yōu)化器：基于規(guī)則的優(yōu)化器和基于成本的優(yōu)化器。它們之間的不同之處主要在于取得代價(jià)的方法與衡量代價(jià)的大小不同。

圖1 SQL查詢語(yǔ)句執(zhí)行圖

1.3 SQL查詢語(yǔ)句的執(zhí)行計(jì)劃

Oracle要實(shí)現(xiàn)許多步驟才能完成SQL查詢語(yǔ)句的執(zhí)行，優(yōu)化器將這些步驟組合在一起稱為SQL查詢語(yǔ)句的執(zhí)行計(jì)劃。從執(zhí)行計(jì)劃中可以看出數(shù)據(jù)庫(kù)是如何執(zhí)行查詢語(yǔ)句的，判斷出查詢語(yǔ)句的執(zhí)行是否高效，從而制定查詢的優(yōu)化方案。獲取執(zhí)行計(jì)劃的方法有以下兩種：(1)用Explain plan命令對(duì)語(yǔ)句的執(zhí)行過程的一些信息進(jìn)行統(tǒng)計(jì)，Explain plan用來顯示優(yōu)化器使用的執(zhí)行計(jì)劃而不實(shí)際運(yùn)行查詢；(2)用Set Autotrace動(dòng)態(tài)查看每個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃，Autotrace可以查看會(huì)話中每個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃。SQL自動(dòng)地進(jìn)行Explain plan的工作，不用維護(hù)plan table表，因此使用非常方便。

2 系統(tǒng)優(yōu)化措施

以具體的實(shí)例來說明系統(tǒng)優(yōu)化問題以及調(diào)整方法。在某電子產(chǎn)品售后服務(wù)系統(tǒng)中，為加強(qiáng)對(duì)售后維修點(diǎn)備件使用情況的精確管理，庫(kù)房發(fā)貨人員對(duì)出庫(kù)的每件備件粘貼一個(gè)唯一的一式兩聯(lián)條碼，一聯(lián)粘貼在發(fā)出的好備件上，另一聯(lián)粘貼到從用戶那里返回的壞備件上。發(fā)貨業(yè)務(wù)和備件條碼管理有關(guān)的E-R圖如圖2所示。

圖2 發(fā)貨業(yè)務(wù)E-R圖

這個(gè)系統(tǒng)中有一個(gè)查詢出庫(kù)信息詳單的視圖，該視圖在系統(tǒng)運(yùn)行初期的查詢速度較快，但隨著時(shí)間推移，數(shù)據(jù)量增加，其中有些表的數(shù)據(jù)量已達(dá)20萬(wàn)行以上，導(dǎo)致該視圖的查詢速度明顯變慢，而由于資金等各方面的原因，短期內(nèi)很難從硬件方面對(duì)系統(tǒng)進(jìn)行升級(jí)。因此決定在其運(yùn)行的Oracle 9i平臺(tái)上進(jìn)行優(yōu)化。在進(jìn)行優(yōu)化前，該視圖的查詢時(shí)間為1′07″左右，運(yùn)行的硬件環(huán)境為：P42.66、IGB內(nèi)存、240 GB普通IDE硬盤。在SQL*Plus中優(yōu)化前的運(yùn)行時(shí)間如圖3所示。

圖3 優(yōu)化前運(yùn)行時(shí)間

2.1 優(yōu)化SQL語(yǔ)句

2.1.1 分析SQL語(yǔ)句的執(zhí)行計(jì)劃

T1、T2、T3都是大表，且在 T1表上一個(gè)組合索引：T1(C1，C2)，注意C1列為索引的引導(dǎo)列。對(duì)于查詢：：Select T1.C4 from T1，T2，T3 where T2.C4=6 and T1.C1=T2.C1 and T1.C2=T3.C2 and T3.C3=7，跟蹤該查詢的執(zhí)行計(jì)劃如圖4所示。

圖4 查詢的執(zhí)行計(jì)劃

分析圖4查詢計(jì)劃，找出各個(gè)表之間的關(guān)聯(lián)關(guān)系，從而得到執(zhí)行計(jì)劃中哪個(gè)表為驅(qū)動(dòng)表。在執(zhí)行計(jì)劃中，需要知道哪個(gè)操作是先執(zhí)行的，哪個(gè)操作是后執(zhí)行的，這對(duì)于判斷哪個(gè)表為驅(qū)動(dòng)表有用處。

執(zhí)行計(jì)劃的第3列，即字母部分，每列值的左面有空格作為縮進(jìn)字符。在該列值左邊的空格越多，說明該列值的縮進(jìn)越多，該列值也越靠右。如圖4的執(zhí)行計(jì)劃所示：第一列值為6的行的縮進(jìn)最多，即該行最靠右；第一列值為4、5的行的縮進(jìn)一樣，其靠右的程度也一樣，但是第一列值為4的行比第一列值為5的行靠上；在上下關(guān)系方面，只對(duì)連續(xù)的、縮進(jìn)一致的行有效。對(duì)于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS(FULL)OF’T2’，所以這一操作先執(zhí)行，該操作對(duì)應(yīng)的T2表為第一個(gè)驅(qū)動(dòng)表(外部表)，T1表即為內(nèi)部表。T2與T1表做嵌套循環(huán)后生成了新的row source，對(duì)該row source進(jìn)行排序后，與T3表對(duì)應(yīng)的排了序的row source(應(yīng)用了 T3.C3=7限制條件)進(jìn)行 MERGEJOIN連接操作。所以由此可以得出如下事實(shí)：T2表先與T1表做嵌套循環(huán)，然后將生成的row source與T3表做排序合并連接。通過分析上面的執(zhí)行計(jì)劃，不能認(rèn)為T3表一定在T1、T2表之后才被讀取，事實(shí)上，T2表有可能與 T3表同時(shí)被讀入內(nèi)存，因?yàn)閷⒈碇械臄?shù)據(jù)讀入內(nèi)存的操作可能為并行的。

事實(shí)上許多操作可能為交叉進(jìn)行，因?yàn)镺racle讀取數(shù)據(jù)時(shí)，如果就是需要一行數(shù)據(jù)也是將該行所在的整個(gè)數(shù)據(jù)塊讀入內(nèi)存，而且還有可能為多塊讀?？磮?zhí)行計(jì)劃時(shí)，其關(guān)鍵不是看哪個(gè)操作先執(zhí)行，哪個(gè)操作后執(zhí)行，而關(guān)鍵是看表之間連接的順序 (如需知道哪個(gè)為驅(qū)動(dòng)表，這需要從操作的順序進(jìn)行判斷)、使用了何種類型的關(guān)聯(lián)及具體的存取路徑(如判斷是否利用了索引)，在從執(zhí)行計(jì)劃中判斷出哪個(gè)表為驅(qū)動(dòng)表后，根據(jù)掌握的知識(shí)判斷該表作為驅(qū)動(dòng)表。在這個(gè)例子中，T2為驅(qū)動(dòng)表，表的連接順序?yàn)?(T2->T1)->T3，查詢的過程中也使用到了T1表中的索引，因此，Oracle優(yōu)化器對(duì)其進(jìn)行的優(yōu)化效果是比較好的。如果分析了執(zhí)行計(jì)劃發(fā)現(xiàn)不合適，就要對(duì)SQL語(yǔ)句進(jìn)行更改，或用Oracle提供的提示(Hints)使優(yōu)化器可以選擇正確的驅(qū)動(dòng)表，以更為合理的順序進(jìn)行表的連接。

2.1.2 使用提示(Hints)干預(yù)執(zhí)行計(jì)劃[5]

基于成本的優(yōu)化器智能化程度很高，絕大多數(shù)情況下它能對(duì)SQL進(jìn)行合理地優(yōu)化，減輕了DBA的負(fù)擔(dān)。但有時(shí)受到一些因素的影響，優(yōu)化器也會(huì)選擇很差的執(zhí)行計(jì)劃，使某個(gè)語(yǔ)句的執(zhí)行變得奇慢無(wú)比。此時(shí)就需要DBA進(jìn)行人為的干預(yù)，告訴優(yōu)化器使用所指定的存取路徑或連接類型生成執(zhí)行計(jì)劃，從而使語(yǔ)句高效地運(yùn)行。例如，如果認(rèn)為對(duì)于一個(gè)特定的語(yǔ)句，執(zhí)行全表掃描要比執(zhí)行索引掃描更有效，則就可以指示優(yōu)化器使用全表掃描。在Oracle中，是通過為語(yǔ)句添加 Hints(提示)來實(shí)現(xiàn)干預(yù)優(yōu)化器優(yōu)化的目的。Hints是Oracle提供的一種機(jī)制，用來告訴優(yōu)化器按照技術(shù)人員告訴它的方式生成執(zhí)行計(jì)劃：

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

(2)基于代價(jià)的優(yōu)化器的優(yōu)化目標(biāo)，是all_rows還是first_rows。

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

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

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

(6)語(yǔ)句的并行程度。

Hints只應(yīng)用在其所在SQL語(yǔ)句塊上，對(duì)其他SQL語(yǔ)句或語(yǔ)句的其他部分沒有影響。除了“RULE”提示外，一旦使用別的提示，語(yǔ)句就會(huì)自動(dòng)地改為使用CBO優(yōu)化器，此時(shí)如果數(shù)據(jù)字典中沒有統(tǒng)計(jì)數(shù)據(jù)，就會(huì)使用缺省的統(tǒng)計(jì)數(shù)據(jù)。所以如果使用CBO或Hints提示，則最好對(duì)表和索引進(jìn)行定期的分析。

對(duì)于表的訪問，可以使用兩種Hints：FULL和ROWID。FULL提示告訴Oracle使用全表掃描的方式訪問指定表。例如：

索引Hints告訴Oracle使用基于索引的掃描方式，不必說明具體的索引名稱。例如：

使用Hints對(duì)Oracle 優(yōu)化器缺省的執(zhí)行路徑進(jìn)行手工修改是一個(gè)很有技巧性的工作，一般建議只針對(duì)特定的、少數(shù)的SQL進(jìn)行Hints的優(yōu)化。絕大多數(shù)情況下，只要SQL書寫規(guī)范，Oracle查詢優(yōu)化器的工作情況是相當(dāng)理想的。Hints提示雖然能帶來一些方便，但是不能濫用，因?yàn)檫@種方法過于復(fù)雜，缺乏必要的通用性和應(yīng)變能力，同時(shí)增加了維護(hù)上的代價(jià)。

調(diào)整SQL語(yǔ)句后的查詢時(shí)間圖5所示。

圖5 調(diào)整SQL語(yǔ)句后的查詢時(shí)間

目前數(shù)據(jù)庫(kù)規(guī)模越來越大，數(shù)據(jù)量呈指數(shù)級(jí)上升，使數(shù)據(jù)庫(kù)的性能越來越重要。Oracle數(shù)據(jù)庫(kù)內(nèi)部結(jié)構(gòu)復(fù)雜，影響系統(tǒng)性能因素較多，但在系統(tǒng)硬件不變的情況下，SQL語(yǔ)句的優(yōu)化是性能得以提高的根本。但優(yōu)化并不能一勞永逸，隨著表結(jié)構(gòu)的改變和數(shù)據(jù)量的增加，優(yōu)化也必須實(shí)時(shí)調(diào)整。

[1]卞榮兵.基于ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化的研究[J].應(yīng)用技術(shù)，2002(9)：36-38.

[2]鐘小權(quán).Oracle數(shù)據(jù)庫(kù)的SQL語(yǔ)句優(yōu)化[J].計(jì)算機(jī)與現(xiàn)代化，2011(3).124-126.

[3]谷小秋，李德昌.索引調(diào)整優(yōu)化 oracle 9i工作性能的研究[J].計(jì)算機(jī)工程與應(yīng)用，2005，26：174-176.

[4]路川.Oracle 10g寶典[M].北京：電子工業(yè)出版社，2009.

[5]仇道霞.Oracle數(shù)據(jù)庫(kù)性能調(diào)整優(yōu)化 [J].山東輕工業(yè)學(xué)報(bào)，2010，24(3).52-54.