贺鹏程

（海军装备部驻重庆地区军事代表局 重庆　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—），男，四川成都人，硕士，工程师。研究方向：军事装备学。