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年生，工程師;长期从事油气田信息技术研究、信息规划设计与信息化项目建设等工作。