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