基于Oracle的數據庫性能優化研究

賀鵬程

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

計算機技術與應用

基于Oracle的數據庫性能優化研究

賀鵬程

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

在Oracle數據庫設計中長期受到設計人員重視的是如何更好更快地提高Oracle數據庫性能的問題。其中對數據庫表現有較大關聯的是兩個因素，一是執行SQL語句的速度問題；二是數據庫的內存配置管理問題。通過優化SQL語句效率、擴充高級緩沖區和配置重做日志緩沖區等幾個方面介紹了Oracle數據庫優化方法，探討了Oracle如何提高性能優化的措施。通過測試Oracle查詢結果表明，經過優化后的Oracle數據庫縮短了響應時間，同時內存的使用效率也有了較大提升。

Oracle；性能優化；SQL查詢語句；內存優化

作為目前常見的Oracle數據庫系統，常被用于大型、海量數據的管理。因此，通過對Oracle數據庫系統的優化研究，對于提升信息系統管理效率和準確率方面具有巨大優勢［1-2］。但是對于Oracle數據庫系統的表現性能具有影響作用的因素有很多，不僅有軟件語言因素，也涉及到數據運行的硬件設施、網絡通信狀況以及數據維護管理等方面［3-5］。

Oracle數據庫系統優化的主要目標是減少磁盤輸入輸出操作、降低CPU占有率和減少運行時對系統資源的競爭，縮短用戶查詢響應時間以及提高系統對數據操作的吞吐量等［6-8］，其原則是“使用最低的資源，達到最高的效率”。為實現這一目標，文中對Oracle數據管理系統的優化從兩個方面進行研究：一個方面是優化數據系統中的SQL語句，第二個方面是優化Oracle數據系統內存管理，提高內存使用效率。

1　Oracle優化SQL語句

SQL語句是Oracle數據庫操作執行的最基礎部分，對系統表現性能的影響也最為直接［9］。隨著數據庫中數據量的增加，同樣的SQL語句執行速度會逐漸變慢，體現在系統的響應時間也會逐漸延長。同時低質SQL語句與高質SQL語句之間的效率差異也會逐漸變大，而這也往往是數據庫設計人員會忽視的，因此對SQL語句的優化直接關系到數據庫反映時間［10］。下面從SQL語句初始化參數設置、索引設置和多表連接時的過濾條件等幾個方面，對SQL語句執行效率進行優化研究。

1.1select子句避免使用“*”

Oracle數據系統在解析SQL語句過程中，會將標識符“*”轉換為數據所在表的所有列名。因此，SQL語句中使用“*”所增加數據操作包括所在數據表中的所有列，尤其當數據表中數據列數目較大時，SQL語句的執行所需要的時間將大大延長，從而導致查詢效率地降低。所以，在編寫SQL語句時，需要盡量較少使用“*”標識符次數，最好直接使用數據表中的特定列名。以Oracle數據系統所提供的emp數據為例，通過采用標識符“*”與直接指定數據列進行的查詢效率如表1所示。

表1　select子句中*優化

從表中執行時間可以看出，當數據行數為1024時采用直接指定數據列名能夠有效縮短系統查詢時間，優化效率達到45%。

1.2優化where子句的限制條件

在Oracle數據系統中，SQL語句中對where子句的解析順序為由后至前。因此，當SQL語句需要疊加多條where子語句時，應當首先考慮將能夠限制最多數據行的過濾條件放置到where的末尾，若存在表間的連接語句，則將其作為最先寫入的約束條件。同樣以emp數據為例，通過where不同的限制條件，得到的SQL執行效率如表2所示。

表2　where子句約束條件優化

從表2可以看出，對SQL語句的優化體現在表間連接語句的前置和兩個限制條件的顛倒。雖然執行結果一致，但執行時間被縮短了0.1 s。

1.3展平子查詢

所謂子查詢的“展平”是指將SQL語句中的子查詢轉變為連接、半連接子語句［11-12］。Oracle數據系統在執行SQL語句時，將首先計算最外層查詢所在的from子句關系直積數值，然后對該直積中的每個子元使用from子句對應的where限制條件濾除數據。鑒于子查詢需要單獨計算每一個直積中的子元，因此在迭代查詢中的I/O操作將頻繁執行，將極為影響語句的執行效率。所以，在Oracle數據系統中應當盡量減少查詢嵌套層數，盡量使“展平”技術替代子查詢語句，以此減少數據I/O操作，縮短語句執行時間。

以Oracle數據系統中emp數據為例，找出所有條件滿足且價格大于100的book的publication time和name。通過采用兩種查詢方式得到的Oracle系統執行效率如表3所示。

表3　展平子查詢優化

從表 3可以看出，通過將子查詢“select book from booklist where price＞100”轉變為查詢連接子表e，可以縮短查詢時間30%以上。

1.4用union替換or

在SQL語句中，應當盡量避免使用標識符or串接where子句的多個索引列，可以使用標識符union替換or［13］。其原因在于，當or標識符包括某索引列，SQL語句將在執行時將對全表進行掃描，但如果該列沒有被索引，則查詢效率將沒有改善；與此相反，若將or替換為union，SQL語句執行時將在表連接的過程中對數據進行查重過濾，去除掉多余數據行，從而提高檢索效率，避免了數據的反復查詢。

2　Oracle數據庫內存優化

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

2.1系統全局區配置

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

綜合考慮這些因素，以內存大小為4G的數據服務器為例，SGA大小應當約為1600～2000M。通過實驗證明，在對SHARED_POOL_SIZE和DB_BLOCK_BUFFER參數進行設置時，需要滿足的公式如（1）所示。

系統內存+SGA+并發執行進程數*（SHARED_POOL _SIZE+DB_BLOCK_BUFFER+2M）＜0.7*總內存（1）

2.2數據庫緩沖區

在Oracle數據系統中，系統通常使用緩沖區的命名率作為數據緩存區的使用效率的評價標準。查詢某緩沖區的命名率的SQL語句如下所示。

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語句的返回值為某數據塊請求次數除以Oracle系統中數據緩沖區所能提供該數據塊的次數的商值。因此當該值越大時，表明Oracle數據緩沖區中數據塊存在的概率越大，系統反應速度也越快。所以，通過提高數據庫中緩沖區數據塊值數目，將增加Oracle系統在緩沖區中找到對應數據

塊的概率，進一步提升數據查詢的速度。

在Oracle系統中，影響數據庫緩沖區大小的值主要為參數DB_BLOCK_SIZE，系統默認DB_BLOCK_SIZE為2KB。以8KB的數據庫為例，當DB_BLOCK_SIZE為2KB時，系統需要執行4次I/O操作才能將數據導入緩沖區內，而當DB_BLOCK_SIZE設置為8KB時，系統僅需要1次數據讀盤即可完成工作。

2.3重做日記緩沖區

重做日記緩沖區是Oracle數據系統中恢復數據庫操作記錄的區域，通過管理重做日記文件，能夠有效保障數據安全和提高系統穩定性。在Oracle系統中，通過參數LOG_BUFFER設置可以設定系統操作等待時間，也能夠調節重做日記緩沖區性能。在Oracle系統中，評價重做日記緩沖區性能的SQL語句如下所示。

SQL＞selectsubstr（name，1，20）“latch”，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將會顯示以下信息：

LatchImmediate Misses RatioWait Misses Ratio

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

Redo allocation00

Redo copy0.0070

其中Immediate Misses Ratio值代表Oracle系統重做缺失率。如果缺失率大于1%，則表明該系統操作執行出現了延遲過長導致緩沖區無法臨時保存數據，即所謂“閂鎖”。而解決這一問題的方法是，設置LOG_SIMULTANEOUS_COPIES值，增大系統平臺重置操作執行最大允許值，進而增加操作拷貝的閂鎖個數。

但當系統重做缺失率較低，而重做日志緩沖區的性能較差（表現在數據系統運行時恢復命令執行緩慢）時，通常是重做緩沖區空間過小或不足導致服務器進程無法獲得。其解決方法為將重做日記文件與數據文件做到存儲器分離，以此減少系統運行時對磁盤讀寫資源的競爭。

3　結束語

雖然Oracle數據庫性能調整難度較大，但是可以通過合理的參數設置對系統進行優化，從而達到提高系統反應性能、保持數據穩定安全的目的。對于以Oracle數據系統為基礎的信息應用，其運行速度、操作效率都與Oracle系統有直接關系。根據文中SQL語句的優化結果和Oracle系統內存優化研究可以看出，提高查詢語句的執行速率和優化設置Oracle內存管理對增強系統表現性能和縮短執行響應時間都有較大改善。數據庫的性能調整是一個較為復雜的系統工程，需要在各種不同情況下，通過綜合運用上述優化技術積累更多實踐經驗，進而提升數據庫性能。

［1］魏亞楠，宋義秋.Oracle數據庫應用系統的性能優化［J］.制造業自動化，2010，20（8）：131-133.

［2］任偉建，王子維，霍鳳財，等.基于Oracle數據庫的油田數據庫實時監控系統［J］.化工自動化及儀表，2015（12）：18-23.

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

［4］蔣年德，李英.Oracle環境下管理信息系統的優化設計［J］.計算機應用研究，2003，20（10）：131-133.

［5］柳園園.Oracle數據庫SQL優化原則［J］.電子技術與軟件工程，2015，21（7）：197-197.

［6］劉光霆.Oracle中SQL查詢優化研究［J］.計算機與信息技術，2008，32（5）：32-35.

［7］王霞，閆愛平，李娜.大對象數據的存取在Oracle數據庫中的設計與實現［J］.電子技術與軟件工程，2015，47（6）：203.

［8］劉曉丹.基于Oracle分布式數據庫的查詢算法改進研究［J］.自動化與儀器儀表，2015，47（11）：67-75.

［9］韋安云.Oracle數據庫的性能調整與優化方法探析［J］.電腦知識與技術，2015（21）：41-44.

［10］吳秀君.基于ORACLECDC技術實現數據自動同步［J］.電腦知識與技術，2015（27）：23-28.

［11］冷鵬.自動跟蹤技術在Oracle數據庫維護中的應用分析［J］.電子技術與軟件工程，2015（24）：72-77.

［12］張標，金英.Oracle性能調整［J］.煤炭技術，2007（2）：81-85.

［13］宋霞.Oracle疑難錯誤解析［J］.山東省農業管理干部學院學報，2007（6）：54-58.

［14］李學忠.Oracle數據庫的優化使用［J］.電子工藝技術，2003 （5）：11-15.

［15］屈遠林.Oracle編程的常見問題及解決方法［J］.鐵路計算機應用，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稿件編號：201601141

國家自然科學基金（61032001）

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