基于Oralce数据库SQL查询优化研究

张学义，王观玉，黄 隽

ZHANG Xue-yi，WANG Guan-yu，HUANG Jun

（黔南民族师范学院 计算机科学系，都匀 558000）

0 引言

Oracle数据库是当前应用最广泛的大型数据库，其查询性能直接关系到系统的运行效率，对其查询优化方法的研究更具有现实意义。随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性和降低系统的响应时间。传统的Oracle SQL查询优化方法[1]是使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器略去索引而使用全表扫描，导致查询效率低下。

针对上述情况，提出了几种SQL查询语句优化新策略：检测分析影响系统响应速度的SQL语句、共享SQL语句、使用表的别名、数据缓冲区优化、共享池的优化、数据缓冲池的优化，使得执行SQL语句时优化器根据优化原则来合理使用索引，并尽可能减少磁盘I/O访问获取所需要的数据，提高查询性能。在数据库优化前后比较其评价指标：响应时间和吞吐量之间的权衡、数据库的命中率以及内存的使用效率，并以此来衡量优化的效果和指导优化的方向。

1 Oracle优化原理

Oracle数据库优化的主要目标就是减少磁盘I/O、减少CPU利用率和资源竞争，降低查询响应时间或提高系统吞吐量。影响SQL性能的因素很多，如初始化参数设置不合理、导入了不准确的系统及模式统计数据从而影响优化程序（CBO）的正确判断、未建立恰当的索引引起全表扫描、多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录、未充分利用数据库提供的查询并行化处理。

磁盘I/O[2]是影响Oracle数据库性能的瓶颈，主要原因有磁盘竞争、I/O读取次数和数据块空间的分配管理不当等。提高I/O设备的并发访问率，可以有效提高SQL语句的执行效率。当竞争增强的时候，系统响应时间将增长。

用户在编写新的SQL语句，或者对应用程序中存在疑问的语句进行优化时，其基本步骤为查找最消耗资源的语句，对这些语句进行优化，使其占用更少的资源。利用SQL TRACE、SQL Analyze等工具，可以查出存在问题的SQL语句。

SQL处理体系结构由以下几个主要组件组成：解析程序、字典、优化程序、SQL执行，如下图1所示：

解析程序、优化程序共同组成了SQL编译器。编译器将SQL语句编译成共享游标，并与查询计划相关联。解析程序执行语法分析和语义分析。优化程序是SQL处理引擎的核心。Oracle数据库支持两种优化方法：基于规则的优化（RBO）和基于开销的优化（CBO），本论文正是基于CBO方法设置优化程序的方法和目标，以及收集CBO的统计信息，对SQL语句进行查询优化，获得最佳吞吐量。

图1 SQL处理系统结构

2 Oracle数据库内存优化方法

要充分发挥Oracle数据库的优势，必须对数据库的各项初始化参数进行合理配置[3]。从Oracle数据库内存优化管理的角度出发，针对影响其性能的因素及其对应的参数，分别从数据缓冲区优化、共享池优化、重做日志缓冲区优化几个方面完成内存优化配置。

SGA（系统全局区）是数据库的工作区，它和Oracle进程结合组成一个Oracle数据库实例，管理数据库数据，应答用户请求。SGA[4]有三个组成部分，即数据库缓存区，共享池区以及日志缓存区，这些内存区域由初始化文件initSID.ora中相应的参数来配置，它们的性能效率也将受initSID.ora中各参数设置的影响。以DBA的身份连接到数据库，通过执行下列语句来获取SGA设置信息：SQL＞select * from v$sga，并做相应的调整。

1）共享池的优化

共享池（shared pool）包括库高速缓存、数据字典高速缓存，衡量这两个缓存区性能的指标主要是它们的命中率。共享池用LRU算法进行管理，保证频繁使用的代码和数据字典能够存于共享池中。数据字典命中率查询：SQL＞select（1 -（sum（getmisses）/sum（gets）））* 100“Hit Ratio” from v$rowcache；查询结果Hit Ratio为98.25588424。

若共享池的库高速缓存和数据字典命中率低于95%，则可增大initSID.ora中shared_ pool_size的值。

2）日志缓冲区的优化

日志缓冲区存储数据库的修改信息，大小由log_buffer确定，它必须是db_block_ size的整数倍。日志缓冲区的存在可以加快数据库的操作速度，因为内存到内存的操作比内存到硬盘的速度快很多。SQL＞select n.name,gets,misses,immediate_gets，immediate_ misses from v$latch l,v$latchname n where n.name in（'redo allocation','redo copy'）and n.latch#=l.latch#；此查询中redo allocation和redo copy的失败率为0，如果计算结果大于1%，则需增大日志缓冲区的大小。

3 SQL查询语句优化新策略

SQL语句尤其是复杂SQL语句的性能优化对于数据库的性能是至关重要的。在集中式数据库中，SQL查询的执行总代价＝I/O代价＋CPU代价＋内存代价。调整影响其执行效率的三大因素来减少系统总代价：一是减少查询所产生的I/O总次数；二是减少CPU的计算频度，减少SQL语句中需要计算的量和参数；三是减少对系统内存的使用和占用时间。SQL语句优化的一般步骤如下图2所示：

首先我们要检测出不合理的SQL语句，首先要生成执行计划，最简单的办法有两种：一是SQL＞set autotrace on自动记录执行计划；二是explain plan for ‘SQL语句’，然后通过select * from table（dbms_xplan.display（））来查看执行计划。第一种方法查看执行时间较长的SQL语句时，需要等待该语句执行成功后才返回执行计划。

在第一次解析之后，Oracle将SQL语句存放位在SGA共享池中，为所有的数据库用户共享，大大地提高了SQL的执行性能并节省了内存。当用户提交SQL语句时，服务器进程在共享池中查找有无该条语句，如果有就跳过语法分析等过程，节省了SQL语句的分析和编译的开销。只有在共享池中不存在等价SQL语句的情况下，才对该语句作语法分析，并为该语句分配新的共享SQL区。

图2 SQL优化的一般步骤

SQL优化[6]的实质就是在结果正确的前提下，充分利用索引，减少表扫描的I/O次数，选择最有效的执行计划来执行SQL语句的过程。下面的查询优化实例以EMP和DEPT表为查询表，其中emp表有1204行记录，dept有604行记录。

1）select子句中避免使用*

Oracle在解析的过程中，通过查询数据字典将*依次转换成所有的列名，这将消耗更多的时间，降低了查询速度。优化例1如下表1所示：

表1 select子句中＊ 优化

2）where子句中约束条件的顺序

Oracle采用自下而上的顺序解析where子句，因此表之间的连接必须写在其他约束条件之前，将过滤掉最多记录的条件写在where子句的末尾，提高查询效率。优化例2如下表2所示：

表2 where子句约束条件优化

3）用where子句替换having子句

where检查每条记录是否符合条件，通过其过滤条件减少系统开销。having子句检查group by后的各组是否满足条件，而Where子句在汇总之前就减少参加汇总的数据量，从而提高查询速度。如果having子句应用了汇总函数，则不能用Where代替。优化例3如下表3所示：

表3 group by和having优化

4）子查询“展平”技术

子查询“展平”指将子查询转变为连接、半连接，从而达到优化查询的目的。SQL首先计算位于外层查询的from子句中关系的笛卡尔积，然后对该笛卡尔积的每个元组用where子句中的谓词进行过滤。因为子查询要对应位于外层查询的每一个元组进行单独的计算，从而导致大量的磁盘I/O操作，所以在实际应用中，用连接查询代替子查询，提高查询效率。

找出所有工资超过2000的雇员的那些部门编号和名称。优化例5如下表4所示：

表4 子查询展平优化

5）用union替换or（适用于索引列）

在where子句中or连接多个索引列，用union替换where子句中的or，可以显著提高查询效率。对索引列使用or将造成全表扫描，如果用or连接的列没有被索引，查询效率不会提高。union在进行表连接后，对所产生的结果集进行排序运算，筛选掉重复的记录再返回结果。

4 结束语

本文从影响SQL性能的最主要的几个方面入手，提出了从磁盘I/O、系统内存参数的调整和SQL查询语句的优化新策略，算法可行，效率较高，可以在实际中推广应用。实验结果表明，SQL语句的优化使运算速度加快，有效减少执行时间，提高响应速度，优化效果理想，因此，本文提出的Oracle SQL查询优化方法是一种有效的数据库性能调优方法。

数据库的性能调整是一个系统工程，需要在大量的实践工作中不断地积累经验，结合上述各种优化技术，更好地进行数据库调优，实现数据库检索性能的提高。

[1]Donald K.Burleson.刘砚，黄春，译.Oracle高性能SQL调整[M].北京：机械工业出版社，2002.

[2]Y.Ionnidis and S.Christodoulakis.Optimal histograms for limiting worst-case error propagation in the size of join results[J].ACM TODS，1993，18（4）.

[3]赵慧勤,李秀兰.Oracle数据库应用系统的优化策略[J].计算机工程与应用，2003，27（3）.

[4]刘博.Oracle数据库性能优化与调整[D].大连：大连理工大学，2007.

[5]谷小秋,李德昌.索引调整优化Oracle9i工作性能的研究[J].计算机工程与应用，2005，41（26）.

[6]刘光霆.ORACLE中SQL查询优化研究[J].计算机与信息技术，2008，32（5）.

[7]吴超，沈为群，潘舜良，宋子善.某直升机工程飞行模拟器控制中心的研究与实现[J].计算机仿真，2006，23（9）：294-297.