基于Oralce數據庫SQL查詢優化研究

張學義，王觀玉，黃 雋

ZHANG Xue-yi，WANG Guan-yu，HUANG Jun

（黔南民族師范學院 計算機科學系，都勻 558000）

0 引言

Oracle數據庫是當前應用最廣泛的大型數據庫，其查詢性能直接關系到系統的運行效率，對其查詢優化方法的研究更具有現實意義。隨著數據庫中數據的增加，系統的響應速度就成為目前系統需要解決的最主要的問題之一，劣質SQL語句和優質SQL語句之間的速度差別可以達到上百倍，可見對于一個系統不是簡單地能實現其功能就可，而是要寫出高質量的SQL語句，提高系統的可用性和降低系統的響應時間。傳統的Oracle SQL查詢優化方法[1]是使用索引來更快地遍歷表，優化器主要根據定義的索引來提高性能。但是，如果在SQL語句的where子句中寫的SQL代碼不合理，就會造成優化器略去索引而使用全表掃描，導致查詢效率低下。

針對上述情況，提出了幾種SQL查詢語句優化新策略：檢測分析影響系統響應速度的SQL語句、共享SQL語句、使用表的別名、數據緩沖區優化、共享池的優化、數據緩沖池的優化，使得執行SQL語句時優化器根據優化原則來合理使用索引，并盡可能減少磁盤I/O訪問獲取所需要的數據，提高查詢性能。在數據庫優化前后比較其評價指標：響應時間和吞吐量之間的權衡、數據庫的命中率以及內存的使用效率，并以此來衡量優化的效果和指導優化的方向。

1 Oracle優化原理

Oracle數據庫優化的主要目標就是減少磁盤I/O、減少CPU利用率和資源競爭，降低查詢響應時間或提高系統吞吐量。影響SQL性能的因素很多，如初始化參數設置不合理、導入了不準確的系統及模式統計數據從而影響優化程序（CBO）的正確判斷、未建立恰當的索引引起全表掃描、多表連接時過濾條件位置不當導致中間結果集包含了太多的無用記錄、未充分利用數據庫提供的查詢并行化處理。

磁盤I/O[2]是影響Oracle數據庫性能的瓶頸，主要原因有磁盤競爭、I/O讀取次數和數據塊空間的分配管理不當等。提高I/O設備的并發訪問率，可以有效提高SQL語句的執行效率。當競爭增強的時候，系統響應時間將增長。

用戶在編寫新的SQL語句，或者對應用程序中存在疑問的語句進行優化時，其基本步驟為查找最消耗資源的語句，對這些語句進行優化，使其占用更少的資源。利用SQL TRACE、SQL Analyze等工具，可以查出存在問題的SQL語句。

SQL處理體系結構由以下幾個主要組件組成：解析程序、字典、優化程序、SQL執行，如下圖1所示：

解析程序、優化程序共同組成了SQL編譯器。編譯器將SQL語句編譯成共享游標，并與查詢計劃相關聯。解析程序執行語法分析和語義分析。優化程序是SQL處理引擎的核心。Oracle數據庫支持兩種優化方法：基于規則的優化（RBO）和基于開銷的優化（CBO），本論文正是基于CBO方法設置優化程序的方法和目標，以及收集CBO的統計信息，對SQL語句進行查詢優化，獲得最佳吞吐量。

圖1 SQL處理系統結構

2 Oracle數據庫內存優化方法

要充分發揮Oracle數據庫的優勢，必須對數據庫的各項初始化參數進行合理配置[3]。從Oracle數據庫內存優化管理的角度出發，針對影響其性能的因素及其對應的參數，分別從數據緩沖區優化、共享池優化、重做日志緩沖區優化幾個方面完成內存優化配置。

SGA（系統全局區）是數據庫的工作區，它和Oracle進程結合組成一個Oracle數據庫實例，管理數據庫數據，應答用戶請求。SGA[4]有三個組成部分，即數據庫緩存區，共享池區以及日志緩存區，這些內存區域由初始化文件initSID.ora中相應的參數來配置，它們的性能效率也將受initSID.ora中各參數設置的影響。以DBA的身份連接到數據庫，通過執行下列語句來獲取SGA設置信息：SQL＞select * from v$sga，并做相應的調整。

1）共享池的優化

共享池（shared pool）包括庫高速緩存、數據字典高速緩存，衡量這兩個緩存區性能的指標主要是它們的命中率。共享池用LRU算法進行管理，保證頻繁使用的代碼和數據字典能夠存于共享池中。數據字典命中率查詢：SQL＞select（1 -（sum（getmisses）/sum（gets）））* 100“Hit Ratio” from v$rowcache；查詢結果Hit Ratio為98.25588424。

若共享池的庫高速緩存和數據字典命中率低于95%，則可增大initSID.ora中shared_ pool_size的值。

2）日志緩沖區的優化

日志緩沖區存儲數據庫的修改信息，大小由log_buffer確定，它必須是db_block_ size的整數倍。日志緩沖區的存在可以加快數據庫的操作速度，因為內存到內存的操作比內存到硬盤的速度快很多。SQL＞select n.name,gets,misses,immediate_gets，immediate_ misses from v$latch l,v$latchname n where n.name in（'redo allocation','redo copy'）and n.latch#=l.latch#；此查詢中redo allocation和redo copy的失敗率為0，如果計算結果大于1%，則需增大日志緩沖區的大小。

3 SQL查詢語句優化新策略

SQL語句尤其是復雜SQL語句的性能優化對于數據庫的性能是至關重要的。在集中式數據庫中，SQL查詢的執行總代價＝I/O代價＋CPU代價＋內存代價。調整影響其執行效率的三大因素來減少系統總代價：一是減少查詢所產生的I/O總次數；二是減少CPU的計算頻度，減少SQL語句中需要計算的量和參數；三是減少對系統內存的使用和占用時間。SQL語句優化的一般步驟如下圖2所示：

首先我們要檢測出不合理的SQL語句，首先要生成執行計劃，最簡單的辦法有兩種：一是SQL＞set autotrace on自動記錄執行計劃；二是explain plan for ‘SQL語句’，然后通過select * from table（dbms_xplan.display（））來查看執行計劃。第一種方法查看執行時間較長的SQL語句時，需要等待該語句執行成功后才返回執行計劃。

在第一次解析之后，Oracle將SQL語句存放位在SGA共享池中，為所有的數據庫用戶共享，大大地提高了SQL的執行性能并節省了內存。當用戶提交SQL語句時，服務器進程在共享池中查找有無該條語句，如果有就跳過語法分析等過程，節省了SQL語句的分析和編譯的開銷。只有在共享池中不存在等價SQL語句的情況下，才對該語句作語法分析，并為該語句分配新的共享SQL區。

圖2 SQL優化的一般步驟

SQL優化[6]的實質就是在結果正確的前提下，充分利用索引，減少表掃描的I/O次數，選擇最有效的執行計劃來執行SQL語句的過程。下面的查詢優化實例以EMP和DEPT表為查詢表，其中emp表有1204行記錄，dept有604行記錄。

1）select子句中避免使用*

Oracle在解析的過程中，通過查詢數據字典將*依次轉換成所有的列名，這將消耗更多的時間，降低了查詢速度。優化例1如下表1所示：

表1 select子句中＊ 優化

2）where子句中約束條件的順序

Oracle采用自下而上的順序解析where子句，因此表之間的連接必須寫在其他約束條件之前，將過濾掉最多記錄的條件寫在where子句的末尾，提高查詢效率。優化例2如下表2所示：

表2 where子句約束條件優化

3）用where子句替換having子句

where檢查每條記錄是否符合條件，通過其過濾條件減少系統開銷。having子句檢查group by后的各組是否滿足條件，而Where子句在匯總之前就減少參加匯總的數據量，從而提高查詢速度。如果having子句應用了匯總函數，則不能用Where代替。優化例3如下表3所示：

表3 group by和having優化

4）子查詢“展平”技術

子查詢“展平”指將子查詢轉變為連接、半連接，從而達到優化查詢的目的。SQL首先計算位于外層查詢的from子句中關系的笛卡爾積，然后對該笛卡爾積的每個元組用where子句中的謂詞進行過濾。因為子查詢要對應位于外層查詢的每一個元組進行單獨的計算，從而導致大量的磁盤I/O操作，所以在實際應用中，用連接查詢代替子查詢，提高查詢效率。

找出所有工資超過2000的雇員的那些部門編號和名稱。優化例5如下表4所示：

表4 子查詢展平優化

5）用union替換or（適用于索引列）

在where子句中or連接多個索引列，用union替換where子句中的or，可以顯著提高查詢效率。對索引列使用or將造成全表掃描，如果用or連接的列沒有被索引，查詢效率不會提高。union在進行表連接后，對所產生的結果集進行排序運算，篩選掉重復的記錄再返回結果。

4 結束語

本文從影響SQL性能的最主要的幾個方面入手，提出了從磁盤I/O、系統內存參數的調整和SQL查詢語句的優化新策略，算法可行，效率較高，可以在實際中推廣應用。實驗結果表明，SQL語句的優化使運算速度加快，有效減少執行時間，提高響應速度，優化效果理想，因此，本文提出的Oracle SQL查詢優化方法是一種有效的數據庫性能調優方法。

數據庫的性能調整是一個系統工程，需要在大量的實踐工作中不斷地積累經驗，結合上述各種優化技術，更好地進行數據庫調優，實現數據庫檢索性能的提高。

[1]Donald K.Burleson.劉硯，黃春，譯.Oracle高性能SQL調整[M].北京：機械工業出版社，2002.

[2]Y.Ionnidis and S.Christodoulakis.Optimal histograms for limiting worst-case error propagation in the size of join results[J].ACM TODS，1993，18（4）.

[3]趙慧勤,李秀蘭.Oracle數據庫應用系統的優化策略[J].計算機工程與應用，2003，27（3）.

[4]劉博.Oracle數據庫性能優化與調整[D].大連：大連理工大學，2007.

[5]谷小秋,李德昌.索引調整優化Oracle9i工作性能的研究[J].計算機工程與應用，2005，41（26）.

[6]劉光霆.ORACLE中SQL查詢優化研究[J].計算機與信息技術，2008，32（5）.

[7]吳超，沈為群，潘舜良，宋子善.某直升機工程飛行模擬器控制中心的研究與實現[J].計算機仿真，2006，23（9）：294-297.