基于Oracle的數(shù)據(jù)庫(kù)性能優(yōu)化研究

賀鵬程

（海軍裝備部駐重慶地區(qū)軍事代表局 重慶　400042）

計(jì)算機(jī)技術(shù)與應(yīng)用

基于Oracle的數(shù)據(jù)庫(kù)性能優(yōu)化研究

賀鵬程

（海軍裝備部駐重慶地區(qū)軍事代表局 重慶400042）

在Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)中長(zhǎng)期受到設(shè)計(jì)人員重視的是如何更好更快地提高Oracle數(shù)據(jù)庫(kù)性能的問(wèn)題。其中對(duì)數(shù)據(jù)庫(kù)表現(xiàn)有較大關(guān)聯(lián)的是兩個(gè)因素，一是執(zhí)行SQL語(yǔ)句的速度問(wèn)題；二是數(shù)據(jù)庫(kù)的內(nèi)存配置管理問(wèn)題。通過(guò)優(yōu)化SQL語(yǔ)句效率、擴(kuò)充高級(jí)緩沖區(qū)和配置重做日志緩沖區(qū)等幾個(gè)方面介紹了Oracle數(shù)據(jù)庫(kù)優(yōu)化方法，探討了Oracle如何提高性能優(yōu)化的措施。通過(guò)測(cè)試Oracle查詢結(jié)果表明，經(jīng)過(guò)優(yōu)化后的Oracle數(shù)據(jù)庫(kù)縮短了響應(yīng)時(shí)間，同時(shí)內(nèi)存的使用效率也有了較大提升。

Oracle；性能優(yōu)化；SQL查詢語(yǔ)句；內(nèi)存優(yōu)化

作為目前常見(jiàn)的Oracle數(shù)據(jù)庫(kù)系統(tǒng)，常被用于大型、海量數(shù)據(jù)的管理。因此，通過(guò)對(duì)Oracle數(shù)據(jù)庫(kù)系統(tǒng)的優(yōu)化研究，對(duì)于提升信息系統(tǒng)管理效率和準(zhǔn)確率方面具有巨大優(yōu)勢(shì)［1-2］。但是對(duì)于Oracle數(shù)據(jù)庫(kù)系統(tǒng)的表現(xiàn)性能具有影響作用的因素有很多，不僅有軟件語(yǔ)言因素，也涉及到數(shù)據(jù)運(yùn)行的硬件設(shè)施、網(wǎng)絡(luò)通信狀況以及數(shù)據(jù)維護(hù)管理等方面［3-5］。

Oracle數(shù)據(jù)庫(kù)系統(tǒng)優(yōu)化的主要目標(biāo)是減少磁盤輸入輸出操作、降低CPU占有率和減少運(yùn)行時(shí)對(duì)系統(tǒng)資源的競(jìng)爭(zhēng)，縮短用戶查詢響應(yīng)時(shí)間以及提高系統(tǒng)對(duì)數(shù)據(jù)操作的吞吐量等［6-8］，其原則是“使用最低的資源，達(dá)到最高的效率”。為實(shí)現(xiàn)這一目標(biāo)，文中對(duì)Oracle數(shù)據(jù)管理系統(tǒng)的優(yōu)化從兩個(gè)方面進(jìn)行研究：一個(gè)方面是優(yōu)化數(shù)據(jù)系統(tǒng)中的SQL語(yǔ)句，第二個(gè)方面是優(yōu)化Oracle數(shù)據(jù)系統(tǒng)內(nèi)存管理，提高內(nèi)存使用效率。

1　Oracle優(yōu)化SQL語(yǔ)句

SQL語(yǔ)句是Oracle數(shù)據(jù)庫(kù)操作執(zhí)行的最基礎(chǔ)部分，對(duì)系統(tǒng)表現(xiàn)性能的影響也最為直接［9］。隨著數(shù)據(jù)庫(kù)中數(shù)據(jù)量的增加，同樣的SQL語(yǔ)句執(zhí)行速度會(huì)逐漸變慢，體現(xiàn)在系統(tǒng)的響應(yīng)時(shí)間也會(huì)逐漸延長(zhǎng)。同時(shí)低質(zhì)SQL語(yǔ)句與高質(zhì)SQL語(yǔ)句之間的效率差異也會(huì)逐漸變大，而這也往往是數(shù)據(jù)庫(kù)設(shè)計(jì)人員會(huì)忽視的，因此對(duì)SQL語(yǔ)句的優(yōu)化直接關(guān)系到數(shù)據(jù)庫(kù)反映時(shí)間［10］。下面從SQL語(yǔ)句初始化參數(shù)設(shè)置、索引設(shè)置和多表連接時(shí)的過(guò)濾條件等幾個(gè)方面，對(duì)SQL語(yǔ)句執(zhí)行效率進(jìn)行優(yōu)化研究。

1.1select子句避免使用“*”

Oracle數(shù)據(jù)系統(tǒng)在解析SQL語(yǔ)句過(guò)程中，會(huì)將標(biāo)識(shí)符“*”轉(zhuǎn)換為數(shù)據(jù)所在表的所有列名。因此，SQL語(yǔ)句中使用“*”所增加數(shù)據(jù)操作包括所在數(shù)據(jù)表中的所有列，尤其當(dāng)數(shù)據(jù)表中數(shù)據(jù)列數(shù)目較大時(shí)，SQL語(yǔ)句的執(zhí)行所需要的時(shí)間將大大延長(zhǎng)，從而導(dǎo)致查詢效率地降低。所以，在編寫SQL語(yǔ)句時(shí)，需要盡量較少使用“*”標(biāo)識(shí)符次數(shù)，最好直接使用數(shù)據(jù)表中的特定列名。以O(shè)racle數(shù)據(jù)系統(tǒng)所提供的emp數(shù)據(jù)為例，通過(guò)采用標(biāo)識(shí)符“*”與直接指定數(shù)據(jù)列進(jìn)行的查詢效率如表1所示。

表1　select子句中*優(yōu)化

從表中執(zhí)行時(shí)間可以看出，當(dāng)數(shù)據(jù)行數(shù)為1024時(shí)采用直接指定數(shù)據(jù)列名能夠有效縮短系統(tǒng)查詢時(shí)間，優(yōu)化效率達(dá)到45%。

1.2優(yōu)化where子句的限制條件

在Oracle數(shù)據(jù)系統(tǒng)中，SQL語(yǔ)句中對(duì)where子句的解析順序?yàn)橛珊笾燎?。因此，?dāng)SQL語(yǔ)句需要疊加多條where子語(yǔ)句時(shí)，應(yīng)當(dāng)首先考慮將能夠限制最多數(shù)據(jù)行的過(guò)濾條件放置到where的末尾，若存在表間的連接語(yǔ)句，則將其作為最先寫入的約束條件。同樣以emp數(shù)據(jù)為例，通過(guò)where不同的限制條件，得到的SQL執(zhí)行效率如表2所示。

表2　where子句約束條件優(yōu)化

從表2可以看出，對(duì)SQL語(yǔ)句的優(yōu)化體現(xiàn)在表間連接語(yǔ)句的前置和兩個(gè)限制條件的顛倒。雖然執(zhí)行結(jié)果一致，但執(zhí)行時(shí)間被縮短了0.1 s。

1.3展平子查詢

所謂子查詢的“展平”是指將SQL語(yǔ)句中的子查詢轉(zhuǎn)變?yōu)檫B接、半連接子語(yǔ)句［11-12］。Oracle數(shù)據(jù)系統(tǒng)在執(zhí)行SQL語(yǔ)句時(shí)，將首先計(jì)算最外層查詢所在的from子句關(guān)系直積數(shù)值，然后對(duì)該直積中的每個(gè)子元使用from子句對(duì)應(yīng)的where限制條件濾除數(shù)據(jù)。鑒于子查詢需要單獨(dú)計(jì)算每一個(gè)直積中的子元，因此在迭代查詢中的I/O操作將頻繁執(zhí)行，將極為影響語(yǔ)句的執(zhí)行效率。所以，在Oracle數(shù)據(jù)系統(tǒng)中應(yīng)當(dāng)盡量減少查詢嵌套層數(shù)，盡量使“展平”技術(shù)替代子查詢語(yǔ)句，以此減少數(shù)據(jù)I/O操作，縮短語(yǔ)句執(zhí)行時(shí)間。

以O(shè)racle數(shù)據(jù)系統(tǒng)中emp數(shù)據(jù)為例，找出所有條件滿足且價(jià)格大于100的book的publication time和name。通過(guò)采用兩種查詢方式得到的Oracle系統(tǒng)執(zhí)行效率如表3所示。

表3　展平子查詢優(yōu)化

從表 3可以看出，通過(guò)將子查詢“select book from booklist where price＞100”轉(zhuǎn)變?yōu)椴樵冞B接子表e，可以縮短查詢時(shí)間30%以上。

1.4用union替換or

在SQL語(yǔ)句中，應(yīng)當(dāng)盡量避免使用標(biāo)識(shí)符or串接where子句的多個(gè)索引列，可以使用標(biāo)識(shí)符union替換or［13］。其原因在于，當(dāng)or標(biāo)識(shí)符包括某索引列，SQL語(yǔ)句將在執(zhí)行時(shí)將對(duì)全表進(jìn)行掃描，但如果該列沒(méi)有被索引，則查詢效率將沒(méi)有改善；與此相反，若將or替換為union，SQL語(yǔ)句執(zhí)行時(shí)將在表連接的過(guò)程中對(duì)數(shù)據(jù)進(jìn)行查重過(guò)濾，去除掉多余數(shù)據(jù)行，從而提高檢索效率，避免了數(shù)據(jù)的反復(fù)查詢。

2　Oracle數(shù)據(jù)庫(kù)內(nèi)存優(yōu)化

Oracle數(shù)據(jù)系統(tǒng)的第二個(gè)優(yōu)化方面是數(shù)據(jù)庫(kù)內(nèi)存系統(tǒng)的管理優(yōu)化。通過(guò)Oracle數(shù)據(jù)庫(kù)內(nèi)存使用效率的提升，可以減少Oracle數(shù)據(jù)庫(kù)所占內(nèi)存容量，從而減少數(shù)據(jù)的輸入輸出操作，進(jìn)而縮短系統(tǒng)響應(yīng)時(shí)間［14］。Oracle數(shù)據(jù)系統(tǒng)的內(nèi)存配置主要由兩個(gè)區(qū)間組成，分別為系統(tǒng)全局區(qū)（System Global Area）和進(jìn)程全局區(qū)（Process Global Area），但其中對(duì)Oracle數(shù)據(jù)系統(tǒng)性能影響較大的為SGA、數(shù)據(jù)緩沖區(qū)和日記緩沖區(qū)。下面分別對(duì)這3個(gè)方面進(jìn)行內(nèi)存配置的優(yōu)化研究。

2.1系統(tǒng)全局區(qū)配置

Oracle數(shù)據(jù)系統(tǒng)對(duì)于內(nèi)存大小要求較為嚴(yán)格。系統(tǒng)在運(yùn)行時(shí)，需要將SGA設(shè)置為占有25～50%左右［15］。需要注意的是，在設(shè)置SGA的占用比時(shí)，也要保證平衡Oracle數(shù)據(jù)系統(tǒng)和其他應(yīng)用程序的內(nèi)存要求。因此，需要合理設(shè)置Oracle中所提供的SHARED_POOL_SIZE和DB_BLOCK_BUFFER兩個(gè)變量值來(lái)控制SGA的規(guī)模。與此同時(shí)，當(dāng)共享池設(shè)置過(guò)大時(shí)，操作系統(tǒng)中的部分進(jìn)程或者Oracle中的SGA將被迫與內(nèi)存buffer置換，從而增加額外的數(shù)據(jù)操作，導(dǎo)致系統(tǒng)反應(yīng)遲緩造成Oracle系統(tǒng)的性能不穩(wěn)。

綜合考慮這些因素，以內(nèi)存大小為4G的數(shù)據(jù)服務(wù)器為例，SGA大小應(yīng)當(dāng)約為1600～2000M。通過(guò)實(shí)驗(yàn)證明，在對(duì)SHARED_POOL_SIZE和DB_BLOCK_BUFFER參數(shù)進(jìn)行設(shè)置時(shí)，需要滿足的公式如（1）所示。

系統(tǒng)內(nèi)存+SGA+并發(fā)執(zhí)行進(jìn)程數(shù)*（SHARED_POOL _SIZE+DB_BLOCK_BUFFER+2M）＜0.7*總內(nèi)存（1）

2.2數(shù)據(jù)庫(kù)緩沖區(qū)

在Oracle數(shù)據(jù)系統(tǒng)中，系統(tǒng)通常使用緩沖區(qū)的命名率作為數(shù)據(jù)緩存區(qū)的使用效率的評(píng)價(jià)標(biāo)準(zhǔn)。查詢某緩沖區(qū)的命名率的SQL語(yǔ)句如下所示。

SQL＞select 1-sum（decode（name，‘Physical reads'，value，0））/（sum（decode（name，‘db block gets'，value，0）））

“HitRatio”fromGV_$SYSSTATwherenamein（‘physical reads'，‘db block gets'，‘consistent gets'）；

Hit Ratio

0.99863427

該SQL語(yǔ)句的返回值為某數(shù)據(jù)塊請(qǐng)求次數(shù)除以O(shè)racle系統(tǒng)中數(shù)據(jù)緩沖區(qū)所能提供該數(shù)據(jù)塊的次數(shù)的商值。因此當(dāng)該值越大時(shí)，表明Oracle數(shù)據(jù)緩沖區(qū)中數(shù)據(jù)塊存在的概率越大，系統(tǒng)反應(yīng)速度也越快。所以，通過(guò)提高數(shù)據(jù)庫(kù)中緩沖區(qū)數(shù)據(jù)塊值數(shù)目，將增加Oracle系統(tǒng)在緩沖區(qū)中找到對(duì)應(yīng)數(shù)據(jù)

塊的概率，進(jìn)一步提升數(shù)據(jù)查詢的速度。

在Oracle系統(tǒng)中，影響數(shù)據(jù)庫(kù)緩沖區(qū)大小的值主要為參數(shù)DB_BLOCK_SIZE，系統(tǒng)默認(rèn)DB_BLOCK_SIZE為2KB。以8KB的數(shù)據(jù)庫(kù)為例，當(dāng)DB_BLOCK_SIZE為2KB時(shí)，系統(tǒng)需要執(zhí)行4次I/O操作才能將數(shù)據(jù)導(dǎo)入緩沖區(qū)內(nèi)，而當(dāng)DB_BLOCK_SIZE設(shè)置為8KB時(shí)，系統(tǒng)僅需要1次數(shù)據(jù)讀盤即可完成工作。

2.3重做日記緩沖區(qū)

重做日記緩沖區(qū)是Oracle數(shù)據(jù)系統(tǒng)中恢復(fù)數(shù)據(jù)庫(kù)操作記錄的區(qū)域，通過(guò)管理重做日記文件，能夠有效保障數(shù)據(jù)安全和提高系統(tǒng)穩(wěn)定性。在Oracle系統(tǒng)中，通過(guò)參數(shù)LOG_BUFFER設(shè)置可以設(shè)定系統(tǒng)操作等待時(shí)間，也能夠調(diào)節(jié)重做日記緩沖區(qū)性能。在Oracle系統(tǒng)中，評(píng)價(jià)重做日記緩沖區(qū)性能的SQL語(yǔ)句如下所示。

SQL＞selectsubstr（name，1，20）“l(fā)atch”，immediate_misses/decode（（immediate_get+ immediate_misses）），0，1，（immediate_get+ immediate_misses））“Immediate Misses Ratio”，misses/decode（（gets+misses），0，1，（gets+misses））“Wait Misses Ratio”fromV$LATCHwherenamein（‘redocopy'，‘redo allocation'）；

Oracle將會(huì)顯示以下信息：

LatchImmediate Misses RatioWait Misses Ratio

----------------------------

Redo allocation00

Redo copy0.0070

其中Immediate Misses Ratio值代表Oracle系統(tǒng)重做缺失率。如果缺失率大于1%，則表明該系統(tǒng)操作執(zhí)行出現(xiàn)了延遲過(guò)長(zhǎng)導(dǎo)致緩沖區(qū)無(wú)法臨時(shí)保存數(shù)據(jù)，即所謂“閂鎖”。而解決這一問(wèn)題的方法是，設(shè)置LOG_SIMULTANEOUS_COPIES值，增大系統(tǒng)平臺(tái)重置操作執(zhí)行最大允許值，進(jìn)而增加操作拷貝的閂鎖個(gè)數(shù)。

但當(dāng)系統(tǒng)重做缺失率較低，而重做日志緩沖區(qū)的性能較差（表現(xiàn)在數(shù)據(jù)系統(tǒng)運(yùn)行時(shí)恢復(fù)命令執(zhí)行緩慢）時(shí)，通常是重做緩沖區(qū)空間過(guò)小或不足導(dǎo)致服務(wù)器進(jìn)程無(wú)法獲得。其解決方法為將重做日記文件與數(shù)據(jù)文件做到存儲(chǔ)器分離，以此減少系統(tǒng)運(yùn)行時(shí)對(duì)磁盤讀寫資源的競(jìng)爭(zhēng)。

3　結(jié)束語(yǔ)

雖然Oracle數(shù)據(jù)庫(kù)性能調(diào)整難度較大，但是可以通過(guò)合理的參數(shù)設(shè)置對(duì)系統(tǒng)進(jìn)行優(yōu)化，從而達(dá)到提高系統(tǒng)反應(yīng)性能、保持?jǐn)?shù)據(jù)穩(wěn)定安全的目的。對(duì)于以O(shè)racle數(shù)據(jù)系統(tǒng)為基礎(chǔ)的信息應(yīng)用，其運(yùn)行速度、操作效率都與Oracle系統(tǒng)有直接關(guān)系。根據(jù)文中SQL語(yǔ)句的優(yōu)化結(jié)果和Oracle系統(tǒng)內(nèi)存優(yōu)化研究可以看出，提高查詢語(yǔ)句的執(zhí)行速率和優(yōu)化設(shè)置Oracle內(nèi)存管理對(duì)增強(qiáng)系統(tǒng)表現(xiàn)性能和縮短執(zhí)行響應(yīng)時(shí)間都有較大改善。數(shù)據(jù)庫(kù)的性能調(diào)整是一個(gè)較為復(fù)雜的系統(tǒng)工程，需要在各種不同情況下，通過(guò)綜合運(yùn)用上述優(yōu)化技術(shù)積累更多實(shí)踐經(jīng)驗(yàn)，進(jìn)而提升數(shù)據(jù)庫(kù)性能。

［1］魏亞楠，宋義秋.Oracle數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的性能優(yōu)化［J］.制造業(yè)自動(dòng)化，2010，20（8）：131-133.

［2］任偉建，王子維，霍鳳財(cái)，等.基于Oracle數(shù)據(jù)庫(kù)的油田數(shù)據(jù)庫(kù)實(shí)時(shí)監(jiān)控系統(tǒng)［J］.化工自動(dòng)化及儀表，2015（12）：18-23.

［3］高旭.Oracle數(shù)據(jù)庫(kù)段空間收縮的研究［J］.中小企業(yè)管理與科技，2015，31（12）：233-234.

［4］蔣年德，李英.Oracle環(huán)境下管理信息系統(tǒng)的優(yōu)化設(shè)計(jì)［J］.計(jì)算機(jī)應(yīng)用研究，2003，20（10）：131-133.

［5］柳園園.Oracle數(shù)據(jù)庫(kù)SQL優(yōu)化原則［J］.電子技術(shù)與軟件工程，2015，21（7）：197-197.

［6］劉光霆.Oracle中SQL查詢優(yōu)化研究［J］.計(jì)算機(jī)與信息技術(shù)，2008，32（5）：32-35.

［7］王霞，閆愛(ài)平，李娜.大對(duì)象數(shù)據(jù)的存取在Oracle數(shù)據(jù)庫(kù)中的設(shè)計(jì)與實(shí)現(xiàn)［J］.電子技術(shù)與軟件工程，2015，47（6）：203.

［8］劉曉丹.基于Oracle分布式數(shù)據(jù)庫(kù)的查詢算法改進(jìn)研究［J］.自動(dòng)化與儀器儀表，2015，47（11）：67-75.

［9］韋安云.Oracle數(shù)據(jù)庫(kù)的性能調(diào)整與優(yōu)化方法探析［J］.電腦知識(shí)與技術(shù)，2015（21）：41-44.

［10］吳秀君.基于ORACLECDC技術(shù)實(shí)現(xiàn)數(shù)據(jù)自動(dòng)同步［J］.電腦知識(shí)與技術(shù)，2015（27）：23-28.

［11］冷鵬.自動(dòng)跟蹤技術(shù)在Oracle數(shù)據(jù)庫(kù)維護(hù)中的應(yīng)用分析［J］.電子技術(shù)與軟件工程，2015（24）：72-77.

［12］張標(biāo)，金英.Oracle性能調(diào)整［J］.煤炭技術(shù)，2007（2）：81-85.

［13］宋霞.Oracle疑難錯(cuò)誤解析［J］.山東省農(nóng)業(yè)管理干部學(xué)院學(xué)報(bào)，2007（6）：54-58.

［14］李學(xué)忠.Oracle數(shù)據(jù)庫(kù)的優(yōu)化使用［J］.電子工藝技術(shù)，2003 （5）：11-15.

［15］屈遠(yuǎn)林.Oracle編程的常見(jiàn)問(wèn)題及解決方法［J］.鐵路計(jì)算機(jī)應(yīng)用，2000（3）：16-19.

Research on optimization of Oracle database

HE Peng-cheng
（Military Representative Bureau of Naval Equipment Department in Chongqing Area，Chongqing 400042，China）

The problem of Oracle database's performance optimization has been the focal point which the decision-maker and the technician show solicitude for commonly，and two key factors affecting the database's performance are that low efficiency of the SQL query sentence and management of Oracle's memory.Several kinds of common Oracle database's performance optimization techniques were elaborated，such as database design's optimization，the adjustment of various dynamic parameters of database，and the SQL sentence query optimization was discussed emphatically.Judging from the results of query optimization test，the system's response time reduces obviously and the system's operation efficiency obtains the promotion.

oracle database；performance optimization；SQL query optimization；parameter optimize

TP392

A

1674－6236（2016）09-0001-03

2016-01-18稿件編號(hào)：201601141

國(guó)家自然科學(xué)基金（61032001）

賀鵬程（1985—），男，四川成都人，碩士，工程師。研究方向：軍事裝備學(xué)。