對(duì)Oracle數(shù)據(jù)庫(kù)性能優(yōu)化技術(shù)的研究

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

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

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

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

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

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

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

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

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

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

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

1.6 減小磁盤排序

當(dāng)用戶提交的 SQL 語(yǔ)句含有聚合函數(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使用常見(jiàn)錯(cuò)誤等等都會(huì)影響數(shù)據(jù)庫(kù)的性能。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

根據(jù)數(shù)據(jù)庫(kù)運(yùn)行狀況重新調(diào)整SGA的大小，對(duì)每個(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 提高共享池性能

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

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

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

3)對(duì)于比較大的對(duì)象，如自定義的過(guò)程與包，在載入共享池以及硬解析的過(guò)程中需要共享池付出很大的代價(jià)， 把重要的大對(duì)象保持在內(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的大小

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

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

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

3.2.4 盡量減少全表掃描

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

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

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

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

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

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

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

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

3.4 SQL優(yōu)化

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

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

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

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

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

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

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

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

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

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

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

5 結(jié)束語(yǔ)

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

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]