Oracle數(shù)據(jù)庫性能優(yōu)化淺析

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

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

關(guān)鍵詞：Oracle數(shù)據(jù)庫;性能優(yōu)化;優(yōu)化策略

1 前言

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

2 Oracle數(shù)據(jù)庫概述

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

3 Oracle優(yōu)化技術(shù)

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

3.1 數(shù)據(jù)庫設(shè)計(jì)

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

3.2 內(nèi)存優(yōu)化

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

3.2.1 SGA與PGA概述

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

3.2.2 內(nèi)存現(xiàn)狀分析

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

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

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

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

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

3.3 常駐內(nèi)存

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

show parameter keep;alter system set db_keep_cache_size=128M

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

1）查詢2014年至2016年某井油壓、套壓、輸出壓力、井口溫度、計(jì)量溫度等數(shù)據(jù)。系統(tǒng)響應(yī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存放位置，結(jié)果表明其存放在默認(rèn)緩沖池中;

select segment_name，secment_type，buffer_pool from user_segments

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

3）將表itb_djscsj_temp從默認(rèn)緩沖池存入保持池（常駐內(nèi)存）;

alter table scyx.itb_djscsj_temp storage （buffer_pool keep）

4） 確認(rèn)表是否存入常駐內(nèi)存，從BUFFER_POOL的值為keep可以知道，表已經(jīng)設(shè)置到常駐內(nèi)存;

5）結(jié)果表明：表常駐內(nèi)存后，執(zhí)行上述測試SQL，系統(tǒng)響應(yīng)8327ms，性能提升39%。

3.3.2 測試將索引設(shè)置為常駐內(nèi)存對系統(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)響應(yī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）將該條索引存入保持池中（常駐內(nèi)存）

alter index i_itb_djscsj_temp storage（buffer_pool keep）

4）確認(rèn)索引是否成功存入到保持池中（常駐內(nèi)存），從BUFFER_POOL值為keep可以得知，表已經(jīng)設(shè)置到保持池（常駐內(nèi)存）;

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

5）結(jié)果表明：索引常駐內(nèi)存后，執(zhí)行上述測試SQL，系統(tǒng)響應(yīng)減少765ms，性能提升11%。

3.4 SQL優(yōu)化

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

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

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

3.5 鎖爭用

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

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

1）查詢數(shù)據(jù)庫被鎖對象信息;

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）移除鎖進(jìn)程;

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

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

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

4 結(jié)論

1）數(shù)據(jù)庫設(shè)計(jì)階段，應(yīng)針對不同業(yè)務(wù)場景使用合理的設(shè)計(jì)范式，使系統(tǒng)性能最佳、開銷最小;

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

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

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

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

5結(jié)束語

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

參考文獻(xiàn)

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

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

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

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

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