基于Oracle數據庫的SQL語句優化

李展濤，曹英忠

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

隨著信息化技術在各行業的廣泛應用，Oracle數據庫也越來越多地被使用到很多關鍵領域，成為國內高端數據庫市場的主流產品和眾多行業信息化系統的主要支柱。如何充分利用Oracle的各種功能來提高數據庫的可用性，如何提高數據庫的數據查詢響應時間以及如何診斷數據庫出現的問題已經成為不斷提高Oracle應用水平和提高Oracle數據庫應用系統性能的關鍵[1]。

1 SQL查詢過程及優化器

1.1 SQL查詢語句的執行過程[2]

查詢優化最重要的就是對SQL語句進行優化。調整SQL對性能的改善要比調整其他方面明顯得多。理解SQL語句的執行過程有助于更好地對其進行優化。SQL語句在Oracle中是自動執行的，絕大多數用戶不需要關心各個階段的執行細節。但是，對執行的各個階段的了解會有助于快速找到性能低下的SQL語句，幫助書寫出更高效的SQL語句，進而解決問題。幾乎所有的SQL語句都分為語法分析、執行、讀取數據三大階段進行處理[3]。SQL查詢語句的執行過程如圖1所示。

1.2 Oracle查詢優化器[4]

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

圖1 SQL查詢語句執行圖

1.3 SQL查詢語句的執行計劃

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

2 系統優化措施

以具體的實例來說明系統優化問題以及調整方法。在某電子產品售后服務系統中，為加強對售后維修點備件使用情況的精確管理，庫房發貨人員對出庫的每件備件粘貼一個唯一的一式兩聯條碼，一聯粘貼在發出的好備件上，另一聯粘貼到從用戶那里返回的壞備件上。發貨業務和備件條碼管理有關的E-R圖如圖2所示。

圖2 發貨業務E-R圖

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

圖3 優化前運行時間

2.1 優化SQL語句

2.1.1 分析SQL語句的執行計劃

T1、T2、T3都是大表，且在 T1表上一個組合索引：T1(C1，C2)，注意C1列為索引的引導列。對于查詢：：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，跟蹤該查詢的執行計劃如圖4所示。

圖4 查詢的執行計劃

分析圖4查詢計劃，找出各個表之間的關聯關系，從而得到執行計劃中哪個表為驅動表。在執行計劃中，需要知道哪個操作是先執行的，哪個操作是后執行的，這對于判斷哪個表為驅動表有用處。

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

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

2.1.2 使用提示(Hints)干預執行計劃[5]

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

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

(2)基于代價的優化器的優化目標，是all_rows還是first_rows。

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

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

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

(6)語句的并行程度。

Hints只應用在其所在SQL語句塊上，對其他SQL語句或語句的其他部分沒有影響。除了“RULE”提示外，一旦使用別的提示，語句就會自動地改為使用CBO優化器，此時如果數據字典中沒有統計數據，就會使用缺省的統計數據。所以如果使用CBO或Hints提示，則最好對表和索引進行定期的分析。

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

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

使用Hints對Oracle 優化器缺省的執行路徑進行手工修改是一個很有技巧性的工作，一般建議只針對特定的、少數的SQL進行Hints的優化。絕大多數情況下，只要SQL書寫規范，Oracle查詢優化器的工作情況是相當理想的。Hints提示雖然能帶來一些方便，但是不能濫用，因為這種方法過于復雜，缺乏必要的通用性和應變能力，同時增加了維護上的代價。

調整SQL語句后的查詢時間圖5所示。

圖5 調整SQL語句后的查詢時間

目前數據庫規模越來越大，數據量呈指數級上升，使數據庫的性能越來越重要。Oracle數據庫內部結構復雜，影響系統性能因素較多，但在系統硬件不變的情況下，SQL語句的優化是性能得以提高的根本。但優化并不能一勞永逸，隨著表結構的改變和數據量的增加，優化也必須實時調整。

[1]卞榮兵.基于ORACLE數據庫性能優化的研究[J].應用技術，2002(9)：36-38.

[2]鐘小權.Oracle數據庫的SQL語句優化[J].計算機與現代化，2011(3).124-126.

[3]谷小秋，李德昌.索引調整優化 oracle 9i工作性能的研究[J].計算機工程與應用，2005，26：174-176.

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

[5]仇道霞.Oracle數據庫性能調整優化 [J].山東輕工業學報，2010，24(3).52-54.