對Oracle數據庫性能優化技術的研究

1 性能優化目標

1.1 縮短響應時間

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

1.2 提高系統吞吐量

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

1.3 提高數據庫多個指標的命中率

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

1.4 優化磁盤I/O

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

1.5 合理使用內存

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

1.6 減小磁盤排序

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

2 影響Oracle性能的因素

影響Oracle數據性能的因素有很多，比如：操作系統，CPU 性能，內存分配不合理， Oracle 配置， I/O 沖突，網絡速度低以及SQL使用常見錯誤等等都會影響數據庫的性能。

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

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

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

4)Oracle 配置：每一個 Oracle 實例都是由一組 Oracle 后臺進程和系統全局區的一個內存區所組成的， 正確調整 Oracle 配置將會對系統性能產生重大的影響;

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

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

7)SQL使用常見錯誤：配置和數據遷移的錯誤，大量遞歸 SQL 語句的存在，長時間的全表掃描，一些數據庫結構的設置不合理，重做日志文件的不合理設置，I/O 設備的不合理的規劃，非標準參數的使用，執行效率很差的 SQL 語句，游標和共享池的錯誤使用，低效率的數據庫連接。

3 性能優化優化與調整技術

3.1 調整優化數據庫

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

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

3)使用存儲過程完成數據庫中頻繁執行的應用邏輯，使代碼編程與數據庫的操作分離，可以降低網絡傳輸量，提高數據請求的執行效率，執行存儲過程時，用戶只需要發出執行命令，而不再進行SQL語句提交，節省了系統的SQL語法分析，充分利用了SQL共享池;

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

5)使用觸發器自動執行的SQL語句，可以降低網絡 I/O及系統資源的消耗，如需要對所有訪問數據庫的程序自動實施一定規則或檢查，則使用觸發器可以大大提高效率。

3.2調整優化內存

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

3.2.1 調整SGA的大小

根據數據庫運行狀況重新調整SGA的大小，對每個節點修改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語句，共享池內存分配算法保證了數據字典數據比庫緩沖區數據在內存停留時間更長，命中率更高，應優先調整庫緩沖區。

1)通過調整參數SHARED_POOL_SIZE的值，可以根據實際情況對每個節點共享池的大小進行調整;

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

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

3.2.3 優化數據緩沖區高速緩存性能

為減少系統磁盤 I/O 開銷，應調整數據緩沖區的尺寸，使服務器進程盡量在緩沖區中找到所需的數據，盡量減少等待數據塊或空閑緩沖區的時間。

1)加大Buffer Cache的大小

可以通過調整DB_CACHE_SIZE參數的值增大Buffer Cache。

2)使用多個緩沖池

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

3.2.4 盡量減少全表掃描

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

3.3 調整優化磁盤I/O

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

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

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

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

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

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

3.4 SQL優化

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

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

2)為了充分利用庫緩沖區的 SQL 解析信息，對于經常運行條件子句變量值不同的 SQL 語句，應將這些變量改為統一的綁定變量;

3)調整 SQL 的關鍵是使數據庫尋找數據的路徑最簡化，限制動態SQL的使用，優化操作符，如in或not in，is null 或 is not null，like ，union 等操作符，應盡量少用;

4)避免不帶任何where條件的SQL語句的執行，使用order by、group by、union 等條件的 SQL 語句會對查詢完的數據進行排序，增大了 PGA 或 TEMP 的負擔，優化這些語句時可在使用這些條件的列上加上有序索引;

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

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

7)在系統不繁忙或在大量對象更改后定時或及時統計數據庫信息，選擇適當的方法進行優化，包括幾種常用方法：基于代價的優化(CBO)，基于規則的優化(RBO)， 對于需要經常進行查詢的表，可以通過建立索引或嵌入內存區以提高查詢效率。

4 數據庫優化實驗

選取約為5Gbyte左右的信貸管理系統作為數據庫優化實例，該數據庫運行在HP ProLiant BL490c G7(603599-B21)服務器上，使用UNIX操作系統，優化結果如表1所示。

從表1可以看出，數據庫經過調整優化數據庫、調整與優化內存、調整與優化I/O和SOL優化后，響應時間變得越來越短，系統性能得到逐步提高。

5 結束語

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

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