Oracle數據庫性能優化淺析

王道奇 何楊 王柯 任艷艷 吳昶 郭會敏

摘 ?要：隨著運行時間增長、業務量增加以及硬件、操作系統、應用程序等方面原因，數據庫性能在運行一段時間后均會下降， Oracle數據庫因體系結構復雜、細節龐雜，其性能優化會有不小困難。本文基于Oracle數據庫運行情況，從數據庫設計、內存參數、常駐內存、SQL優化、鎖爭用五個方面入手，提出一套具有實際參考價值的數據庫優化策略。

關鍵詞：Oracle數據庫;性能優化;優化策略

1 前言

隨著信息技術快速發展，企業信息化管理程度也越來越高，幾乎所有企業都會采用數據庫實現海量業務數據的分類存儲與管理應用。數據庫作為底層核心應用系統，其性能好壞對上層業務應用起著關鍵作用。本文通過分析Oracle數據庫（以下簡稱數據庫）運行情況，探索其性能優化方法。

2 Oracle數據庫概述

數據庫由磁盤控制文件、數據文件、日志文件、參數文件等組成的物理文件集合，存儲結構分為邏輯與物理存儲。邏輯存儲描述其內部組織和管理數據方式，物理存儲展示其在操作系統中的物理文件組成情況。啟動數據庫實際是在服務器內存中創建一個實例，用實例訪問和控制磁盤數據文件。用戶連接到數據庫時，實際連接的是實例，由實例和數據庫進行通信，再將處理結果反饋給用戶。

3 Oracle優化技術

數據庫優化是在同樣硬件資源下提升數據庫執行速度，減少系統響應與I/O競爭，提高用戶訪問效率。

3.1 數據庫設計

開發應用程序，數據建模要先于所有設計任務，所有后續應用設計都會以數據建模為基礎，應用程序最終性能會受到數據模型制約。數據庫中，數據模型幾乎無法改動，盡早對數據建模優化是項非常關鍵的任務。數據庫設計盡可能遵循三大范式，實現冗余較小且結構合理。

3.2 內存優化

數據庫內存配置是否合理將影響數據庫整體性能。Oracle內存主要包括SGA（系統全局區）和PGA（程序全局區），而內存優化主要是對系統影響較大的SGA進行合理調整。

3.2.1 SGA與PGA概述

SGA與PGA都是數據庫為會話而在服務器內存中分配的區域。SGA是一組包含一個數據庫實例數據和控制信息的內存結構，由所有服務進程和線程共享。PGA是每個服務進程、線程的專用內存，不允許系統中其它進程或線程訪問，是獨立于SGA的私有空間。

3.2.2 內存現狀分析

查詢數據庫中執行低效的SQL語句，系統響應1880ms;

select executions， disk_reads， buffer_gets，round（（buffer_gets-disk_reads）/

buffer_gets，2） hit_radio， round（disk_reads/executions，2） reads_per_run， sql_text

from v$sqlarea where executions>0 and buffer_gets >0 and（buffer_gets-disk_reads）/

buffer_gets <0.8

調整數據庫內存參數，擴充數據緩沖區。通過select * from v$sga查詢數據庫內存配置：SGA共584M（固定組件1.2M、可變塊108M、數據緩沖區468M、重做日志緩沖區6.8M）、PGA共399M。

3.2.3 內存優化測試

1）根據V$db_cache_advice保存的數據緩存區內存信息調整Buffer Cache大小;

select size_for_estimate.buffers_for_estimate from v$db_cache_advice where name

=‘DEFAULT’andvice_status=‘ON’and block_size=（select value from v$parameter

where name =‘db_block_size’）

2）通過V$db_cache_advice查詢生產運行管理平臺物理服務器內存，現空間不能滿足系統需要，經調試：增加SGA緩存至800M（固定組件1M、可變塊100M、數據緩沖區400M、日志緩沖區300M），PGA增加至1G，余下內存給操作系統及其它應用，以提高Buffer cache命中率，緩解數據庫I/O壓力;

3）結果表明：內存優化后，執行上述測試SQL，響應減少320ms，系統性能提高17%，。

3.3 常駐內存

針對頻繁使用的表和索引，使其常駐內存：將物理I/O讀取轉換為邏輯存儲，避免對表與索引訪問產生頻繁磁盤I/O行為。若不需要頻繁訪問該表和索引，將其從內存移除。將常駐內存空間設置成128M，用于存儲表與索引。

show parameter keep;alter system set db_keep_cache_size=128M

3.3.1 測試將表設置為常駐內存對系統性能的影響

1）查詢2014年至2016年某井油壓、套壓、輸出壓力、井口溫度、計量溫度等數據。系統響應13649ms;

select * from scyx.itb_djscsj_temp where scrq between to_date（'2014-01-01'，'yyyy-mm-dd'） and ?to_date（'2016-01-31'，'yyyy-mm-dd'）

2）查詢表itb_djscsj_temp存放位置，結果表明其存放在默認緩沖池中;

select segment_name，secment_type，buffer_pool from user_segments

where secment_type = 'table' and secment_name = 'itb_djscsj_temp'

3）將表itb_djscsj_temp從默認緩沖池存入保持池（常駐內存）;

alter table scyx.itb_djscsj_temp storage （buffer_pool keep）

4） 確認表是否存入常駐內存，從BUFFER_POOL的值為keep可以知道，表已經設置到常駐內存;

5）結果表明：表常駐內存后，執行上述測試SQL，系統響應8327ms，性能提升39%。

3.3.2 測試將索引設置為常駐內存對系統性能的影響

1）為表itb_djscsj_temp創建索引;

create index scys.i_itb_djscsj_temp on itb_djscsj_temp（dwdm，jdm，scrq，cql） tablespace scyx_data

2）創建索引后，通過該索引查詢表，測試系統響應7281ms：

select dwdm，jdm，scrq，cql from scyx.itb_djscsj_temp where scrq between to_date（'2010-01-01'，'yyyy-mm-dd'）and to_date（'2017-01-31'，'yyyy-mm-dd'）

3）將該條索引存入保持池中（常駐內存）

alter index i_itb_djscsj_temp storage（buffer_pool keep）

4）確認索引是否成功存入到保持池中（常駐內存），從BUFFER_POOL值為keep可以得知，表已經設置到保持池（常駐內存）;

select segment_name，segment_type，buffer_pool from user_segments where segment_type='index'and segment_name= 'i_itb_djscsj_temp'

5）結果表明：索引常駐內存后，執行上述測試SQL，系統響應減少765ms，性能提升11%。

3.4 SQL優化

數據庫所有操作均通過SQL執行，執行低效的SQL會嚴重消耗系統資源，因此有必要整合SQL，減少表訪問次數。在編寫SQL語句時應從索引、關聯與子查詢、排序、分組、集合、并行SQL、DML等方面考慮。

1）查詢某作業區所有單井生產數據及總產氣量和產水量，系統響應4160ms;

Select sum（cql），sum（csl）as csl，to_char（scrq，’yyyy-mm-dd’）as scrq，t.* from itb_djscsj t Where dwdm in（’1082401000’）Group by dwdm，jdw，scrq

2）使用視圖封裝該作業區單井日生產數據，通過日期過濾其他功能應用;

Create view ivi_djscsj_jy as Select dwdm，jdm，scrq，sum（cql）as cql，sum（csl） as csl

Where dwdm in（’1082401000’）Group by dwdm，jdw，scrq;

Select dwdm，cql.csl from ivi_djscsj_jy where scrq=trunc（sysdate）

3）結果表明：執行上述測試SQL的，系統響應減少2190ms，性能提升53%。

3.5 鎖爭用

由于應用程序的DML操作導致的鎖，并不是在繁忙的數據庫中可以看到的唯一的鎖。數據庫使用鎖來使一些內部行為保持同步，例如修改數據字典，可能引發內部鎖爭用，導致業務應用系統運行緩慢或不能訪問等問題。

數據庫有兩種基本鎖：排它鎖和共享鎖。當數據對象被加上排它鎖時，其他事務不能對它讀取和修改。當數據對象加了共享鎖可以被其他事務讀取，但不能修改。數據庫利用這兩種基本鎖對數據庫事務進行并發控制。我們通過如下語句來解決因數據庫鎖導致的問題：

1）查詢數據庫被鎖對象信息;

select object_name，machine，s.sid，s.serial# from v$locked_object l，dba_objects o ，v$session s where l.object_id=o.object_id and l.session_id=s.sid

2）移除鎖進程;

移除批量鎖：alter system kill session 'sid，serial#'

移除單個鎖：alter system kill session '100，200'

通過移除不合理的鎖，減少鎖競爭，解決死鎖帶來的數據阻塞、性能低下等問題，確保數據庫高效運行。

4 結論

1）數據庫設計階段，應針對不同業務場景使用合理的設計范式，使系統性能最佳、開銷最小;

2）數據庫對內存參數設置較為嚴格，將SGA大小設置為數據庫總內存空間的30～50%，并滿足：系統內存+SGA+并發執行進程數*（shared_pool_size+db_block_buffer）<0.7*服務器總內存，才能平衡數據庫與其他應用程序內存需求;

3）將表與索引常駐內存，查詢表及索引響應均有效提升，表明此方法能增加系統資源利用率;

4）遵循SQL編寫原則（避免全表搜索、格式轉換、模糊匹配等），SQL執行速率會大幅提高;

5）使用鎖管理數據庫內部操作，在一定程度上能防止違反事物一致性的兩個會話并發操作同一對象，保護共享內存中的數據。

5結束語

數據庫運行一段時間后均會出現不同程度的性能問題。隨著系統應用集成工作開展，只有做好數據庫底層工作，提升其應用性、可靠性與健壯性，才能為后續數據過濾、清洗、挖掘與分析提供有力的數據服務保障。

參考文獻

[1]任偉建，王子維，霍鳳財，等.基于Oracle數據庫的油田數據庫實時監控系統[J].化工自動化及儀表，2015

[2]江李兵，江川寧. Oracle數據庫的性能調整與優化方法探析[J].計算機光盤軟件與應用，2014

[3]于穎. Oracle的性能調整與優化分析[J].信息安全與技術，2013

[4]Guy Harrison.Oracle性能優化求生指南.人民郵電出版社，2012

作者簡介：王道奇，男，1990年生，工程師;長期從事油氣田信息技術研究、信息規劃設計與信息化項目建設等工作。