Oracle數據庫性能優化分析

摘要：“隨著網絡的普及，電子商務技術得到了迅速發展。各個站點的訪問量隨之增大，數據庫規模也不斷擴大，數據庫系統的性能問題就越來越被廣泛關注。因此，如何對數據庫進行優化至關重要。本文針對這一問題使用Oracle數據庫作為研究對象進行研究。通過索引、劃分表空間、優化回滾段、分區等技術，對數據庫的存儲結構和存儲路徑進行優化，對數據庫性能進行測試，尋找一套最佳的優化方案。

關鍵詞：數據庫優化 分區 索引 回滾段

1、選題背景及意義

目前，幾乎所有的應用程序都要和數據庫打交道。尤其對于數據量大的系統，用戶在進行業務處理時往往感到速度很慢，不能滿足應用的要求。究其原因：一是硬件設備(如CPU、磁盤)的存取速度跟不上，內存容量不夠大，當然和網速也有很大的關系；二是應用程序設計方面的問題；三是沒有對數據庫進行合理的優化。針對這一嚴重影響工作效率的問題，本文結合項目開發經驗，闡述具體的配置方法。對數據庫性能的優化可以大大節省用戶在對數據庫進行操作的時間，從而可以大大提高工作效率。

作為全球第一大數據庫廠商，Oracle數據庫在國內外獲得了諸多成功應用，據統計，全球93%的上市.COM公司、65家”財富全球100強”企業不約而同地采用Oracle數據庫來開展電子商務。我國很多企業、政府單位及電子商務網站也采用了Oracle作為數據庫服務器。Oracle數據庫服務器是高度可優化的軟件產品，經常性的調整可以優化應用系統的性能，防止出現系統瓶頸[1]。因此本文選用Oracle數據庫作為研究的對象。

2、系統調整

所謂系統調整是指為了改變系統特性而對系統的軟件或硬件進行的修改。主要表現在以下幾個方面：

提高系統吞吐量：使系統能在最短的時間內完成最大的工作量。

縮短系統響應時間：提高系統返回數據的速度。

支持眾多用戶：使系統能支持最大數據的用戶。

很多因素都會影響服務器上Oracle的性能：Oracle實例的調整、操作系統、硬件以及由用戶所產生的數據庫負載等等。很多因素都可能會導致系統性能下降。下面是一些導致系統性能下降的因素[2]：

1．I/O子系統超載：這可能導致系統需要等待磁盤返回數據而降低整個系統的運行速度。

2．計算機系統內存不足：內存不足將減少Oracle用于存放最近訪問過的數據的緩沖區空間，并導致操作系統頻繁換頁和交換，從而導致計算機系統額外的I/O開銷。

3．Oracle資源缺乏：如果Oracle的共享緩沖池太小，將引發系統性能方面的問題。

4．硬件：硬件方面的問題可能是系統中包括了故障或是系統中的某種資源（如磁盤或內存）不足。

5．操作系統：這部分的內容包括對數據庫的調整和對Oracle所使用的操作系統資源的調整。

6．Oracle：為了獲得優化的系統性能，正確調整Oracle配置是非常重要的。如果調整不善將會嚴重影響系統性能。

針對以上影響系統性能的問題，本文將提出六個方案來闡述具體的性能優化過程。

3、數據庫優化方案

對數據庫優化的方案有很多，本文主要從以下幾個方面著手研究分析：

1. 所有表不建索引，并且數據庫內所有的表都存放在同一個表空間內，對海量數據測試其系統的響應時間。

2. 添加索引并從中選擇最優索引字段、劃分表空間、分離各表與索引及回滾段、進行分區并對分區建立索引，然后以同樣的方法測試系統的響應時間。

3. 優化存儲技術。確定數據塊、盤曲、非簇表的空間與簇表空間的數量和大小；優化回滾段；進行緩沖區管理；再測試系統的響應時間。

4、通過添加顯示圖來測試系統的響應時間

3.1 方案一：不采用任何優化技術來測試系統的性能

1. 創建用戶：默認存儲在users 表空間內；

2. 數據準備：創建數據表，包括三張數據字典，兩張業務表，一個用戶表，六張表存在六個關聯。其中主業務表（DragIn 表）1630000條記錄，占用140M的空間；（DragOut表）1400000條記錄，占用120M的空間；

3．測試：測試系統的性能，記錄查詢所用的時間，與下一個方案進行比較。

3.2 方案二：添加索引，優化其查詢效率

索引的選擇性是指索引列中不同值的數目與表中記錄數目的比。一個索引的選擇性越接近于1，表明這個索引的效率就越高。

本測試方案，首先確定用戶常用的字段，然后列出全部預索引字段，最后確定預索引字段的選擇性并對選擇性高的字段設置索引。確定索引的選擇性，可以有兩種方法：手工測量和自動測量。

由于只是對表dragin，dragout 的六個字段分別進行并列索引，所以采用手工測量索引的選擇性，從而確定哪種并列索引方案最優。

3.3方案三：劃分表空間，分離各類表與索引，回滾段

在以上方案的基礎上劃分表空間，分離表、索引、回滾段，從而達到優化數據庫性能的目的。具體表現為：數據庫管理員可以將組成同一個表空間的數據文件放在不同的硬盤上，做到硬盤之間I/O 負載均衡。

本方案將通過以下方法對數據庫進行優化：

1．將表和索引分開。

2．將用戶表空間從系統表空間（system）中分離出來。這將使用戶操作對數據庫功能的影響最小化。取消用戶在其它表空間中的定額，并且將缺省表空間設置值改為USERS 表空間。

3．創建回滾段專用的表空間，防止空間競爭影響事務的完成。回滾段的I/O 通常與DATA 和INDEXES 表空間的I/O 同時發生。將回滾段與數據段分隔開有助于避免發生I/O 沖突，從而簡化對它們的管理。本方案對表空間創建回滾段并指定表空間為RBS 表空間；

4．創建臨時表空間用于排序操作，盡可能的防止數據庫碎片存在于多個表

空間中。

3.4 方案四：進行分區

隨著數據的的增大，對表的維護也更加困難。在海量數據庫中，通過把一個大表分成多個小表，來簡化數據庫的管理程序的操作，稱之為分區（partition）。除了表以外，也可以對索引進行分區。Oracle 把分區表看作一個大表，同時也將其作為一些獨立的對象來管理。因為分區基礎使用的范圍值是程序設定的，所以在訪問表時可以使用特定的分區直接查詢。這樣查詢進程中只瀏覽少量數據，自然就改善了查詢性能。

在Oracle9i 中，Oracle 支持三種劃分方式：范圍劃分、散列劃分和混合劃分[3]。本文采用范圍分區。用作分區邏輯基礎常用的劃分基礎是表中一個外鍵，因此本文中以DragId 和DragTypeId，FactoryId 列為基礎進行分區。如果頻繁使用這些列進行查詢，就可以基于這些列分開數據，然后將其用作分區鍵。

3.4.1 查詢分區

查詢直接在分區中執行，這樣可以優化查詢功能，減少I/O 沖突的可能性。其代碼如下：

select * from DragIn partition(part1) where 字段 between 數值 and 數值

3.4.2 分區的索引

當創建一個分區表時，應該對該表創建一個索引。表索引也隨劃分表的范圍值進行劃分。為了使分區的效果更好，在分區的基礎上創建一個局部索引。

3.5 方案五：優化存儲技術

由于數據庫的塊大小在數據庫創建以后就不能再修改（除非重建數據庫），因此為了減少數據鏈接和行遷移，提高磁盤空間的利用率，在設計數據庫時要確定合適的數據塊大小和存儲參數。本文根據樣例數據確定數據塊大小，而根據業務現狀和未來發展趨勢確定存儲參數。

3.5.1 storage（存儲參數）子句

一個段所使用的空間量由它的存儲參數決定。這些參數在段創建時由數據庫來確定。存儲參數規定initial 盤區大小、next盤區大小以及pctincrease (每個順序盤區幾何增長的系數)、maxextents (盤區的最大數量)、minetents(盤區的最小數量)值。在段創建后，就不能改變initial 和minextents 的值。當創建一個段時，它至少會獲取一個盤區(其他值可以通過minextents 設定)。盤區用于存儲數據，直到沒有可利用的自由空間為止。當段中增加新的數據時，這個段通過獲取由next 參數規定大小的另一個盤區來實現擴充。Pctincrease 參數用于最小化增長的表中的盤區數量。若該參數的值不是零，則可能導致每個相繼盤區的尺寸幾何地增長，而這個增長率由pctincrease 系數確定。理想情況是，一個段只具有一個大小合適的盤區，并且next值較小，設表的pctincrease 值為0。這樣設置將避免段中碎片所帶來的影響[4]。

3.5.2 估計非簇表的空間

只需知道下列四個值就可估計一個表所需的空間：數據庫的塊大小、該表的pctfree值、行的平均長度、該表中的期望行數。

1. 確定數據庫的塊大小

數據庫的塊大小可通過數據庫的init.ora 文件中的DB_BLOCK_SIZE 參數來設置。每個數據庫塊都有一個用于該塊內開銷的區域。表的塊開銷估計為90字節[5]。因此，一個塊中的有效空間=為數據庫塊大小-塊開銷。

根據此次實驗的要求，確定塊大小為8192字節，從而確定其有效空間為8102字節。

2. 計算pctfree 值

pctfree 值代表了為自由空間保留的每個數據塊的百分比。它控制了一個表的塊中存儲的記錄數[6]。如果pctfree 值設置得太小，則有可能使一些行移到新的數據塊中以適應它們的新長度。如果pctfree 值太大，會導致不必要的空間浪費。

3. 確定行的平均長度

接下來估算平均行長度。Date 值的長度估計為8字節，Number 值的長度估計為4字節。對于Varchar2 列，估算存儲在該列中的數值的實際長度。注意這些估算含有附加的列開銷。實際上，一個Date 值存有7字節，而一個Number 值是3字節。

4. 確定表的最終空間方案：

一個表具有10列，估計的平均行長度為600字節。由于每塊有3605字節的有效空間(根據前面的估計)，所以每塊的行數是：3605/600=6。

現在，必須估算表中的期望行數，如果該測試表擁有25000行，那么所需塊數為：25000/6=4166。

該表大約需要4166塊。然而，它與上述幾節所指定的盤區大小不相符。可有下列兩種選擇：

(1)創建一個16MB(4096塊)的initial 盤區和一個512KB（128塊）的next 盤區。

(2)如果空間足夠并且考慮到表的進一步增長，可創建一個32MB的initial 盤區。

如果使用第一個選項，則空間分配（4224塊）將超過估算值（4166塊）的1%。通過分配這附加的1％，就可創建一個表，其盤區大小可正確調整以符合性能和再利用自由盤區的要求。

3.5.3 確定盤區尺寸

如果表的盤區大小不是I/O緩沖區大小的倍數，則所需要的讀操作次數可能會增加。要避免為盤區大小付出性能代價，必須選擇如下兩種策略之一：

1. 創建明顯大于I/O容量的盤區。如果盤區非常大，即使盤區的大小不是I/O緩沖區大小的倍數，也只需要很少的附加讀操作。

2. 創建其大小是操作系統的I/O緩沖區大小的倍數的盤區。

考慮到第一種方法對硬件，軟件的要求都比較高，此處采用第二種方法進行計算。由于系統默認的I/O 緩沖區大小為64KB， 那么盤區大小可選擇為64KB、128KB、192KB、256KB等等. 要避免浪費自由空間，必須使較大盤區大小是較小盤區大小的整數倍，這樣在數據存儲時不至于造成大量的空間流失。

以此為基礎，根據本次實驗的要求，確定其盤曲尺寸為128KB。

3.6 方案六：應用顯示圖技術

對于Oracle，你可以創建顯形圖(materalized view)。顯形圖在結構上與快照非常相似。它存儲基于一個基本查詢的復制數據。快照一般存儲來自遠程數據庫的數據，而顯形圖通常則存儲從當前數據庫中復制的數據。在數據庫操作期間，如果顯形圖要返回相同的數據，優化程序就可以動態地選擇使用一個可利用的顯形圖，以代替對一個更大數據表的查詢。顯形圖還可以提供潛在的性能，但是要增加空間占用和維護代價。

4 結束語

Oracle數據庫的性能調整相當重要，但難度也較大。數據庫管理員需要綜合運用上面介紹的規律，在數據庫建立時，就能根據應用的需要合理設計分配表空間以及存儲參數、內存使用初始化參數，對以后的數據庫性能有很大的益處。只有認真分析Oracle運行過程當中出現的各種性能問題，才能保證Oracle數據庫高效可靠地運行。還需要指出的是：數據庫的性能調整是一個系統工程，涉及的方面很多，不能僅僅根據一個時間點的情況就斷定數據庫運行性能的好與壞。如何有效地進行調整，數據庫管理員需要經過反反復復的過程。這些都需要在大量的實踐工作中不斷地積累經驗，從而更好地進行數據庫的調優。由于知識準備不充分和缺少實際經驗，因此在很多技術細節上闡述得不夠明確，這有待于在實際工作中提高。

參考文獻：

[1] Oracle數據庫性能優化技術， 文章來自:天極網

http://www.7880.com/Info/Article-42b295c0.html，2005-5-14 11:00.

[2] Edward Whalen， Mitchell Schroeter 著， 高艷春， 周兆確， 唐艷軍譯. Oracle 性能調整與優化. 2002年12月第一版. 北京: 人民郵電出版社出版發行， 2002.

[3] 求是科技. Java數據庫系統開發實例導航. 2004年5月第1版. 北京: 人民郵電出版社出版發行， 2004.

[4] 文宏. Oracle9i簡明教程――輕松實戰20天. 2003年10月 第一版. 北京: 清華大學出版社. 2003.

[5] Oracle中優化SQL的原則. http://www.ddvip.net/database/oracle/index5/198.htm， 2005-4-16 10:29 .

[6] 提高ORACLE數據庫的查詢統計速度.

http://www.ddvip.net/database/oracle/index5/198.htm， 2005-4-16 10:39 .

[7] 數據庫查詢優化.北工大計算機學院軟件工程研究所， 程穎 http://www.strongsun.com/oracle/optimizea.htm， 2005-4-16-10:23.

[8] 李志敏， 彭志剛.Oracle數據庫性能優化技術， 計算機與信息技術， 2004.

[9] 薩師煊．數據庫系統概論［M］．北京：高等教育出版社， 2000.

[10] 王能斌．數據庫系統原理［M］．北京：電子工業出版社， 2000.

[11] 袁長河．Sybase SQLserver性能優化技術初探［J］.計算機系統應用， 2000.