Oracle數(shù)據(jù)庫性能優(yōu)化方案的研究.doc

精品論文大全oracle 數(shù)據(jù)庫性能優(yōu)化方案的研究童非, 劉泉 武漢理工大學(xué)信息工程系，武漢（430070) e-mail：摘要：主要以 oracle 數(shù)據(jù)庫性能優(yōu)化為出發(fā)點，從該數(shù)據(jù)庫結(jié)構(gòu)分析入手，圍繞數(shù)據(jù)庫 參數(shù)、應(yīng)用系統(tǒng)、網(wǎng)絡(luò)負(fù)載等方面討論了 oracle 數(shù)據(jù)庫性能調(diào)整的主要幾個方面，由此了 解到數(shù)據(jù)庫的合理規(guī)劃及運行一段時間后如何分析和改進(jìn)性能，如何維護(hù)一個性能滿意的數(shù)據(jù)庫等。關(guān)鍵詞：oracle 數(shù)據(jù)庫，性能，參數(shù)，優(yōu)化中圖分類號：tp392；文獻(xiàn)標(biāo)識碼：a1.引 言oracle 數(shù)據(jù)庫系統(tǒng)是美國 oracle 公司（甲骨文）提供的以分布式數(shù)據(jù)庫為核心的 數(shù)據(jù)庫軟件。作為全球第一大數(shù)據(jù)庫系統(tǒng)，oracle 在國內(nèi)外獲得了諸多成功應(yīng)用，我國 電信、金融、證券等關(guān)鍵領(lǐng)域的計算機系統(tǒng)中也正在使用 oracle 數(shù)據(jù)庫。隨著網(wǎng)絡(luò)應(yīng)用 和電子商務(wù)的不斷發(fā)展，數(shù)據(jù)庫服務(wù)器的訪問量越來越大，如何使用有限的計算機系統(tǒng)資源 為更多的用戶服務(wù)? 如何保證用戶的響應(yīng)速度和服務(wù)質(zhì)量? 這些問題都屬于 oracle 數(shù)據(jù) 庫性能優(yōu)化的問題。2.性能優(yōu)化的概念及其評價oracle 數(shù)據(jù)庫的優(yōu)化是通過合理分配計算機的資源（如內(nèi)存、cpu 、磁盤等）、調(diào) 整系統(tǒng)運行參數(shù)來改進(jìn) oracle 性能的過程1。優(yōu)化策略一般包括 oracle 數(shù)據(jù)庫參數(shù)調(diào) 整、磁盤 i/o 調(diào)整、應(yīng)用程序 sql 語句分析及設(shè)計、網(wǎng)絡(luò)性能調(diào)整等幾個方面。分析評價 oracle 數(shù)據(jù)庫性能主要有數(shù)據(jù)庫吞吐量、數(shù)據(jù)庫用戶響應(yīng)時間兩項指標(biāo)。 數(shù)據(jù)庫吞吐量是指單位時間內(nèi)數(shù)據(jù)庫完成的 sql 語句數(shù)目；數(shù)據(jù)庫用戶響應(yīng)時間是指用戶 從提交 sql 語句開始到獲得結(jié)果的那一段時間。3.oracle 的體系結(jié)構(gòu)oracle 的體系結(jié)構(gòu)決定了該數(shù)據(jù)庫系統(tǒng)是如何使用內(nèi)存、硬件和網(wǎng)絡(luò)，以及哪個進(jìn)程或 程序運行在哪臺機器上等，了解其體系結(jié)構(gòu)可以幫助我們解決復(fù)雜的問題，優(yōu)化數(shù)據(jù)庫的性 能，設(shè)計并開發(fā)出更加健壯的產(chǎn)品系統(tǒng)。圖 1 即是絕大多數(shù)機器環(huán)境下的 oracle 體系結(jié)構(gòu)：圖1 oracle體系結(jié)構(gòu)- 6 -3.1 內(nèi)存分為兩類：sga 與 pgasga：是用于存儲數(shù)據(jù)庫信息的內(nèi)存區(qū)，該信息為數(shù)據(jù)庫進(jìn)程所共享。它包含 oracle 服務(wù)器的數(shù)據(jù)和控制信息, 它是在 oracle 服務(wù)器所駐留的計算機的實際內(nèi)存中得以分配，如 果實際內(nèi)存不夠再往虛擬內(nèi)存中寫。pga：包含單個服務(wù)器進(jìn)程或單個后臺進(jìn)程的數(shù)據(jù)和控制信息，與幾個進(jìn)程共享的 sga正相反，pga 是只被一個進(jìn)程使用的區(qū)域，pga 在創(chuàng)建進(jìn)程時分配在終止進(jìn)程時回收。3.2 oracle 的系統(tǒng)后臺進(jìn)程oracle數(shù)據(jù)庫有四個基本后臺進(jìn)程，它們分別是：dbwr、smon、pmon、lgwr。 其中dbwr負(fù)責(zé)將database buffer catch中被修改過的數(shù)據(jù)塊寫到database files中；smon、 pmon則分別是oracle數(shù)據(jù)庫對系統(tǒng)和進(jìn)程進(jìn)行監(jiān)控、修復(fù)的兩個守護(hù)進(jìn)程；lgwr負(fù)責(zé)將 數(shù)據(jù)庫產(chǎn)生的日志文件定期寫到redo log files中。3.3 oracle 數(shù)據(jù)庫的物理結(jié)構(gòu)和邏輯結(jié)構(gòu)oracle數(shù)據(jù)庫的物理結(jié)構(gòu)可劃分為：數(shù)據(jù)文件、日志文件、控制文件和參數(shù)文件。數(shù)據(jù) 文件中存放了所有的數(shù)據(jù)信息；日志文件存放數(shù)據(jù)庫運行期間產(chǎn)生的日志信息，它被重復(fù)覆 蓋使用，若不采用歸檔方式的話，已被覆蓋的日志信息將無法恢復(fù)；控制文件記錄了整個數(shù) 據(jù)庫的關(guān)鍵結(jié)構(gòu)信息，它若被破壞，整個數(shù)據(jù)庫將無法工作和恢復(fù)；參數(shù)文件中設(shè)置了很多 oracle數(shù)據(jù)庫的配置參數(shù)，當(dāng)數(shù)據(jù)庫啟動時，會讀取這些信息。oracle數(shù)據(jù)庫的邏輯結(jié)構(gòu)則 是由一些數(shù)據(jù)庫對象組成，如：數(shù)據(jù)庫表空間、表、索引、段、視圖、存儲過程、觸發(fā)器等。 數(shù)據(jù)庫的邏輯存儲結(jié)構(gòu)(表空間等) 決定了數(shù)據(jù)庫的物理空間是如何被使用的。4.oracle 性能優(yōu)化的主要方法從對oracle數(shù)據(jù)庫體系結(jié)構(gòu)的介紹中，我們可以發(fā)現(xiàn)，主要有三個方面將影響數(shù)據(jù)庫性 能：系統(tǒng)、數(shù)據(jù)庫、網(wǎng)絡(luò)。系統(tǒng)方面的主要問題是cpu時間、內(nèi)存使用、磁盤i/o等，網(wǎng)絡(luò) 方面諸如對網(wǎng)絡(luò)帶寬的占用等。我們所說的oracle性能優(yōu)化，都是在假設(shè)服務(wù)器硬件、操作 系統(tǒng)和網(wǎng)絡(luò)帶寬均沒有引起嚴(yán)重的性能問題的前提下進(jìn)行的。4.1 數(shù)據(jù)庫參數(shù)4.1.1 cpu 參數(shù)的調(diào)整cpu是服務(wù)器的一項重要資源。服務(wù)器良好的工作狀態(tài)是在工作高峰時，cpu的使用率 在90 %以上。如果空閑時間cpu使用率就在90 %以上，說明服務(wù)器缺乏cpu資源。對unix操作系統(tǒng)的服務(wù)器，可以使用sar-u命令查看cpu的使用率?？梢酝ㄟ^查看v$sysstat數(shù)據(jù)字典中“cpu used by this session”統(tǒng)計項得知oracle數(shù)據(jù)庫使用的cpu時間， 查看“os user level cpu time”統(tǒng)計項得知操作系統(tǒng)用戶態(tài)下的cpu時間，查看“os system call cpu time”統(tǒng)計項得知操作系統(tǒng)系統(tǒng)態(tài)下的cpu時間。還可以通過查看v $sysstat數(shù)據(jù)字典來獲得當(dāng)前連接oracle數(shù)據(jù)庫各個會話占用的cpu時間，從而得知什么會話耗用服務(wù)器cpu比較多。 可以執(zhí)行下述語句來查看 sql 語句的解析情況：select * from v $sysstat where name in(parse time cpu ,parse time elapsed, parse count (hard) ) ;這里parse time cpu是系統(tǒng)服務(wù)時間，parse time elapsed是響應(yīng)時間，用戶等待時間waittime = parse time elapsed - parse time cpu。由此可以得到用戶sql語句平均解析等待時間= wait time/ parse count。這個平均等待時間應(yīng)該接近于0。如果平均解析等待時間過長，可以 通過下述語句select sql - text, parse- calls, executions from v $sqlarea order by parse- calls分析確定解析效率比較低的sql語句，優(yōu)化對應(yīng)語句，或者增加參數(shù)亦可。數(shù)據(jù)庫管理員應(yīng)該經(jīng)常查看cpu的利用率。出現(xiàn)異常要迅速找出原因，及時調(diào)整系統(tǒng)參 數(shù)或優(yōu)化sql語句。4.1.2 內(nèi)存分配的優(yōu)化技術(shù)cpu是服務(wù)器的一項重要資源。服務(wù)器良好的工作狀態(tài)是在工作高峰時，cpu的使用率 在90 %以上。如果空閑時間cpu使用率就在90 %以上，說明服務(wù)器缺乏cpu資源。圖2 oracle內(nèi)存結(jié)構(gòu)oracle服務(wù)器由一個實例（instance）和一個數(shù)據(jù)庫組成。實例分配的內(nèi)存結(jié)構(gòu)主要由系 統(tǒng)全局區(qū)sga和程序全局區(qū)pga構(gòu)成，如圖2所示。oracle運行時，用到三部分內(nèi)存區(qū)域： 軟件區(qū)、系統(tǒng)全局區(qū)和程序全局區(qū)。sga區(qū)和pga區(qū)的大小是否合適，對系統(tǒng)的性能有很大 的影響。1)調(diào)整共享池大小oracle系統(tǒng)中，共享池由幾個cache組成，每個cache保持一個特殊的數(shù)據(jù)庫對象的 信息,其中主要包括library cache（存放共享sql和pl/sql）和data dictionary cache（存放數(shù)據(jù)字典對象信息）。共享池的大小對數(shù)據(jù)庫系統(tǒng)的性能有較大的影響， library cache和data dictionary cache的大小只能通過shared - pool - size 間接調(diào)整，一般是通過下面的方法來判斷shared - pool - size參數(shù)是否需要調(diào)整。檢測library cache的大?。簊elect sum (reloads) / sum (pins) * 100 from v $ library cache查詢結(jié)果：sum (reloads) / sum (pins) * 100 = 0.59195703 此值應(yīng)該小于1 ,否則要加大shared - pool -size。 檢測data dictionary cacheselect sum (gets) “gets”,sum (getmisses) “getmisses”,sum(getmisses) / sum(gets) *100“rate”from v s | rowcache;查詢結(jié)果：gets=13502532getmisses=765065rate=5. 6660854%此rate 應(yīng)該小于15 % ,否則要加大shared - pool - size。2)調(diào)整數(shù)據(jù)緩沖區(qū)cache sga中的數(shù)據(jù)緩沖區(qū)cache用于存放oracle進(jìn)程經(jīng)常存取的表、索引、回滾段和聚族等對象的數(shù)據(jù)拷貝。用戶使用的所有數(shù)據(jù)都要通過數(shù)據(jù)高速緩存，高速數(shù)據(jù)緩沖區(qū)越大，oracle 可裝入內(nèi)存的數(shù)據(jù)就越多，磁盤的i/ o就越少，系統(tǒng)的性能也就越好。數(shù)據(jù)緩沖區(qū)的大小是 通過參數(shù)db - block -buffers來調(diào)節(jié)的。通過計算高速緩沖區(qū)命中率，可以知道db -block- buffers是否需要調(diào)整。select name, value from v $sysstat where name in (db block gets,consistent gets, physical reads查詢結(jié)果：name velue db block gets24 372 082consistent gets2. 493e + 09physical reads50 606 953hit ratio = 97. 9 %計算高速緩沖區(qū)命中率的公式為hit raito = 1- physical reads/ (db block gets + consistent gets)。對于通常的環(huán)境,要求此值大于80 %，當(dāng)命中率低于標(biāo)準(zhǔn)值時，需要增加db - block- buffers，保證命中率為80%以上。3)調(diào)整重做日志緩沖區(qū)大小 此參數(shù)用來定義內(nèi)存中重做日志緩沖區(qū)大小，雖然它相對sga較小，但當(dāng)此值設(shè)置太小時，lgwr進(jìn)程會頻繁將log buffer中的數(shù)據(jù)寫入磁盤，增加i/ o的次數(shù)，影響系統(tǒng)性能。 查v $sysstat表：select name , value from v $sysstatwhere name = redo log space requests;查詢結(jié)果:namevalueredo log space requests261value值應(yīng)該接近于零，否則每次將log -buffers增大5 %，再執(zhí)行上面的查詢，直 到value接近零。4)調(diào)整 pga oracle對事務(wù)的處理要產(chǎn)生一定數(shù)目的遞歸語句。減少不必要的sql語句的語法分析可以提高事務(wù)的處理速度，每條sql語句一旦完成了語法分析就可被重復(fù)執(zhí)行，不需重做語法 分析。為避免重復(fù)的語法分析，要為pga分配合適的用于處理sql語句的工作空間。context page的內(nèi)存分配由init .ora中的3個參數(shù)決定：open cursor ： 一個進(jìn)程同時享有的context area的總數(shù)。context area ：context area 的初始大小context incr ：決定當(dāng)信息超出context area時oracle每次追加到一個context area中的增 量。4.1.3 磁盤 i/ o 的調(diào)整磁盤的i/ o速度對整個系統(tǒng)性能有重要影響。影響磁盤i/ o的性能的主要原因有磁盤競 爭、i/ o次數(shù)過多和數(shù)據(jù)塊空間的分配管理2 。為oracle數(shù)據(jù)庫服務(wù)器創(chuàng)建新文件時，不論是表空間所用的數(shù)據(jù)文件還是數(shù)據(jù)事務(wù)登錄 所用的日志文件，都應(yīng)將文件分散存儲到各個可用邏輯或物理磁盤上，減少對數(shù)據(jù)庫的數(shù)據(jù) 文件及事務(wù)日志文件的競爭，從而有效地改善服務(wù)器的性能。調(diào)整dbwr（數(shù)據(jù)庫寫進(jìn)程）， 增加寫帶寬，oracle提供以下方法以防止dbwr活動成為瓶頸：使用異步i/ o，異步i/ o允許進(jìn)程繼續(xù)處理下一個操作，而不必等待在發(fā)出寫后，最小 化了空閑時間，因而改善了系統(tǒng)性能。使用iostat查找大磁盤請求隊列顯示特定磁盤設(shè)備上等待服務(wù)的i/ o請求有多長。oracle塊大小應(yīng)該匹配磁盤塊大小或是磁盤塊大小的倍數(shù)。4.2 應(yīng)用系統(tǒng)開發(fā)一個優(yōu)秀的應(yīng)用系統(tǒng)對提高oracle數(shù)據(jù)庫系統(tǒng)效率有著極其關(guān)鍵的作用，不過高效 的應(yīng)用系統(tǒng)需要開發(fā)人員有深厚的編程經(jīng)驗，并對oracle數(shù)據(jù)庫有較好的理解等，一般在以 下方面需要重視。4.2.1 優(yōu)化 sql 語句較好的sql語句可以提高sga區(qū)的命中率，減少i/ o請求數(shù)目，減少對網(wǎng)絡(luò)帶寬的占用 等。優(yōu)化sql語句主要從兩方面著手：應(yīng)盡量使用索引；用規(guī)范的格式和訪問數(shù)據(jù)庫對象的 一致順序書寫sql語句，使得相同訪問的sql代碼完全相同，以提高共享緩沖池的命中率。4.2.2 索引使用的一些原則 在大型應(yīng)用開發(fā)或表較大的情況下,使用索引可以極大減少數(shù)據(jù)庫讀寫次數(shù)，從而提高數(shù)據(jù)庫訪問速度，但如何正確使用索引以發(fā)揮它的優(yōu)勢需遵循以下原則。（1）在主鍵(primary key) 的索引方面，不應(yīng)有超過25 %的列成為主鍵，而只有很少的 普通列，這會浪費索引空間；（2）在索引的使用效率方面，當(dāng)選擇數(shù)據(jù)少于全表的20 %，并且表的大小超過oracle 的5個數(shù)據(jù)塊時，使用索引才會有效，否則用于索引的i/ o加上用于數(shù)據(jù)的i/ o就會大于做一 次全表掃描的i/o ;（3）當(dāng)指向被刪除行的索引所占空間超過總索引空間的20 %時，就應(yīng)刪除并重建索引， 以節(jié)省空間，提高性能。4.3 網(wǎng)絡(luò)負(fù)載在完成一定的任務(wù)量情況下，應(yīng)盡量減少客戶端與服務(wù)器之間傳遞的數(shù)據(jù)量，合理地分 配任務(wù)處理。（1）采用oracle自身的完整性約束機制，而非讓應(yīng)用程序來檢查完整性，可大大減少 客戶端/服務(wù)器間的網(wǎng)絡(luò)訪問。（2）使用觸發(fā)器、過程和包。開發(fā)人員利用觸發(fā)器、過程和包把應(yīng)用邏輯移到數(shù)據(jù)庫 服務(wù)器執(zhí)行以便減少網(wǎng)絡(luò)i/ o，提高性能。（3）使用顯式游標(biāo)。當(dāng)select語句使用隱式游標(biāo)時將增加網(wǎng)絡(luò)調(diào)用，尤其多次執(zhí)行 相同的sql語句時，顯式游標(biāo)不需傳輸這些額外的包重新打開游標(biāo)，從而避免了不必要的網(wǎng)絡(luò)傳輸。5. 實際應(yīng)用在開發(fā)的凱盛國際辦公自動化系統(tǒng)中, 采用oracle9i數(shù)據(jù)庫管理系統(tǒng)。系統(tǒng)運行的硬件 環(huán)境為：pentium iv 2.8g處理器, 512m內(nèi)存, 操作系統(tǒng)為linux。在正常工作日，系統(tǒng)運行穩(wěn) 定的狀態(tài)下, 通過該系統(tǒng)的運行狀態(tài)來觀看系統(tǒng)優(yōu)化的效果。表 1 oracle 9i 系統(tǒng)優(yōu)化對照表步驟優(yōu)化策略緩沖區(qū)高速緩沖命中率cpu 成本/s程序運行時間1無任何優(yōu)化策略49.09%9.2922.452優(yōu)化 sql63.44%7.5815.013調(diào)整 oracle 內(nèi)存分配96.49%6.036.53從表1中可能看出, 通過采取相應(yīng)的優(yōu)化策略,整個系統(tǒng)的性能得到了很大的改進(jìn)。緩沖區(qū)高速緩存命中率在調(diào)整內(nèi)存分配后大大提高, 其中cpu成本也有輕微的減少。6. 結(jié) 論優(yōu)化oracle數(shù)據(jù)庫對提高計算機系統(tǒng)可用性和效率，都有非常重要的意義，也是一件經(jīng) 常性的行之有效的工作，這里我們只是從數(shù)據(jù)庫參數(shù)、應(yīng)用系統(tǒng)、網(wǎng)絡(luò)負(fù)載等方面做了一些 簡略討論，性能優(yōu)化需要數(shù)據(jù)庫管理員、開發(fā)人員等具有較深的經(jīng)驗和共同合作，并采用正 確的方法，才能達(dá)到最終目的。參考文獻(xiàn)1 joseph c, johoson. ocp: oracl