淺談ORACLE數(shù)據(jù)庫調(diào)優(yōu)

國家信息中心 何國慶 朱 虹

一、引言

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

二、ORACLE數(shù)據(jù)庫整體優(yōu)化

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

(一)內(nèi)存調(diào)優(yōu)

Oracle的內(nèi)存結(jié)構(gòu)如圖1所示：

圖1 Oracle的內(nèi)存結(jié)構(gòu)

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

(二)I/O調(diào)優(yōu)

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

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

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

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

三、應(yīng)用優(yōu)化

應(yīng)用優(yōu)化是指除了對數(shù)據(jù)庫整體優(yōu)化之外，合理調(diào)整應(yīng)用訪問數(shù)據(jù)庫的設(shè)計及SQL語句。其效果通常比數(shù)據(jù)庫整體優(yōu)化的效果要好得多。

(一)數(shù)據(jù)庫設(shè)計優(yōu)化。

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

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

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

(二)SQL語句的優(yōu)化

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

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

(三)應(yīng)用優(yōu)化

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

四、小結(jié)

數(shù)據(jù)庫的調(diào)優(yōu)主要從數(shù)據(jù)庫整體、應(yīng)用軟件兩個方面調(diào)整，數(shù)據(jù)庫整體方面的調(diào)優(yōu)包括內(nèi)存調(diào)優(yōu)和I/O調(diào)優(yōu)，他們的目標(biāo)就是硬件資源利用最大化。應(yīng)用軟件調(diào)優(yōu)主要包括數(shù)據(jù)庫設(shè)計調(diào)優(yōu)和SQL語句調(diào)優(yōu)兩部分，由于其針對性強，所以其效果往往比整體調(diào)優(yōu)效果要好。

數(shù)據(jù)庫優(yōu)化是一個長期的、漸進的系統(tǒng)過程，這個過程不能一步到位，往往是一個由分析到調(diào)整，由調(diào)整再到檢驗的循環(huán)過程。這需要讀者自己在長期的工作中慢慢摸索與積累。

[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.