oracle數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能優(yōu)化

魏亞楠，宋義秋

WEI Ya-nan, SONG Yi-qiu

（唐山職業(yè)技術(shù)學(xué)院，唐山 063000）

0 引言

信息化系統(tǒng)都基于數(shù)據(jù)庫而運(yùn)行，而數(shù)據(jù)庫系統(tǒng)性能又最大程度的決定著應(yīng)用系統(tǒng)的性能。大多數(shù)數(shù)據(jù)庫系統(tǒng)在運(yùn)行一段時(shí)間后都會(huì)存在一定的性能問題，主要涉及數(shù)據(jù)庫硬件、數(shù)據(jù)庫服務(wù)器、數(shù)據(jù)庫內(nèi)存、應(yīng)用程序、操作系統(tǒng)、數(shù)據(jù)庫參數(shù)等方面。因此，基于數(shù)據(jù)庫系統(tǒng)的性能調(diào)整與優(yōu)化對(duì)于整個(gè)系統(tǒng)的正常運(yùn)行起著至關(guān)重要的作用。

1 oracle數(shù)據(jù)庫及特點(diǎn)

oracle是一個(gè)功能極其強(qiáng)大的數(shù)據(jù)庫系統(tǒng)。它起始于七十年代末的關(guān)系型數(shù)據(jù)庫技術(shù)。這種類型數(shù)據(jù)庫的關(guān)鍵是怎樣理解數(shù)據(jù)間的關(guān)系，然后構(gòu)造反映這些關(guān)系的信息庫。oracle成功的將關(guān)系型數(shù)據(jù)庫轉(zhuǎn)移到桌面計(jì)算機(jī)上，提供了一個(gè)完整的客戶/服務(wù)器體系結(jié)構(gòu)的商用DBMs。同時(shí)它利用SQL*NET軟件層，與多種操作系統(tǒng)支持通信協(xié)議相配合，為oracle關(guān)系型數(shù)據(jù)庫提供分布式環(huán)境，可以實(shí)現(xiàn)單點(diǎn)更新，多點(diǎn)查詢。Oracle數(shù)據(jù)庫已經(jīng)被用于各種大型信息系統(tǒng)中，特別是諸如銀行，保險(xiǎn)，煙草，石油等大數(shù)據(jù)量，對(duì)安全性要求較高的企業(yè)。其特點(diǎn)主要體現(xiàn)在：1）支持大數(shù)據(jù)庫、多用戶的高性能事務(wù)處理Oracle支持最大數(shù)據(jù)庫（幾百TB），可充分利用硬件設(shè)備。支持大量用戶同時(shí)在同一數(shù)據(jù)上執(zhí)行各種應(yīng)用，并使數(shù)據(jù)爭(zhēng)用最小，保證數(shù)據(jù)的一致性[1]。2）硬件環(huán)境獨(dú)立。Oracle具有良好的硬件環(huán)境獨(dú)立性，支持各種類型的大型，中型，小型和微機(jī)系統(tǒng)。3）遵守?cái)?shù)據(jù)存取語言、操作系統(tǒng)、用戶接口和網(wǎng)絡(luò)通信協(xié)議的工業(yè)標(biāo)準(zhǔn)。4）較好的安全性和完整控制。Oracle有用戶鑒別、特權(quán))、角色、觸發(fā)器、日志、后備等功能，有效地保證了數(shù)據(jù)存取的安全性和完整性以及并發(fā)控制和數(shù)據(jù)的回復(fù)。5）具有可移植性、可兼容性與可連接性oracle不僅可以在不同型號(hào)的機(jī)器上運(yùn)行，而且可以在同一廠家的不同操作系統(tǒng)支持下運(yùn)行。具有操作系統(tǒng)的獨(dú)立性。

2 數(shù)據(jù)庫系統(tǒng)性能評(píng)價(jià)指標(biāo)

主要從以下幾個(gè)方面進(jìn)行：1）系統(tǒng)吞吐量。吞吐量是指單位時(shí)間內(nèi)數(shù)據(jù)庫完成的SQL語句數(shù)目，以每秒鐘的事務(wù)量(tps)表示。提高系統(tǒng)吞吐量可以通過減少服務(wù)時(shí)間在同樣的資源環(huán)境下做更多的工作或通過減少總的響應(yīng)時(shí)間使工作做得更快這兩種方法來實(shí)現(xiàn)。2）用戶響應(yīng)時(shí)間。響應(yīng)時(shí)間是指用戶從提交SQL語句開始到獲得結(jié)果集的第一行所需要的時(shí)間，是應(yīng)用做出反應(yīng)的時(shí)間，以毫秒或秒表示。響應(yīng)時(shí)間可以分為系統(tǒng)服務(wù)時(shí)間（CPU時(shí)間）和用戶等待時(shí)間兩項(xiàng)。也就是說，要獲得滿意的用戶響應(yīng)時(shí)間有兩個(gè)途徑：一是減少系統(tǒng)服務(wù)時(shí)間，即提高數(shù)據(jù)庫的吞吐量；二是減少用戶等待時(shí)間，即減少用戶訪問同一數(shù)據(jù)庫資源的沖突率。3）數(shù)據(jù)庫命中率。Oracle用戶進(jìn)程所需的所有數(shù)據(jù)都是經(jīng)過緩沖區(qū)高速緩存來存取的。用戶對(duì)數(shù)據(jù)的需求能否在內(nèi)存中得到滿足，給出快速的響應(yīng)，可用緩沖區(qū)高速緩存命中率來衡量。該比率等于高速緩存命中總數(shù)除以對(duì)高速緩存的查找總數(shù)。由于從高速緩存中讀數(shù)據(jù)比從磁盤中讀數(shù)據(jù)的開銷要小得多，因此一般應(yīng)使該命中率足夠高。4）內(nèi)存使用情況。內(nèi)存的使用情況主要體現(xiàn)在可共享內(nèi)存、永久性內(nèi)存和運(yùn)行時(shí)內(nèi)存這三者的分配使用上。內(nèi)存是否合理使用，一般考慮的主要調(diào)整目標(biāo)有兩條：使投資得到最大回報(bào)。把時(shí)間和精力用于解決可能產(chǎn)生最大利益的問題；使?fàn)幱脺p到最小。瓶頸的特點(diǎn)在于延遲和等待，盡可能地消除或減少它。5）磁盤I/O。數(shù)據(jù)庫中發(fā)生的每個(gè)動(dòng)作幾乎都將產(chǎn)生某種類型的I/O活動(dòng)，該活動(dòng)可以是邏輯的（在內(nèi)存中），也可以是物理的（在磁盤上）。通過降低不必要的I/O開銷可以增加用戶任務(wù)可獲得的吞吐量，縮短用戶響應(yīng)時(shí)間。其中，磁盤I/O操作是數(shù)據(jù)庫性能最重要的方面，是計(jì)算機(jī)最大的開銷。

3 oracle數(shù)據(jù)庫應(yīng)用系統(tǒng)性能優(yōu)化的主要方向

3.1 CPU利用

CPU是服務(wù)器的重要資源，服務(wù)器良好的工作狀態(tài)是在工作高峰時(shí)CPU的使用率在90%以上。在大型的應(yīng)用系統(tǒng)中，比較流行的配置是oracle+unix，如IBM的小機(jī)，sun公司的red hat等。這些系統(tǒng)中使用sar-u命令查看CPU的使用率，Windows系列的操作系統(tǒng)的服務(wù)器，可以使用性能管理器來查看CPU的使用率。Oracle中：v$sysstat數(shù)據(jù)字典中“CPU used by this session”記錄了數(shù)據(jù)庫使用的CPU時(shí)間，“OS User level CPU time”統(tǒng)計(jì)了操作系統(tǒng)用戶態(tài)下的CPU時(shí)間，“OS System call CPU time”統(tǒng)計(jì)了操作系統(tǒng)系統(tǒng)態(tài)下的CPU時(shí)間，操作系統(tǒng)總的CPU時(shí)間就是用戶態(tài)和系統(tǒng)態(tài)時(shí)間之和，如果Oracle數(shù)據(jù)庫使用的CPU時(shí)間占操作系統(tǒng)總的CPU時(shí)間90%以上，說明服務(wù)器CPU基本上被Oracle數(shù)據(jù)庫使用著，這是合理，反之，說明服務(wù)器CPU被其它程序占用過多，Oracle數(shù)據(jù)庫無法得到更多的CPU時(shí)間[2]。出現(xiàn)CPU資源不足的原因可能是SQL語句的重解析、低效率的SQL語句、鎖沖突等，oracle提供了初步的分析方法：對(duì)于SQL語句的重解析可以執(zhí)行下述語句來查看SQL語句的解析情況：

SELECT * FROM V$SYSSTAT WHERE NAME IN('parse time cpu','parse time elapsed','parse count(hard)')，其中parse time cpu是系統(tǒng)服務(wù)時(shí)間，parse time elapsed是響應(yīng)時(shí)間，用戶等待時(shí)間waite time為兩者之差。由此可以得到用戶SQL語句平均解析等待時(shí)間=waite time/parse count。這個(gè)平均等待時(shí)間應(yīng)該接近于0，如果平均解析等待時(shí)間過長(zhǎng)；對(duì)于SQL語句解析效率比較低的問題，可以通過以下方法查詢哪些sql需要優(yōu)化：

SELECT SQLJEXT,PARSE CALLS,EXECUTIONS FROM V$SQLAREA，

SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA。對(duì)于沖突，可以通過v$system_event數(shù)據(jù)字典中的“l(fā)atch free”統(tǒng)計(jì)項(xiàng)查看，如果沒有沖突的話，latch free查詢出來沒有結(jié)果。如果沖突太大的話，可以降低spin_count參數(shù)值，來消除高的CPU使用率。

3.2 內(nèi)存分配

內(nèi)存參數(shù)的調(diào)整主要是指Oracle數(shù)據(jù)庫的系統(tǒng)全局區(qū)SGA（System Global Area）的調(diào)整。SGA是Oracle數(shù)據(jù)庫的心臟，是對(duì)數(shù)據(jù)庫數(shù)據(jù)進(jìn)行快速訪問的一個(gè)系統(tǒng)區(qū)域，可以被服務(wù)器和用戶共享。SGA主要由三部分構(gòu)成：共享池（Share Pool）、數(shù)據(jù)緩沖區(qū)（Data Buffers）、日志緩沖區(qū)（Redo Log Buffers）和PGA區(qū)域。SGA隨著不同的環(huán)境而不同，沒有一種通用的最佳方案，但在設(shè)置它之前要先考慮以下的幾個(gè)方面：物理內(nèi)存多大：操作系統(tǒng)是哪種以及占多大的內(nèi)存，數(shù)據(jù)庫系統(tǒng)是文件系統(tǒng)還是存儲(chǔ)設(shè)備；數(shù)據(jù)庫運(yùn)行的模式。SGA占有物理內(nèi)存的比例沒有嚴(yán)格的規(guī)定，只能遵從一般的規(guī)則：SGA占據(jù)物理內(nèi)存的40%～60%左右。如果通過直觀的公式化來表達(dá)則為：OS使用內(nèi)存+SGA+并發(fā)進(jìn)程數(shù)×（Sort_area_size+Hash_area_size+2M）＜0.7RAM，以這個(gè)公式為參考進(jìn)行自由調(diào)整即可。初始化參數(shù)文件中的一些參數(shù)對(duì)SGA的大小有決定性的影響。參數(shù)Db_block_Buffers（SGA中存儲(chǔ)區(qū)高速緩存的緩沖區(qū)數(shù)目），參數(shù)Shared_pool_size（分配給共享SQL區(qū)的字節(jié)數(shù)），是SGA大小的主要影響者。DataBuffers參數(shù)是SGA大小和數(shù)據(jù)庫性能的最重要的決定因素。該值較高，可以提高系統(tǒng)的命中率，減少I/O。每個(gè)緩沖區(qū)的大小等于參數(shù)Db_block_size的大小。Oracle數(shù)據(jù)庫塊以字節(jié)表示大小。Oracle SGA區(qū)共享池部分由庫高速緩存（Library

Cache）、字典高速緩存（Dictionary Cache）及其他一些用戶和服務(wù)器會(huì)話信息組成，共享池是最大的消耗成分。

4 oracle數(shù)據(jù)庫應(yīng)用系統(tǒng)性能優(yōu)化技術(shù)

4.1 sql語句優(yōu)化

SQL語句優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識(shí)別的語句，充分利用索引來減少表掃描的I/O次數(shù)，盡量避免表搜索的發(fā)生。優(yōu)化的目的就是將性能低下的SQL語句轉(zhuǎn)換成目的相同的、性能優(yōu)異的SQL語句，使數(shù)據(jù)查找的路徑最簡(jiǎn)化，并盡量保持處理器時(shí)間和I/O時(shí)間的平衡。通常分為以下幾個(gè)步驟：1）查找有問題的SQL語句。優(yōu)化有問題的sql語句能顯著提高數(shù)據(jù)庫性能。尋找的方法有：搜集統(tǒng)計(jì)數(shù)據(jù)，oracle中可以通過DBMSSTATS包或ANALYZE命令，前者可用于搜集有關(guān)鏈接數(shù)據(jù)行的統(tǒng)計(jì)數(shù)據(jù)，簇的數(shù)據(jù)情況只能使用ANALYEZ命令獲得，其相關(guān)語法為ANALYEZE CLUSTER Cluste_name Computer STATISTICS，對(duì)于其它情況可以使用DBMS_SATS包，語法為：exec DBMS_SATS.gather_table_stats(‘owname’,’tablename’,’partnma e’)；利用SQLTrace工具分析SQL語句。通過命令A(yù)LTER SESSION SET SQL_TRACE=TRUE會(huì)話激活SQL_TRACE，oracle就會(huì)在udump管理區(qū)創(chuàng)建跟蹤文件，從而可以了解如解析、執(zhí)行和返回?cái)?shù)據(jù)的次數(shù)、CPU時(shí)間和執(zhí)行時(shí)間、物理讀和邏輯讀操作次數(shù)、庫緩沖區(qū)命中率等參數(shù)；通過oracle Enterprise Manager Console、Oracle Diagnostics Pack等圖形性能工具能夠很快地獲取到數(shù)據(jù)庫緩沖區(qū)命中率、CPU利用率、運(yùn)行時(shí)內(nèi)存等重要的數(shù)據(jù)庫性能指標(biāo)信息，并將它們以GUI表格形式和曲線圖形式顯示出來[3]。2）建立合適的SQL語句。參照原則如下：建立“適當(dāng)”的索引，使用索引的根本目的就是為了提高查詢效率，但索引也不是越多越好，使用索引時(shí)應(yīng)遵循相應(yīng)的原則。此外，為了降低I/O競(jìng)爭(zhēng)，索引不應(yīng)與用戶表空間建在同一磁盤上；避免使用耗費(fèi)資源的操作如DSJTNITC、UNION、GROUP BY、ORDER BY等關(guān)鍵字，因?yàn)樗麄儠?huì)啟動(dòng)SQL引擎執(zhí)行耗費(fèi)資源的排序功能，通常這些語句都可以通過其他方式實(shí)現(xiàn)；注意WHERE子句中的查詢順序，oracle采用自下而上的順序解析，因此表之間的連接必須寫在其他Where條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在Where子句的末尾，返回記錄最少的索引列應(yīng)寫在最前面。

4.2 oracle內(nèi)存調(diào)整

Oracle的內(nèi)存結(jié)構(gòu)較為復(fù)雜，但是對(duì)系統(tǒng)具有較大影響的參數(shù)一般為共Shared pool，Database Buffer Cache，Redo log buffer，Java Pool，Large Pool，Program Global Area等。1）優(yōu)化Shared pool。共享池大小是否合適，主要體現(xiàn)在庫緩沖區(qū)和數(shù)據(jù)字典高速緩沖區(qū)的命中率上。庫緩沖區(qū)設(shè)置過小那么語句將被連續(xù)不斷地裝入影響系統(tǒng)性能，如果設(shè)置過大，將導(dǎo)致空間碎片化以及CPU使用率的大幅上升。庫緩沖區(qū)的命中率可以通過命令select (1-sum(reloads/sum(pins) from v$librarycache，該值若小于0.95，則可以通過增加SHARED_POOLSIZE值來提高命中率。對(duì)于數(shù)據(jù)字典高速緩存，通過以下命令select sum(1-(getmisses)/(sum(gets)+sum(getmisses))) form v$rowcache，如果結(jié)果小于0.85，則需增大共享池的大小來提高數(shù)據(jù)字典高速緩存可用的內(nèi)存數(shù)量。2）優(yōu)化緩沖區(qū)高速緩存。緩沖區(qū)高速緩存越大，oracle可裝入內(nèi)存的數(shù)據(jù)就越多，磁盤的I/O性能就越少，系統(tǒng)性能就越好。通過數(shù)據(jù)字典v$vsysstat可以了解其活動(dòng)情況：select name，value from v$sysstat where name in （’dbblokc gets’,’consistent gets’,’Physical reads’)；如果命中率小于0.85，則可增加參數(shù)DB_CACH_SIZE的值為數(shù)據(jù)塊緩沖區(qū)分配更多的內(nèi)存。3）調(diào)整重做日志緩沖區(qū)。重做日志緩沖區(qū)如果分配太小，會(huì)導(dǎo)致沒有足夠的空間來放重做條目而等待，LGWR進(jìn)程會(huì)頻繁將LOG BUFEFR中的數(shù)據(jù)寫入磁盤增加I/O的次數(shù)，影響系統(tǒng)性能。查詢語句Select name，value from v$sysstat where name=’redo log space requests’，查詢結(jié)果中的value值應(yīng)接近零，否則每次將LOG_BUFFERS增大5%再執(zhí)行上面的查詢，直到value值接近零。

4.3 oracle表空間調(diào)整

表空間概念是oracle數(shù)據(jù)庫系統(tǒng)的重要的邏輯概念，高效合理的使用表空間，有利于提高數(shù)據(jù)庫性能。1）合理分布表空間。在數(shù)據(jù)方面，應(yīng)分離數(shù)據(jù)和索引，減少I/O競(jìng)爭(zhēng)，在修改方面，應(yīng)分離重演日志和歸檔日志，在系統(tǒng)開銷方面，應(yīng)分離系統(tǒng)表空間、TOOLS表空間和TEMP表空間。盡量將90%以上的I/O操作集中在系統(tǒng)表空間、數(shù)據(jù)表空間、回滾表空間、索引表空間這4個(gè)表空間上，且這4個(gè)表空間應(yīng)單獨(dú)存儲(chǔ)在不同的磁盤上。2）表空間優(yōu)化結(jié)構(gòu)OFA。首先要分離系統(tǒng)表空間，盡量避免在系統(tǒng)表空間中存儲(chǔ)非系統(tǒng)用戶的對(duì)象，否則會(huì)增加產(chǎn)生數(shù)據(jù)庫維護(hù)和空間管理問題的可能性。一般而言，除了數(shù)據(jù)字典，其它能移出系統(tǒng)表空間的任何數(shù)據(jù)都應(yīng)該從中移出；其次分離索引段，索引段不應(yīng)與相關(guān)的數(shù)據(jù)段存儲(chǔ)在同一個(gè)表空間中，因?yàn)樗鼈冊(cè)跀?shù)據(jù)管理和查詢中存在許多I/O沖突。將索引段和數(shù)據(jù)段存儲(chǔ)在不同的表空間中能夠平衡兩者之間的負(fù)載，減少資源爭(zhēng)用，并能減少整理數(shù)據(jù)表或索引碎片所需的管理代價(jià)。再次分離臨時(shí)表空間，臨時(shí)段是數(shù)據(jù)庫中動(dòng)態(tài)生成的對(duì)象，用來存儲(chǔ)巨型排序操作的數(shù)據(jù)。由于它們的動(dòng)態(tài)特性，臨時(shí)段不應(yīng)與其他類型的段一起存儲(chǔ)。通常，在建立用戶時(shí)，將這些用戶使用的臨時(shí)數(shù)據(jù)段設(shè)置到臨時(shí)表空間。

[1]THOMAS KYTE.蘇金國(guó),王小振,等譯.ORACLE9I&10G編程藝術(shù)：深入數(shù)據(jù)庫體系結(jié)構(gòu)[M].人民郵電出版社，2006：265-269.

[2]孫風(fēng)棟,閆海珍.Oracle 10g數(shù)據(jù)庫系統(tǒng)性能優(yōu)化與調(diào)整[J].計(jì)算機(jī)技術(shù)與發(fā)展.2009,19(2)：83-86.

[3]蓋國(guó)強(qiáng),馮春培.Oracle數(shù)據(jù)庫性能優(yōu)化[M].北京：人民郵電出版社,2006.