qileilove

大型關(guān)系數(shù)據(jù)庫Oracle已經(jīng)廣泛應(yīng)用于各行各業(yè)，如政府、交通、公安、電信、金融、能源等部門，并已逐漸成為企業(yè)信息化建設(shè)的重要數(shù)據(jù)庫平臺，但隨著 Oracle 數(shù)據(jù)庫規(guī)模的擴(kuò)大，數(shù)據(jù)庫用戶人數(shù)的增加，數(shù)據(jù)庫性能問題越來越突出，因此，有必要對 Oracle 數(shù)據(jù)庫性能進(jìn)行調(diào)整與優(yōu)化， 使之在滿足需求條件下，系統(tǒng)性能達(dá)到最佳和系統(tǒng)開銷最小。

　　1、性能優(yōu)化目標(biāo)

　　1.1 縮短響應(yīng)時(shí)間

　　響應(yīng)時(shí)間是指從用戶提交SQL語句到數(shù)據(jù)庫返回結(jié)果集的第一行數(shù)據(jù)所需要的時(shí)間，縮短響應(yīng)時(shí)間可以通過減小系統(tǒng)服務(wù)時(shí)間或用戶等待時(shí)間來實(shí)現(xiàn)，通過使用毫秒ms來表示，通過縮短響應(yīng)時(shí)間，既能減少用戶請求的處理時(shí)間，又能提高系統(tǒng)資源利用率。

　　1.2 提高系統(tǒng)吞吐量

　　數(shù)據(jù)庫吞吐量指在單位時(shí)間內(nèi)數(shù)據(jù)庫所能完成的SQL 語句事務(wù)數(shù)量，吞吐量=事務(wù)量/時(shí)間，通常用TPS（每秒鐘的事務(wù)量） 來表示，提高數(shù)據(jù)庫的吞吐量可以在同樣的時(shí)間內(nèi)處理更多的請求，即在相同的資源環(huán)境內(nèi)做更加多的工作。

　　1.3 提高數(shù)據(jù)庫多個(gè)指標(biāo)的命中率

　　數(shù)據(jù)庫指標(biāo)包括數(shù)據(jù)庫高速緩沖區(qū)命中率、庫緩沖區(qū)命中率、軟解析率等，其中，高速緩沖區(qū)命中率是最常用的指標(biāo)， 高速緩沖區(qū)命中率=高速緩存命中總數(shù)/數(shù)據(jù)請求總數(shù)，通常使用高速緩沖區(qū)命中率來衡量Oracle數(shù)據(jù)庫的性能。

　　1.4 優(yōu)化磁盤I/O

　 　Oracle 數(shù)據(jù)庫將數(shù)據(jù)儲存在磁盤和內(nèi)存中，想要往Oracle中寫入和讀取數(shù)據(jù)基本上都會涉及到 I/O 操作，通過對磁盤合理的進(jìn)行規(guī)劃，利用高速緩存技術(shù)，可以提高系統(tǒng)吞吐量，縮短用戶響應(yīng)時(shí)間，盡可能有效地利用系統(tǒng)物理內(nèi)存而盡量避免或推遲使用磁盤 I/O 操作。

　　1.5 合理使用內(nèi)存

　　在大量并發(fā)用戶數(shù)下，若Oracle 內(nèi)存尺寸不夠會降低程序的處理效率，延緩數(shù)據(jù)庫的響應(yīng)時(shí)間，內(nèi)存是否合理使用，一般可以從使投資得到最大回報(bào)和使?fàn)幱脺p到最小這兩個(gè)指標(biāo)來判斷，通過合理使用內(nèi)存，可以大大提高系統(tǒng)性能。

　　1.6 減小磁盤排序

　　當(dāng)用戶提交的 SQL 語句含有聚合函數(shù)或者有排序時(shí)，這些排序可能在內(nèi)存中進(jìn)行，也可能在物理磁盤上進(jìn)行。由于物理磁盤自身結(jié)構(gòu)的原因，其讀寫速度遠(yuǎn)遠(yuǎn)慢于內(nèi)存讀寫，因此一個(gè)優(yōu)化的原則是盡可能減少物理磁盤排序操作。

　　2、影響Oracle性能的因素

　　影響Oracle數(shù)據(jù)性能的因素有很多，比如：操作系統(tǒng)，CPU 性能，內(nèi)存分配不合理，Oracle 配置，I/O 沖突，網(wǎng)絡(luò)速度低以及SQL使用常見錯(cuò)誤等等都會影響數(shù)據(jù)庫的性能。

　　1）操作系統(tǒng)：Oracle 數(shù)據(jù)庫服務(wù)器很大程度上依賴于運(yùn)行服務(wù)器的操作系統(tǒng)，操作系統(tǒng)配置不合理會直接降低Oracle性能；

　 　2）CPU占用過高：CPU 是服務(wù)器中一個(gè)重要的資源 ，CPU 資源被其它應(yīng)用占用或被某個(gè)數(shù)據(jù)庫事務(wù)占用，會導(dǎo)致其它數(shù)據(jù)庫事務(wù)運(yùn)行停滯，而使數(shù)據(jù)庫響應(yīng)遲鈍，比如：空閑時(shí)，CPU占用率超過90%，則說明 Oracle服務(wù)器CPU資源不足，低效率的 SQL 語句、鎖沖突、SQL 語句的重解析等原因都會引起 CPU 資源不足；

　　3）I/O 沖突：由于磁盤在同一時(shí)刻只能滿足一個(gè)進(jìn)程的需要，當(dāng)多個(gè)進(jìn)程同時(shí)訪問同一個(gè)磁盤時(shí)，會引起讀寫盤沖突，進(jìn)而降低整個(gè)系統(tǒng)的速度；

　　4）Oracle 配置：每一個(gè) Oracle 實(shí)例都是由一組 Oracle 后臺進(jìn)程和系統(tǒng)全局區(qū)的一個(gè)內(nèi)存區(qū)所組成的， 正確調(diào)整 Oracle 配置將會對系統(tǒng)性能產(chǎn)生重大的影響；

　　5）內(nèi)存分配不合理：內(nèi)存分配不合理將會減少 Oracle 用于存放最近訪問過的數(shù)據(jù)的緩沖區(qū)空間，并導(dǎo)致操作系統(tǒng)頻繁進(jìn)行頁面或內(nèi)存交換，從而導(dǎo)致計(jì)算機(jī)系統(tǒng)額外的 I/O 開銷；

　　6）網(wǎng)絡(luò)速度低：網(wǎng)絡(luò)的帶寬會在一定程度上影響系統(tǒng)的整體性能，網(wǎng)絡(luò)速度過低會增加網(wǎng)絡(luò) I/O 負(fù)荷量，從而降低數(shù)據(jù)庫系統(tǒng)的吞吐量并延長用戶響應(yīng)時(shí)間；

　　7）SQL使用常見錯(cuò)誤：配置和數(shù)據(jù)遷移的錯(cuò)誤，大量遞歸 SQL 語句的存在，長時(shí)間的全表掃描，一些數(shù)據(jù)庫結(jié)構(gòu)的設(shè)置不合理，重做日志文件的不合理設(shè)置，I/O 設(shè)備的不合理的規(guī)劃，非標(biāo)準(zhǔn)參數(shù)的使用，執(zhí)行效率很差的 SQL 語句，游標(biāo)和共享池的錯(cuò)誤使用，低效率的數(shù)據(jù)庫連接。

3、性能優(yōu)化優(yōu)化與調(diào)整技術(shù)

　　3.1 調(diào)整優(yōu)化數(shù)據(jù)庫

　　1）最常見的調(diào)優(yōu)方法是在數(shù)據(jù)庫中增加索引，索引（index）是常見的數(shù)據(jù)庫對象，充分利用索引來減少表掃描的 I/O 次數(shù)，使用索引可以避免不必要的大表全表掃描，索引設(shè)置的位置要視 where 子句中索引列所應(yīng)用的查詢條件而定，通過索引對表的數(shù)據(jù)進(jìn)行檢索比起直接全表掃描所引起的I/O操作要小得多，索引可增加查詢速度；

　　2）若某種業(yè)務(wù)的數(shù)據(jù)量增長非常快，可以使用分區(qū)表技術(shù)將數(shù)據(jù)進(jìn)行分散，將不同表空間分布到不同的磁盤當(dāng)中，使得硬盤之間 I/O 負(fù)載均衡，在一定程度上緩解了數(shù)據(jù)量過大引起的負(fù)面影響，并且會縮短查詢時(shí)間；

　　3）使用存儲過程完成數(shù)據(jù)庫中頻繁執(zhí)行的應(yīng)用邏輯，使代碼編程與數(shù)據(jù)庫的操作分離，可以降低網(wǎng)絡(luò)傳輸量，提高數(shù)據(jù)請求的執(zhí)行效率，執(zhí)行存儲過程時(shí)，用戶只需要發(fā)出執(zhí)行命令，而不再進(jìn)行SQL語句提交，節(jié)省了系統(tǒng)的SQL語法分析，充分利用了SQL共享池；

　　4）使用Oracle優(yōu)化器執(zhí)行直接提交的查詢SQL 語句，可以大大提高語句的執(zhí)行效率；

　　5）使用觸發(fā)器自動執(zhí)行的SQL語句，可以降低網(wǎng)絡(luò) I/O及系統(tǒng)資源的消耗，如需要對所有訪問數(shù)據(jù)庫的程序自動實(shí)施一定規(guī)則或檢查，則使用觸發(fā)器可以大大提高效率。

　　3.2 調(diào)整優(yōu)化內(nèi)存

　　Oracle的信息存儲在內(nèi)存和磁盤上，由于訪問內(nèi)存比訪問磁盤快得多，在大量并發(fā)用戶數(shù)下，如果Oracle 內(nèi)存尺寸不夠會降低程序的處理效率，延緩數(shù)據(jù)庫的響應(yīng)時(shí)間，提高數(shù)據(jù)庫性能需要設(shè)置合適的內(nèi)存尺寸，Oracle 內(nèi)存包括系統(tǒng)全局區(qū) （SGA）和程序全局區(qū) （PGA）。

　　3.2.1 調(diào)整SGA的大小

　　根據(jù)數(shù)據(jù)庫運(yùn)行狀況重新調(diào)整SGA的大小，對每個(gè)節(jié)點(diǎn)修改SGA大小的方法如下：

SQL>alter system set sga_target=200m scope =both sid=’RACDB1’; SQL>alter system set sga_target=200m scope =both sid=’RACDB2’;

　　3.2.2 提高共享池性能

　　共享池主要是用來存放最近使用過的 SQL語句，共享池內(nèi)存分配算法保證了數(shù)據(jù)字典數(shù)據(jù)比庫緩沖區(qū)數(shù)據(jù)在內(nèi)存停留時(shí)間更長，命中率更高，應(yīng)優(yōu)先調(diào)整庫緩沖區(qū)。

　　1）通過調(diào)整參數(shù)SHARED_POOL_SIZE的值，可以根據(jù)實(shí)際情況對每個(gè)節(jié)點(diǎn)共享池的大小進(jìn)行調(diào)整；

　　2）為了提高共享池命中率，可以使用代碼重用方法；

　　3）對于比較大的對象，如自定義的過程與包，在載入共享池以及硬解析的過程中需要共享池付出很大的代價(jià)， 把重要的大對象保持在內(nèi)存中，可以大大提高共享池性能。

　　3.2.3 優(yōu)化數(shù)據(jù)緩沖區(qū)高速緩存性能

　　為減少系統(tǒng)磁盤 I/O 開銷，應(yīng)調(diào)整數(shù)據(jù)緩沖區(qū)的尺寸，使服務(wù)器進(jìn)程盡量在緩沖區(qū)中找到所需的數(shù)據(jù)，盡量減少等待數(shù)據(jù)塊或空閑緩沖區(qū)的時(shí)間。

　　1）加大Buffer Cache的大小

　　可以通過調(diào)整DB_CACHE_SIZE參數(shù)的值增大Buffer Cache。

　　2）使用多個(gè)緩沖池

　　Keep池中數(shù)據(jù)傾向于一直保存，Recycle池中的數(shù)據(jù)傾向于即時(shí)老化，而Default池則存放未指定存儲池的數(shù)據(jù)，通過使用多個(gè)緩沖池提高Buffer Cache的命中率。

3.2.4 盡量減少全表掃描

　　通過索引的正確使用可以避免不必要的全表掃描，發(fā)生的全表掃描越少，Database Buffer Cache命中率將越高，但對于一些表比較小且需要表中的大多數(shù)數(shù)據(jù)時(shí)，這時(shí)使用全表掃描響應(yīng)時(shí)間可能就會優(yōu)于不使用全表掃描，通常，當(dāng)該表的結(jié)果集和表 中記錄總數(shù)的比值大于20%時(shí)，就應(yīng)該要使用全表掃描。

　　3.3 調(diào)整優(yōu)化磁盤I/O

　　1）經(jīng)常使用的對象產(chǎn)生 I/O 爭用的機(jī)會較多，應(yīng)將訪問量較大的數(shù)據(jù)文件放在獨(dú)立磁盤上，同一個(gè)表空間的多個(gè)數(shù)據(jù)文件應(yīng)盡可能地放在不同的磁盤上，為索引創(chuàng)建單獨(dú)的表空間，并將表和索引分開在不同的表空間；

　　2）在內(nèi)存中修改過的數(shù)據(jù)不是直接寫入數(shù)據(jù)文件，而是先寫入重做日志文件中，重做日志文件要足夠大，要與數(shù)據(jù)文件存放在不同的磁盤上，減少對磁盤的競爭，重做日志文件分為幾個(gè)組，寫滿一組時(shí)切換至下一組，最后一組寫完后再返回至第一組，按順序循環(huán)寫入；

　　3）Oracle 的文件和操作系統(tǒng)的其他文件應(yīng)盡可能地放在不同的磁盤上，這樣可以減小 I/O爭用的概率；

　　4）最好使用目前較流行的廉價(jià)磁盤冗余陣列（raid），它能自動分離不同類型、訪問頻率的數(shù)據(jù)庫文件，減小I/O進(jìn)程之間的競爭，優(yōu)化數(shù)據(jù)庫性能；

　　5）創(chuàng)建回滾段及其專用的表空間，回滾段是為了從系統(tǒng)操作的失敗中得到數(shù)據(jù)的恢復(fù)，從而減小I/O進(jìn)程之間的競爭，防止空間競爭影響事務(wù)的完成；

　　6）單獨(dú)創(chuàng)建用戶數(shù)據(jù)表空間，且要與系統(tǒng)表空間（system）分開磁盤存放，創(chuàng)建臨時(shí)表空間用于排序操作，盡可能防止數(shù)據(jù)庫碎片存在于多個(gè)表空間中。

　　3.4 SQL優(yōu)化

　　SQL 語句本身的執(zhí)行效率直接影響Oracle 數(shù)據(jù)庫執(zhí)行效率，它消耗了數(shù)據(jù)庫系統(tǒng) 70%～90%的資源，對SQL語句進(jìn)行合理設(shè)計(jì)可以使其更高效地執(zhí)行，以提高系統(tǒng)對資源的利用率，好的SQL語句可以加快執(zhí)行速度，減少網(wǎng)絡(luò)傳輸，從而 最大限度地發(fā)揮數(shù)據(jù)庫的性能。

　　1）盡量減少對數(shù)據(jù)庫的查詢次數(shù)，對幾個(gè)表查詢時(shí) FROM 子句的順序，按照由內(nèi)及外的訪問順序應(yīng)把可篩選出較少記錄的表放在前面，執(zhí)行時(shí)最先查找出這個(gè)表的幾個(gè)記錄，再和其他表的記錄相連接；

　　2）為了充分利用庫緩沖區(qū)的 SQL 解析信息，對于經(jīng)常運(yùn)行條件子句變量值不同的 SQL 語句，應(yīng)將這些變量改為統(tǒng)一的綁定變量；

　　3）調(diào)整 SQL 的關(guān)鍵是使數(shù)據(jù)庫尋找數(shù)據(jù)的路徑最簡化，限制動態(tài)SQL的使用，優(yōu)化操作符，如in或not in，is null 或 is not null，like ，union 等操作符，應(yīng)盡量少用；

　　4）避免不帶任何where條件的SQL語句的執(zhí)行，使用order by、group by、union 等條件的 SQL 語句會對查詢完的數(shù)據(jù)進(jìn)行排序，增大了 PGA 或 TEMP 的負(fù)擔(dān)，優(yōu)化這些語句時(shí)可在使用這些條件的列上加上有序索引；

　　5）對SQL 語句的索引進(jìn)行優(yōu)化，如：在索引列使用 is null 和 is not null，或進(jìn)行了顯式或隱式的運(yùn)算時(shí)索引不被使用，采用函數(shù)處理的字段也不能利用索引等；

　　6）避免相關(guān)子查詢，查詢嵌套層次越多，效率越低，為了加速查詢速度，可以使用臨時(shí)表；

　　7）在系統(tǒng)不繁忙或在大量對象更改后定時(shí)或及時(shí)統(tǒng)計(jì)數(shù)據(jù)庫信息，選擇適當(dāng)?shù)姆椒ㄟM(jìn)行優(yōu)化，包括幾種常用方法：基于代價(jià)的優(yōu)化（CBO），基于規(guī)則的優(yōu)化（RBO）， 對于需要經(jīng)常進(jìn)行查詢的表，可以通過建立索引或嵌入內(nèi)存區(qū)以提高查詢效率。

　　4、數(shù)據(jù)庫優(yōu)化實(shí)驗(yàn)

　　選取約為5Gbyte左右的信貸管理系統(tǒng)作為數(shù)據(jù)庫優(yōu)化實(shí)例，該數(shù)據(jù)庫運(yùn)行在HP ProLiant BL490c G7（603599-B21）服務(wù)器上，使用UNIX操作系統(tǒng)，優(yōu)化結(jié)果如表1所示。

　　從表1可以看出，數(shù)據(jù)庫經(jīng)過調(diào)整優(yōu)化數(shù)據(jù)庫、調(diào)整與優(yōu)化內(nèi)存、調(diào)整與優(yōu)化I/O和SOL優(yōu)化后，響應(yīng)時(shí)間變得越來越短，系統(tǒng)性能得到逐步提高。

　　5、結(jié)束語

　　隨著Oracle數(shù)據(jù)庫規(guī)模的擴(kuò)大，用戶數(shù)量的增加，Oracle數(shù)據(jù)庫性能問題越來越突出，Oracle 數(shù)據(jù)庫的性能優(yōu)化涉及的方面很廣，優(yōu)化與調(diào)整是一個(gè)需要通過不斷摸索、總結(jié)的過程，在實(shí)踐中，必須先了解影響數(shù)據(jù)庫系統(tǒng)性能的因素，針對這些不同的因素選 擇合理的優(yōu)化調(diào)整策略予以調(diào)整，同時(shí)也需要采取更加先進(jìn)的技術(shù)來對數(shù)據(jù)庫進(jìn)行調(diào)優(yōu)，使得數(shù)據(jù)庫系統(tǒng)獲得最優(yōu)性能。