Oracle數據庫中大型表查詢優化研究

2024-08-29 13:50:44

字體：大中小

來源：轉載

供稿：網友

摘　要：對海量數據進行訪問查詢時，經常碰到系統響應時間過長，占用系統資源過多的問題。本文結合實例著重對Oracle數據庫中的查詢優化進行了研究，測試結果表明采用的方法是很有效的，大大縮短了測試用例表的響應時間，最后對海量數據的優化方法提出了實用性的建議。要害詞：海量數據；Oracle數據庫；查詢優化；數據查詢。 1　引　言在直升機飛行地面數據處理平臺中，需要查詢歷史飛行數據來進行飛行狀態的模擬及其飛行事故的分析，從而對當前飛機狀態進行評判。其數據量非常巨大。如何對其進行快速訪問，提高系統響應時間就顯得十分重要。在實際應用中，往往采用各種優化措施，使得SQL查詢經過數據庫優化器的處理，得到最佳的執行計劃，即數據訪問路徑，來達到提高響應速度的目的。由于項目采用的是Oracle數據庫，以下考慮對Oracle數據庫進行的優化情況。 2　Oracle查詢順序及其調整 Oracle優化的一般順序如下：環境調整（服務器、網絡、磁盤）、Oracle實例調整、Oracle對象調整、Oracle SQL調整。我們的測試采用的Oracle數據表容量為50 M，記錄條數為50萬條。測試方法為：根據用戶的查詢要求計算得到用戶需要瀏覽的數據記錄的起止位置，然后在SQL語句中加入此位置，執行SQL語句，查詢該數據表，得到用戶想要瀏覽的記錄集合。使用的SQL語句如下：

　　SELECT＊FROM（SELECT＊FROM（SELECT＊FROM BIG） WHERE ROWNUM＜TOPOS ORDER BYROWNUM DESC）WHERE ROWNUM＜TOPOSFROMPOS＋1；

使用該SQL查詢得到結果集需要5～6 s，這個響應速度難以滿足用戶瀏覽要求，因此必須對其優化以提高響應速度。我們是在假定環境調整已經完成的條件下通過對Oracle實例、對象、SQL查詢語句的調整得出結論的，其中重點是對SQL語句的調整。 2．1　實例調整首先進行Oracle實例調整。Oracle實例涉及到SGA內存區和一組Oracle后臺處理進程。對Oracle實例的調整就是對SGA內存區和Oracle后臺處理進程的調整。在對該問題的解決中，主要是針對SGA內存區的調整。 2．1．1　SGA內存區結構 SGA就是系統全局區，是指內存中答應多個進程相互通信的區域。在Oracle中，SGA對所有進程來說都是全局的可用的。圖1為SGA結構圖。緩沖區高速緩存是SGA中為所有用戶和系統進程保存數據的區域，任何數據在傳遞給一個調用的應域是共享的，所以多個進程可以從這片高速緩存讀取同樣的數據塊，而不必每次都從物理磁盤中讀取。

　　共享池是SGA中的另一個區域，其中保存著關于待執行的SQL語句的信息。他由兩部分組成：數據字典高速緩存，存放從數據字典中讀取的信息以用于處理SQL請求；庫高速緩存，存放需要執行的SQL語句信息，包括每個SQL語句的語法分析樹和執行計劃。假如多個用戶要執行同樣的SQL語句，那么語法分析樹和執行計劃就可以重復利用，省去了語法分析步驟的昂貴花費。2．1．2　調整SGA結構一般來講，在系統硬件支持的情況下，系統全局區越大越有利于數據庫高效的運行。大的緩沖區高速緩存可以緩存更多的數據塊，這樣可以提高緩存命中率，節省物理磁盤讀取的高昂代價；大的共享池意味著大的庫高速緩存。庫緩存的內存結構如圖2所示。

　　庫緩存越大，可以保存的SQL語法分析信息越多；此外，數據庫中的一些對象，如表、索引、過程、觸發器、軟件包等也在首次執行后進駐庫高速緩存。大的庫緩存可以保證對這些對象的高命中率，從而節省解析和載入代價。作為一個通用的優化原則，我們在解決該問題時，適當增大了SGA的容量，從而保證Oracle實例可以比較高效的運行。設置緩沖區高速緩存的容量為32 M，設置共享池的大小為56 M。運行rpt＿lib．sql程序檢查庫高速緩存不足率（還沒有運行該腳本），表明庫緩存足夠。 2．2　對象調整這一步驟我們要對每一個Oracle對象進行調整從而優化性能，包括對所有的存儲參數進行正確的設置，尤其是對影響輸入輸出的參數進行設置。Pctfree，pctused，freelist參數的設置都會對SQL性能產生重要影響。在解決該問題時，我們沒有對對象的存儲參數進行調整。對象的存儲參數的調整要建立在具體對象的基礎之上，我們使用的測試大表除了在容量上模擬可能的實際對象之外，和實際對象并沒有多少可比之處。舉例來說，pctfree參數是指在實際的存儲塊中，留出來為塊中的記錄擴展所用的空閑空間占該存儲塊容量的百分比，這個參數和實際表記錄的長度及可擴展性有關，要在設計出實際表之后進行調整。因此我們對對象的存儲參數使用了數據庫的缺省設置。數據庫的對象還包括索引、過程、包等，對對象的調整要包括對他們的調整，這里一并略去。 2．3　SQL語句調整 SQL語言是一種靈活的語言，相同的功能可以使用不同的語句來實現，但是語句的執行效率是很不相同，一般考慮如下規則：（1）添加索引在對大表進行掃描時，首先要避免不必要的全表掃描。最通常的做法就是給大表添加索引。所謂全表掃描，就是在訪問表時，從磁盤上存儲該表的起始位置開始逐記錄讀數據，直到該表的結束位置。給大表添加索引后，我們可以通過訪問索引的方式獲得記錄的物理位置，從而達到訪問表的目的。設想一下，對于一個擁有大量字段的表，假如只需要返回其中少量字段，那么在這些字段上建立索引，通過索引訪問獲得記錄，將大大降低物理磁盤讀寫次數，從而降低了整個查詢響應時間（事實上，這種情況下根本沒有必要訪問數據表，只是訪問索引就足夠了）。在我們的測試用表中，只有4個字段，顯然無法發揮索引的這一優勢。我們的測試用表的特點是記錄條數多，達到50萬條，但每次返回的記錄數只有幾十條，在這種情況下，使用索引同樣可以起到好的效果。首先在檢索條件上建立索引，在表訪問時，我們通過索引來獲取目的記錄集的物理地址，因為返回的記錄數不多，所以這種方式造成的物理讀寫很少，應該可以獲得較滿足的訪問時間。在測試中，我們對測試用表的條件字段添加索引，但事實上并沒有提高查詢的響應時間，這是因為在SQL語句的執行計劃中，在添加索引之前和之后，Oracle生成的執行計劃中，表訪問方式都是全表掃描，根本沒有使用我們添加的索引。那么此時我們可以采用添加提示的方法。（2）添加提示在Oracle中，我們可以在SQL語句中加入提示，來影響Oracle對優化模式的選擇，從而生成最優的執行計劃。于是我們在SQL語句中加入提示，強制優化器在生成執行計劃時將表的訪問方式從全表掃描改為索引范圍掃描。結果該查詢花費了比全表掃描多得多的響應時間。仔細查看這個SQL語句的執行計劃，我們發現Oracle對這個索引進行了全索引掃描，這樣造成的物理讀寫數量比起全表掃描不僅沒有減少，反而大幅上升。尋找原因，問題出在我們索引的字段上。我們索引的AGE字段為了測試方便，每條記錄的值都不相同，這樣的字段其實并不具備建立索引的條件。在該測試中，我們首先要將索引載入內存，由于索引各不相同，Oracle采取了全索引掃描的方式訪問索引，我們要讀取的是30萬條記錄（AGE＞200 k），通過索引，我們獲得的將是30萬個記錄的ROWID，假如這些記錄在磁盤上的物理位置沒有按索引順序排序，那么我們就不得不通過30萬個ROWID來訪問這些記錄，這樣造成的物理讀寫是相當驚人的，這就是為什么我們使用了索引，卻反而查詢速度更慢的原因所在。事實上Oracle的優化器選擇全表訪問是已經對不同的執行計劃作過比較的了。同時，這里同樣違反了一個Oracle優化的準則，對于沒有按索引順序排序的表，假如檢索記錄數小于總記錄數的7％，用索引比全表掃描快。（3）更改優化器模式優化器是Oracle數據庫中接受來自產生器處理過的SQL語句的程序。他將對SQL進行優化，生成內部執行計劃，這個計劃是Oracle存取物理磁盤數據的路徑。根據優化器模式的不同，生成的內部執行計劃也不同，而對于相同的SQL，永遠只有一個最優的執行計劃，因此選擇合適的優化器模式，是很重要的。Oracle有2種優化器模式：基于規則的優化模式和基于成本的優化模式。在這次測試中，我們使用的優化器模式參數為CHOOSE，這樣Oracle將根據SQL語句相關表索引的有無，統計資料的有無以及SQL語句中的提示，自動判定使用哪種優化模式。（4）將調整持久化 Oracle的執行計劃是根據各種情況，比如表的統計資料變化的，但有時這種變化是我們不希望的。為了將我們已經調整好的SQL執行計劃固定，我們可以用Oracle的工具將執行計劃持久化存儲。 2．4　調整結果通過對這個測試用例的調整，我們最終將此大表的響應時間從5 s縮短到不足2 s。經過調整后的緩沖區高速緩存達到113 M，大約占物理內存的50％，調整后的SQL語句為：

　　SELECT name，age，memo FROM （SELECTname，age，memo，rownum ASmynum FROM test2） WHEREmynum＞400000 and mynum＜＝400010；

我們測試所用的機器配置為CELETRON500 M，內存256 M，硬盤為IDE8．4 G?？梢哉f是運行Oracle8I的最低配置。啟動Oracle 8I數據庫后，內存使用達到300 M以上，也就是說已經在用虛擬內存；測試開始后，CPU保持或接近滿載，這些都會影響Oracle的性能表現，也會影響測試結果。 3　結　語以下幾點可以作為通用的Oracle SQL調整原則：（1）消除不必要的全表掃描，可以通過添加索引達到。（2）緩存小型表的全表掃描，可以通過將小型表置入緩沖區高速緩存的KEEP池中實現。（3）假如表有多個索引，要保證Oracle正在使用對此SQL最優化的索引，可以通過添加提示實現。

上一篇：優化Oracle停機時間及數據庫恢復簡介

下一篇：關于Oracle服務器性能全面調整攻略

學習交流

如何查找有故障的配件

如何查找有故障的配件...

熱門圖片

猜你喜歡的新聞

猜你喜歡的關注