基于Oracle 11g表性能優化初探

李學國 沈應蘭

(重慶科創職業學院，重慶 402160)

一 引言

Oracle數據庫系統是美國ORACLE公司(甲骨文)提供的以分布式數據庫為核心的一組軟件產品，是目前最流行的C/S或B/S體系結構的數據庫之一。Oracle數據庫是目前世界上使用最為廣泛的數據庫管理系統，作為一個通用的數據庫系統，它具有完整的數據管理功能。組成Oracle數據庫的物理結構用來存儲、管理、保護以及讀取數據，在創建數據庫時，對數據文件初始布局以及表空間的管理類型對性能都有較大的影響。實際項目中使用的Oracle數據庫經過一段時間的運行，在線保存的數據量和業務處理的數據量在逐漸增大，最初的Oracle設置，與現在實際需要的運行性能有一定差距，需要進行一些優化調整。

二 創建最優性能的數據庫

合理地配置并創建數據庫有助于保證數據庫的優良性能，并且數據庫大小會隨著使用時間成倍增長，因此創建最優性能的數據庫一般具有以下幾個原則：

1.強制數據庫中創建的每一個表空間都必須是本地管理的。本地管理表空間能比已經被棄用的字典管理技術提供更好的性能。

2.確保數據庫為每個用戶自動分配一個默認的永久表空間。這可以保證在創建用戶時，自動分配一個默認的表空間，而不是SYSTEM系統表空間。你不能讓用戶總是將對象建在SYSTEM表空間中，因為這樣會對性能和可用性產生負面影響。

3.確保數據庫會為每個用戶自動分配一個默認的臨時表空間。這可以保證在創建用戶時，自動分配一個臨時表空間，而不是SYSTEM表空間。你不能總是讓用戶使用SYSTEM表空間作為排序操作運算的臨時表空間，因為這樣會對性能和可用性產生負面影響。

如創建以下數據庫：

創建數據庫時，還需要考慮影響可維護性的功能。容易維護的數據庫能正常運行更長時間，而這也正是總體性能的一個重要部分。“解決方案”部分的CREATE DATABASE語句同時還考慮了下面這些可維護性方面的特性。(1)創建一個自動的UNDO表空間。這使Oracle可以自動管理回滾段，你也就不必定期進行監控和微調了。(2)按照環境中的一定標準，將數據文件放到相應文件夾中。這有助于維護和管理，從而使數據庫具備更好的長期可用性，從而獲得更好的性能。

三 高效移除表中數據

在對數據庫進行操作時，如果需要在移除數據以后還能選擇進行數據回滾(而不是立即提交)，那么就應該使用Delete語句。但是，Delete語句的缺點是它會產生大量的撤銷(undo)和重做信息。因此，對于大表來說，Truncate語句通常是移除數據最有效的方法。

Truncate語句的另一個特性就是它會將表的高水位線重新歸零。當你使用Delete語句移除表中數據時，高水位線將不會發生變化。使用Truncate語句并重置高水位線的一個優點就是，全表掃描查詢僅搜索位于高水位線之下的存儲塊中的數據行。這對于進行全表掃描的查詢性能具有很大的影響。

如：使用Truncate語句移除COMPUTER_SYSTEMS表中的所有數據

Truncate語句的另一個副作用就是，如果一張表定義了主鍵，并且這個主鍵是其子表的外鍵，那么不能截斷該表，即使這個子表包含零個數據行也是如此。在這種場景下，試圖截斷父表時，Oracle將會拋出下面這個錯誤：

Oracle之所以會阻止你截斷父表，是因為在一個多用戶系統中，有可能在截斷子表與接下來截斷父表之間的這段時間里，另一個會話向子表中填充數據行。在這種情況下，必須暫時禁用子表所引用的外鍵約束，執行Truncate語句，然后再重新啟用約束。

比較Truncate語句和Delete語句的功能。Oracle的確允許使用Delete語句從父表中移除數據行，而不管是否有指向子表的約束存在(假設子表中的數據為零行)。這是因為Delete會生成重做，具有讀一致性，并且能夠回滾。表1-1總結了Delete和TruncateE之間的區別。

表1 -1 Delete和Truncate比較

如果需要使用Delete語句，就必須使用COMMIT或ROLLBACK來結束事務。提交一條Delete語句就使得數據永久消失：

如果提交一條ROLLBACK語句而不是COMMIT，那么表中的數據將會與執行Delete語句之前一樣。

使用DML語句時，可以查詢V$TRANSACTION視圖來確認事務的細節。例如，假設你往表中插入了數據，在進行COMMIT或ROLLBACK之前，能夠看到當前所連接會話的活動事務信息如下所示：

四 重建跨多個數據塊的數據行

當有一張表，其中的數據行可能會存儲在多個數據塊中。這種情況會增加I/O使用率，并導致對這張表的查詢運行速度變慢。因此需要重建跨多個數據塊的數據行，使每一行數據位于一個數據塊中，從而提高表的性能。但是從表中取出數據時，可能會受到行鏈接的影響，因此必須刪除表中的行鏈接。

解決行鏈接問題的一個辦法就是使用MOVE語句。移動一張表時，Oracle需要表上的排它鎖。因此當要進行移動操作的表沒有活動事務時，應該執行MOVE操作。同時，作為移動操作的一部分，所有數據行都會被分配一個新的行編號(ROWID)。這會使表的所有索引失效。因此作為移動操作的一部分，你需要重建所有與進行移動的表相關的索引。如移動EMP表：

在數據塊中保留有一定量的空間，以便容納數據行的增長。通常數據行大小增加是由于執行了增加列長度值的UPDATE語句。如果塊中沒有足夠的空間來容納所增長的數據，那么Oracle就會創建一個指針，指向具有足夠空間的數據塊，并在該數據塊中存儲一部分行數據。當一個數據行存儲在兩個或多個數據塊中時，就稱為行鏈接。這可能會導致潛在的性能問題，因為Oracle將不得不從多個數據塊(而不是一個)中取鏈接的一行數據。

少量的行鏈接不會對性能有很大的影響。一個大致的準則是，如果表中超過15%的數據行是鏈接的，那么你就要執行正確的操作了(例如移動表以重新進行組織)。

數據塊中所保留的空閑存儲空間大小由表的存儲參數PCTFREE決定。PCTFREE的默認值為10，也就是數據塊保留10%的空間，以備更新操作使用。如果某張表中的列初始插入空值(null)，更新之后包含較大的值，那么就要考慮將PCTFREE設置為更高的值，例如40%。這將有助于阻止行鏈接的產生。

相反，如果你有一張表，在插入數據行之后就再也不會更新，那么可以考慮將PCTFREE值設置為0。這可以使每個數據塊中容納更多的行，從而減少取出數據時需要讀取的磁盤空間數量(也就提高了性能)。

［1］陳雍.基于ORACLE數據庫應用系統性能調整和優化研究［學位論文］，江西師范大學，2008

［2］Vaidyanatha.G.K等.Oracle性能優化技術內幕.北京：機械工業出版社，2002.5

［3］薩師萱，王珊.數據庫系統概論.北京：高等教學育出版社，2006