淺談ORACLE數據庫調優

國家信息中心 何國慶 朱 虹

一、引言

隨著信息技術的發展，信息技術不僅改變了我們的工作方式，更是我們提高工作效率的重要手段。數據庫是信息技術的核心技術之一，其性能更是直接關系到系統的運行效率和服務水平。但是隨著業務不斷擴展以及業務數據量的長期積累，數據庫的速度必然會有所下降。如何在數據量不斷增長和有限的資源條件下，保證數據查詢和寫入速度沒有明顯下降，使數據庫更快的運轉從而為更多的業務提供服務成為數據庫調優面臨的重要挑戰。本文首先從數據庫的自身優化開始，包括內存、讀寫相關參數設定等，然后從業務應用程序端展開討論，包括表和索引的設計、及SQL語句的拼寫等。

二、ORACLE數據庫整體優化

數據庫調優的關鍵是內存與磁盤I/O。內存的讀寫速度遠大于磁盤的讀寫速度，但是內存的容量有限，價格相對較高，最重要的是內存中的內容斷電后會丟失，所以數據庫調優的核心在保證數據安全寫入磁盤的前提下，讓更多的數據庫操作在內存中完成，盡量避免磁盤讀寫。

(一)內存調優

Oracle的內存結構如圖1所示：

圖1 Oracle的內存結構

分為SGA和PGA兩個部分。系統全局區(SGA)是一組共享內存結構，不僅包括數據庫的部分數據內容還有控制信息。程序全局區(PGA)是一個內存緩存區，它包含服務器進程的數據和控制信息。對于32位的操作系統而言，二者之和最大不超過1.5G，對于64位操作系統而言幾乎沒有限制。Oracle在對內存優化方面做了巨大努力，oracle9i實現了通過pga_aggregate_target參數對PGA的自動管理，oracle10g自動共享內存管理(Automatic shared memory management ASMM)的新特性實現了通過sga_target和sga_max_size對SGA的自動管理(實際上是共享池、流池、大型池、java池和數據庫緩沖區，其他區域是固定值)，到oracle11g更是實現了通過memory_target和memory_max_size對SGA和PGA的動態管理。這里target參數均為動態參數，可以在不停實例的情況下進行變更，max_size作為保護性參數并非對應內存的實際值，是靜態參數。此外為了保證SGA全部在物理內存中，而不被交換至虛擬內存中，我們只要設置lock_sga為“TRUE”即可，pre_page_sga參數設置為“TRUE”，可以保證在啟動數據庫時把整個SGA讀入到物理內存中，以便提高系統的效率。

(二)I/O調優

oracle頻繁讀寫的物理文件包括以下三種文件：控制文件、日志文件、數據文件。當數據庫發生任何數據變化時(如業務數據或系統數據變更時，不包括系統參數)這些變化首先被寫進聯機重做日志，而變更的序列號被保存到控制文件和buffer cathe中，最后在一定的機制下寫入數據文件。當聯機日志寫滿時，聯機日志發生切換，并將寫滿的日志文件歸檔，形成歸檔日志，此時往往有比較大的I/O波動，甚至會影響數據庫性能。

檢查點(CKPT)是oracle的一個重要事件，當完全檢查點發生時，會將buffer cathe中的所有臟數據寫入數據文件中，此時大量的I/O會影響數據庫庫性能，但完全檢查點只在正常關閉數據庫和DBA手工執行alter system checkpoint的命令時發生；當臟數據達到一定的閥值或時間，系統也會通知DBWR進程將臟數據寫入數據文件，DBWR寫數據時每3秒會將寫的進度(SCN)寫入控制文件(即heartbeat)，這兩者(系統通知DBWR工作和heartbeat)統稱增量檢查點。

優化I/O的首要途徑是分散I/O，由于安全的原因，控制文件和重做日志組的成員通常有多個副本，將他們分別存放在不同的物理磁盤上，不僅可以提高安全性，也可以有效地提高數據庫I/O帶寬。利用raid和條帶化技術可以充分利用硬件資源，也是分散I/O的重要手段。其次是調整數據庫參數。以下是影響I/O的重要參數：

參數FAST_START_MTTR_TARGET是故障關機后啟動時數據庫恢復的目標時間，系統根據該參數和硬件性能計算出1個閥值，當臟數據達到該值啟動增量檢查點，從而影響了檢查點的頻度；db_file_multiblock_read_count是數據庫每次讀寫的數據塊數；此外還有DB_BLOCK_SIZE(該參數在實例建成后就無法更改)、DB_WRITER_PROCESSES等；另外“重做日志文件大小”雖然不是一個數據庫運行參數，但直接影響數據庫的歸檔頻度和歸檔時的I/O量。

三、應用優化

應用優化是指除了對數據庫整體優化之外，合理調整應用訪問數據庫的設計及SQL語句。其效果通常比數據庫整體優化的效果要好得多。

(一)數據庫設計優化。

在數據庫設計上我們的總體原則仍然是I/O和內存這兩方面的考慮。具體包括：盡量將表空間的數據文件分散在不同磁盤上；將常用的數據存放在I/O性能好的磁盤上；使用分區表；合理創建索引及適度數據冗余等。前兩項很好理解，不再贅述。

分區表是當前主流數據庫均支持的一項技術，它將一個數據表中的數據按照一定的算法分散至不同的子表中，ORACLE數據庫支持范圍分區、列表分區、Hash分區(散列分區)、復合分區。將一個大的數據表分割成多個子表可以改善查詢性能、增強可用性、維護方便以及均衡I/O。

合理創建索引及適度數據冗余均是以空間換時間的方法。索引可以幫助我們快速查詢和排序，但是如果索引過多將會影響數據插入、刪除、更新等操作的效率。另一方面，在進行數據查詢時如果有過多表關聯，SQL語句的執行速度會大大降低，為了避免不必要的關聯，適度的數據冗余是很好的解決辦法。

(二)SQL語句的優化

SQL語句調優通常發生在試運行階段，以及數據積累到一定程度導致系統運行速度下降時。oracle強大的統計功能讓我們很容易獲取數據庫中最占用資源最多的SQL語句和Session，從而針對這些最占資源的SQL語句和Session進行分析和優化。SQL調優的主要措施有：參數化SQL語句、優化SQL語句的執行計劃、避免全表掃描等。

當一個SQL語句提交到oracle數據庫后，oracle會從緩存中查找有沒有該語句，如果有該語句，直接綁定變量并執行編譯好的SQL語句；如果沒有該語句，則編譯該語句，并根據統計信息制定執行計劃，然后綁定變量并執行。參數化SQL語句可以使大量的SQL語句合并，從而提高SQL語句執行效率。SQL語句的執行計劃通常由oracle根據數據表的統計信息自動生成，但是有時SQL并非每次起執行計劃均是最有效的，筆者在工作中遇到過相同的SQL語句僅參數不同，結果兩次查詢，時間相差非常大，經過分析后發現執行慢的那次查詢沒有走索引。像這樣的情況就需要我們干涉執行計劃，強制使用索引。

(三)應用優化

數據庫優化必然是系統優化的一部分，因此數據庫優化不能僅限于數據庫本身，要從更高的角度考慮這個問題，甚至是改變應用的設計以使整個應用系統更加合理，效率更高，如給系統添加歸檔功能、將歷史數據從運行庫中分離出去等。

四、小結

數據庫的調優主要從數據庫整體、應用軟件兩個方面調整，數據庫整體方面的調優包括內存調優和I/O調優，他們的目標就是硬件資源利用最大化。應用軟件調優主要包括數據庫設計調優和SQL語句調優兩部分，由于其針對性強，所以其效果往往比整體調優效果要好。

數據庫優化是一個長期的、漸進的系統過程，這個過程不能一步到位，往往是一個由分析到調整，由調整再到檢驗的循環過程。這需要讀者自己在長期的工作中慢慢摸索與積累。

[1]Oracle Database Performance Tuning Guide.

[2]Oracle Concept.

[3]Oracle Database 10g Administration workshop II.

[4]http://www.sjtsoft.com/sjblog/article.asp?id=589.

[5]http://wenku.baidu.com/view/cbbeaa2e915f804d2b16c186.html.