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

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

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

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

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

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

1.3 提高數(shù)據(jù)庫多個指標的命中率

數(shù)據(jù)庫指標包括數(shù)據(jù)庫高速緩沖區(qū)命中率、庫緩沖區(qū)命中率、軟解析率等，其中，高速緩沖區(qū)命中率是最常用的指標， 高速緩沖區(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 操作，通過對磁盤合理的進行規(guī)劃，利用高速緩存技術(shù)，可以提高系統(tǒng)吞吐量，縮短用戶響應(yīng)時間，盡可能有效地利用系統(tǒng)物理內(nèi)存而盡量避免或推遲使用磁盤 I/O 操作。

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

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

1.6 減小磁盤排序

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

2 影響Oracle性能的因素

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

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

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

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

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

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

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

7)SQL使用常見錯誤：配置和數(shù)據(jù)遷移的錯誤，大量遞歸 SQL 語句的存在，長時間的全表掃描，一些數(shù)據(jù)庫結(jié)構(gòu)的設(shè)置不合理，重做日志文件的不合理設(shè)置，I/O 設(shè)備的不合理的規(guī)劃，非標準參數(shù)的使用，執(zhí)行效率很差的 SQL 語句，游標和共享池的錯誤使用，低效率的數(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ù)進行檢索比起直接全表掃描所引起的I/O操作要小得多，索引可增加查詢速度;

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

3)使用存儲過程完成數(shù)據(jù)庫中頻繁執(zhí)行的應(yīng)用邏輯，使代碼編程與數(shù)據(jù)庫的操作分離，可以降低網(wǎng)絡(luò)傳輸量，提高數(shù)據(jù)請求的執(zhí)行效率，執(zhí)行存儲過程時，用戶只需要發(fā)出執(zhí)行命令，而不再進行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ù)庫的程序自動實施一定規(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ù)據(jù)庫性能需要設(shè)置合適的內(nèi)存尺寸，Oracle 內(nèi)存包括系統(tǒng)全局區(qū) (SGA)和程序全局區(qū) (PGA)。

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

根據(jù)數(shù)據(jù)庫運行狀況重新調(diào)整SGA的大小，對每個節(jié)點修改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)存停留時間更長，命中率更高，應(yīng)優(yōu)先調(diào)整庫緩沖區(qū)。

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

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

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

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

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

1)加大Buffer Cache的大小

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

2)使用多個緩沖池

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

3.2.4 盡量減少全表掃描

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

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

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

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

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

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

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

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

3.4 SQL優(yōu)化

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

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

2)為了充分利用庫緩沖區(qū)的 SQL 解析信息，對于經(jīng)常運行條件子句變量值不同的 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ù)進行排序，增大了 PGA 或 TEMP 的負擔，優(yōu)化這些語句時可在使用這些條件的列上加上有序索引;

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

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

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

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

選取約為5Gbyte左右的信貸管理系統(tǒng)作為數(shù)據(jù)庫優(yōu)化實例，該數(shù)據(jù)庫運行在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)時間變得越來越短，系統(tǒng)性能得到逐步提高。

5 結(jié)束語

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

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