摘　要：對海量數(shù)據(jù)進(jìn)行訪問查詢時，經(jīng)常碰到系統(tǒng)響應(yīng)時間過長，占用系統(tǒng)資源過多的問題。本文結(jié)合實例著重對Oracle數(shù)據(jù)庫中的查詢優(yōu)化進(jìn)行了研究，測試結(jié)果表明采用的方法是很有效的，大大縮短了測試用例表的響應(yīng)時間，最后對海量數(shù)據(jù)的優(yōu)化方法提出了實用性的建議。 要害詞：海量數(shù)據(jù)；Oracle數(shù)據(jù)庫；查詢優(yōu)化；數(shù)據(jù)查詢。 1　引　言 在直升機(jī)飛行地面數(shù)據(jù)處理平臺中，需要查詢歷史飛行數(shù)據(jù)來進(jìn)行飛行狀態(tài)的模擬及其飛行事故的分析，從而對當(dāng)前飛機(jī)狀態(tài)進(jìn)行評判。其數(shù)據(jù)量非常巨大。如何對其進(jìn)行快速訪問，提高系統(tǒng)響應(yīng)時間就顯得十分重要。在實際應(yīng)用中，往往采用各種優(yōu)化措施，使得SQL查詢經(jīng)過數(shù)據(jù)庫優(yōu)化器的處理，得到最佳的執(zhí)行計劃，即數(shù)據(jù)訪問路徑，來達(dá)到提高響應(yīng)速度的目的。由于項目采用的是Oracle數(shù)據(jù)庫，以下考慮對Oracle數(shù)據(jù)庫進(jìn)行的優(yōu)化情況。 2　Oracle查詢順序及其調(diào)整 Oracle優(yōu)化的一般順序如下：環(huán)境調(diào)整（服務(wù)器、網(wǎng)絡(luò)、磁盤）、Oracle實例調(diào)整、Oracle對象調(diào)整、Oracle SQL調(diào)整。我們的測試采用的Oracle數(shù)據(jù)表容量為50 M，記錄條數(shù)為50萬條。測試方法為：根據(jù)用戶的查詢要求計算得到用戶需要瀏覽的數(shù)據(jù)記錄的起止位置，然后在SQL語句中加入此位置，執(zhí)行SQL語句，查詢該數(shù)據(jù)表，得到用戶想要瀏覽的記錄集合。使 用的SQL語句如下： SELECT＊FROM（SELECT＊FROM（SELECT＊FROM BIG） WHERE ROWNUM＜TOPOS ORDER BYROWNUM DESC）WHERE ROWNUM＜TOPOSFROMPOS＋1；使用該SQL查詢得到結(jié)果集需要5～6 s，這個響應(yīng)速度難以滿足用戶瀏覽要求，因此必須對其優(yōu)化以提高響應(yīng)速度。我們是在假定環(huán)境調(diào)整已經(jīng)完成的條件下通過對Oracle實例、對象、SQL查詢語句的調(diào)整得出結(jié)論的，其中重點是對SQL語句的調(diào)整。 2．1　實例調(diào)整 首先進(jìn)行Oracle實例調(diào)整。Oracle實例涉及到SGA內(nèi)存區(qū)和一組Oracle后臺處理進(jìn)程。對Oracle實例的調(diào)整就是對SGA內(nèi)存區(qū)和Oracle后臺處理進(jìn)程的調(diào)整。在對該問題的解決中，主要是針對SGA內(nèi)存區(qū)的調(diào)整。 2．1．1　SGA內(nèi)存區(qū)結(jié)構(gòu) SGA就是系統(tǒng)全局區(qū)，是指內(nèi)存中答應(yīng)多個進(jìn)程相互通信的區(qū)域。在Oracle中，SGA對所有進(jìn)程來說都是全局的可用的。圖1為SGA結(jié)構(gòu)圖。 緩沖區(qū)高速緩存是SGA中為所有用戶和系統(tǒng)進(jìn)程保存數(shù)據(jù)的區(qū)域，任何數(shù)據(jù)在傳遞給一個調(diào)用的應(yīng)域是共享的，所以多個進(jìn)程可以從這片高速緩存讀取同樣的數(shù)據(jù)塊，而不必每次都從物理磁盤中讀取。 共享池是SGA中的另一個區(qū)域，其中保存著關(guān)于待執(zhí)行的SQL語句的信息。他由兩部分組成：數(shù)據(jù)字典高速緩存，存放從數(shù)據(jù)字典中讀取的信息以用于處理SQL請求；庫高速緩存，存放需要執(zhí)行的SQL語句信息，包括每個SQL語句的語法分析樹和執(zhí)行計劃。假如多個用戶要執(zhí)行同樣的SQL語句，那么語法分析樹和執(zhí)行計劃就可以重復(fù)利用，省去了語法分析步驟的昂貴花費(fèi)。2．1．2　調(diào)整SGA結(jié)構(gòu) 一般來講，在系統(tǒng)硬件支持的情況下，系統(tǒng)全局區(qū)越大越有利于數(shù)據(jù)庫高效的運(yùn)行。大的緩沖區(qū)高速緩存可以緩存更多的數(shù)據(jù)塊，這樣可以提高緩存命中率，節(jié)省物理磁盤讀取的高昂代價；大的共享池意味著大的庫高速緩存。庫緩存的內(nèi)存結(jié)構(gòu)如圖2所示。 庫緩存越大，可以保存的SQL語法分析信息越多；此外，數(shù)據(jù)庫中的一些對象，如表、索引、過程、觸發(fā)器、軟件包等也在首次執(zhí)行后進(jìn)駐庫高速緩存。大的庫緩存可以保證對這些對象的高命中率，從而節(jié)省解析和載入代價。 作為一個通用的優(yōu)化原則，我們在解決該問題時，適當(dāng)增大了SGA的容量，從而保證Oracle實例可以比較高效的運(yùn)行。設(shè)置緩沖區(qū)高速緩存的容量為32 M，設(shè)置共享池的大小為56 M。運(yùn)行rpt＿lib．sql程序檢查庫高速緩存不足率（還沒有運(yùn)行該腳本），表明庫緩存足夠。 2．2　對象調(diào)整 這一步驟我們要對每一個Oracle對象進(jìn)行調(diào)整從 而優(yōu)化性能，包括對所有的存儲參數(shù)進(jìn)行正確的設(shè)置，尤其是對影響輸入輸出的參數(shù)進(jìn)行設(shè)置。Pctfree，pctused，freelist參數(shù)的設(shè)置都會對SQL性能產(chǎn)生重要影響。 在解決該問題時，我們沒有對對象的存儲參數(shù)進(jìn)行調(diào)整。對象的存儲參數(shù)的調(diào)整要建立在具體對象的基礎(chǔ)之上，我們使用的測試大表除了在容量上模擬可能的實際對象之外，和實際對象并沒有多少可比之處。舉例來說，pctfree參數(shù)是指在實際的存儲塊中，留出來為塊中的記錄擴(kuò)展所用的空閑空間占該存儲塊容量的百分比，這個參數(shù)和實際表記錄的長度及可擴(kuò)展性有關(guān)，要在設(shè)計出實際表之后進(jìn)行調(diào)整。因此我們對對象的存儲參數(shù)使用了數(shù)據(jù)庫的缺省設(shè)置。數(shù)據(jù)庫的對象還包括索引、過程、包等，對對象的調(diào)整要包括對他們的調(diào)整，這里一并略去。 2．3　SQL語句調(diào)整 SQL語言是一種靈活的語言，相同的功能可以使用不同的語句來實現(xiàn)，但是語句的執(zhí)行效率是很不相同，一般考慮如下規(guī)則： （1）添加索引 在對大表進(jìn)行掃描時，首先要避免不必要的全表掃描。最通常的做法就是給大表添加索引。所謂全表掃描，就是在訪問表時，從磁盤上存儲該表的起始位置開始逐記錄讀數(shù)據(jù)，直到該表的結(jié)束位置。給大表添加索引后，我們可以通過訪問索引的方式獲得記錄的物理位置，從而達(dá)到訪問表的目的。設(shè)想一下，對于一個擁有大量字段的表，假如只需要返回其中少量字段，那么在這些字段上建立索引，通過索引訪問獲得記錄，將大大降低物理磁盤讀寫次數(shù)，從而降低了整個查詢響應(yīng)時間（事實上，這種情況下根本沒有必要訪問數(shù)據(jù)表，只是訪問索引就足夠了）。在我們的測試用表中，只有4個字段，顯然無法發(fā)揮索引的這一優(yōu)勢。我們的測試用表的特點是記錄條數(shù)多，達(dá)到50萬條，但每次返回的記錄數(shù)只有幾十條，在這種情況下，使用索引同樣可以起到好的效果。首先在檢索條件上建立索引，在表訪問時，我們通過索引來獲取目的記錄集的物理地址，因為返回的記錄數(shù)不多，所以這種方式造成的物理讀寫很少，應(yīng)該可以獲得較滿足的訪問時間。在測試中，我們對測試用表的條件字段添加索引，但事實上并沒有提高查詢的響應(yīng)時間，這是因為在SQL語句的執(zhí)行計劃中，在添加索引之前和之后，Oracle生成的執(zhí)行計劃中，表訪問方式都是全表掃描，根本沒有使用我們添加的索引。那么此時我們可以采用添加提示的方法。（2）添加提示 在Oracle中，我們可以在SQL語句中加入提示，來影響Oracle對優(yōu)化模式的選擇，從而生成最優(yōu)的執(zhí)行計劃。于是我們在SQL語句中加入提示，強(qiáng)制優(yōu)化器在生成執(zhí)行計劃時將表的訪問方式從全表掃描改為索引范圍掃描。結(jié)果該查詢花費(fèi)了比全表掃描多得多的響應(yīng)時間。仔細(xì)查看這個SQL語句的執(zhí)行計劃，我們發(fā)現(xiàn)Oracle對這個索引進(jìn)行了全索引掃描，這樣造成的物理讀寫數(shù)量比起全表掃描不僅沒有減少，反而大幅上升。尋找原因，問題出在我們索引的字段上。我們索引的AGE字段為了測試方便，每條記錄的值都不相同，這樣的字段其實并不具備建立索引的條件。在該測試中，我們首先要將索引載入內(nèi)存，由于索引各不相同，Oracle采取了全索引掃描的方式訪問索引，我們要讀取的是30萬條記錄（AGE＞200 k），通過索引，我們獲得的將是30萬個記錄的ROWID，假如這些記錄在磁盤上的物理位置沒有按索引順序排序，那么我們就不得不通過30萬個ROWID來訪問這些記錄，這樣造成的物理讀寫是相當(dāng)驚人的，這就是為什么我們使用了索引，卻反而查詢速度更慢的原因所在。事實上Oracle的優(yōu)化器選擇全表訪問是已經(jīng)對不同的執(zhí)行計劃作過比較的了。同時，這里同樣違反了一個Oracle優(yōu)化的準(zhǔn)則，對于沒有按索引順序排序的表，假如檢索記錄數(shù)小于總記錄數(shù)的7％，用索引比全表掃描快。 （3）更改優(yōu)化器模式 優(yōu)化器是Oracle數(shù)據(jù)庫中接受來自產(chǎn)生器處理過的SQL語句的程序。他將對SQL進(jìn)行優(yōu)化，生成內(nèi)部執(zhí)行計劃，這個計劃是Oracle存取物理磁盤數(shù)據(jù)的路徑。根據(jù)優(yōu)化器模式的不同，生成的內(nèi)部執(zhí)行計劃也不同，而對于相同的SQL，永遠(yuǎn)只有一個最優(yōu)的執(zhí)行計劃，因此選擇合適的優(yōu)化器模式，是很重要的。Oracle有2種優(yōu)化器模式：基于規(guī)則的優(yōu)化模式和基于成本的優(yōu)化模式。在這次測試中，我們使用的優(yōu)化器模式參數(shù)為CHOOSE，這樣Oracle將根據(jù)SQL語句相關(guān)表索引的有無，統(tǒng)計資料的有無以及SQL語句中的提示，自動判定使用哪種優(yōu)化模式。 （4）將調(diào)整持久化 Oracle的執(zhí)行計劃是根據(jù)各種情況，比如表的統(tǒng)計資料變化的，但有時這種變化是我們不希望的。為了將我們已經(jīng)調(diào)整好的SQL執(zhí)行計劃固定，我們可以 用Oracle的工具將執(zhí)行計劃持久化存儲。 2．4　調(diào)整結(jié)果 通過對這個測試用例的調(diào)整，我們最終將此大表的響應(yīng)時間從5 s縮短到不足2 s。經(jīng)過調(diào)整后的緩沖區(qū)高速緩存達(dá)到113 M，大約占物理內(nèi)存的50％，調(diào)整后的SQL語句為： SELECT name，age，memo FROM （SELECTname，age，memo，rownum ASmynum FROM test2） WHEREmynum＞400000 and mynum＜＝400010；我們測試所用的機(jī)器配置為CELETRON500 M，內(nèi)存256 M，硬盤為IDE8．4 G。可以說是運(yùn)行Oracle8I的最低配置。啟動Oracle 8I數(shù)據(jù)庫后，內(nèi)存使用達(dá)到300 M以上，也就是說已經(jīng)在用虛擬內(nèi)存；測試開始后，CPU保持或接近滿載，這些都會影響Oracle的性能表現(xiàn)，也會影響測試結(jié)果。 3　結(jié)　語 以下幾點可以作為通用的Oracle SQL調(diào)整原則： （1）消除不必要的全表掃描，可以通過添加索引達(dá)到。 （2）緩存小型表的全表掃描，可以通過將小型表置入緩沖區(qū)高速緩存的KEEP池中實現(xiàn)。 （3）假如表有多個索引，要保證Oracle正在使用對此SQL最優(yōu)化的索引，可以通過添加提示實現(xiàn)。