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

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

關鍵詞：Oracle數據庫;性能優(yōu)化;優(yōu)化策略

1 前言

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

2 Oracle數據庫概述

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

3 Oracle優(yōu)化技術

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

3.1 數據庫設計

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

3.2 內存優(yōu)化

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

3.2.1 SGA與PGA概述

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

3.2.2 內存現狀分析

查詢數據庫中執(zhí)行低效的SQL語句，系統(tǒng)響應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

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

3.2.3 內存優(yōu)化測試

1）根據V$db_cache_advice保存的數據緩存區(qū)內存信息調整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查詢生產運行管理平臺物理服務器內存，現空間不能滿足系統(tǒng)需要，經調試：增加SGA緩存至800M（固定組件1M、可變塊100M、數據緩沖區(qū)400M、日志緩沖區(qū)300M），PGA增加至1G，余下內存給操作系統(tǒng)及其它應用，以提高Buffer cache命中率，緩解數據庫I/O壓力;

3）結果表明：內存優(yōu)化后，執(zhí)行上述測試SQL，響應減少320ms，系統(tǒng)性能提高17%，。

3.3 常駐內存

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

show parameter keep;alter system set db_keep_cache_size=128M

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

1）查詢2014年至2016年某井油壓、套壓、輸出壓力、井口溫度、計量溫度等數據。系統(tǒng)響應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）結果表明：表常駐內存后，執(zhí)行上述測試SQL，系統(tǒng)響應8327ms，性能提升39%。

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

1）為表itb_djscsj_temp創(chuàng)建索引;

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

2）創(chuàng)建索引后，通過該索引查詢表，測試系統(tǒng)響應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）結果表明：索引常駐內存后，執(zhí)行上述測試SQL，系統(tǒng)響應減少765ms，性能提升11%。

3.4 SQL優(yōu)化

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

1）查詢某作業(yè)區(qū)所有單井生產數據及總產氣量和產水量，系統(tǒng)響應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）使用視圖封裝該作業(yè)區(qū)單井日生產數據，通過日期過濾其他功能應用;

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）結果表明：執(zhí)行上述測試SQL的，系統(tǒng)響應減少2190ms，性能提升53%。

3.5 鎖爭用

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

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

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）數據庫設計階段，應針對不同業(yè)務場景使用合理的設計范式，使系統(tǒng)性能最佳、開銷最小;

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

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

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

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

5結束語

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

參考文獻

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

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

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

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

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