Oracle数据库中SQL语句优化研究

1、引言

Oracle 数据库是当前应用最广泛的大型数据库管理系统, 它在各个领域的使用不断增长, 其查询性能直接关系到系统的运行效率, 对其查询优化方法的研究更具有现实意义。数据库SQL 查询优化的一个基本原则就是: 通过尽可能少的磁盘I/ O访问获取所需要的数据。要评价查询优化性能, 需要在数据库优化前后比较其评价指标: 响应时间和吞吐量之间的权衡、数据库的命中率以及内存的使用效率, 并以此来衡量优化的效果和指导优化的方向。优质的查询语句可以使应用系统的性能得到大大提高。拙劣的应用SQL 语句、大小不合适的SGA 内存结构、效率差的SQL 语句、执行计划过度的文件I/O及访问数据库资源的紊乱等都会影响数据库系统的性能和执行效率。本文将从数据库设计的角度及SQL语句优化技术的两方面进行详细地阐述如何提高数据库的性能和执行效率。

一、 数据库设计的优化

数据库设计是应用系统设计的基础, 其性能直接影响应用系统的性能。数据库性能包括查询响应时间和吞吐量两个方面。数据库设计优化的主要目标就是减少磁盘I/ O、减少CPU 利用率和资源竞争, 降低查询响应时间或提高系统吞吐量。

1.1 磁盘I/ O 优化

磁盘I/ O是影响Oracle 数据库性能的瓶颈,而影响磁盘I/ O 性能的主要原因有磁盘竞争、I/O。资源竞争增强时,响应时间增长读取次数和数据块空间的分配管理不当等。Oracle进程经常需要访问数据文件和重做日志文件,当这两种类型的文件位于同一磁盘中,就会造成磁盘竞争。将表空间所属的数据文件存储在多个不同的物理磁盘上,使系统I/ O 趋于平衡;把数据、日志、索引放到不同的I/O设备上,可以减少磁盘竞争和I/ O读取次数。提高I/O设备的并发访问率,可以有效提高SQL 语句的执行效率。当竞争增强的时候, 系统响应时间将增长。

1.2内存优化调整

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

1.3优化全表扫描操作

一次I/O 能读取多块数据块(由db_block_multiblock_read_count 参数设定),这极大地减少I/O总次数,提高了系统的吞吐量, 所以利用多块读取的方法可以高效地实现全表扫描。

二、SQL 语句优化

SQL语句尤其是复杂SQL语句的性能优化对于数据库的性能是至关重要的。实际数据库使用过程中80% 的性能问题是由于用户使用了不恰当的SQL 查询语句造成的,因此优化SQL 语句可以提升整个系统的运行效率。在集中式数据库中, SQL 查询的执行总代价= I/ O 代价+ CPU 代价+ 内存代价。调整影响其执行效率的三大因素来减少系统总代价:一是减少查询所产生的I/ O 总次数; 二是减少CPU 的计算频度, 减少SQL 语句中需要计算的量和参数; 三是减少对系统内存的使用和占用时间。

2.1检查不合理的SQL 语句

要检测出不合理的SQL 语句, 首先要生成执行计划,最简单的办法有两种: 一是SQL> set autotrace on 自动记录执行计划; 二是explainplan  for ‘SQL 语句’，然后通过select * from table(dbms_xplan.display ( ) ) 来查看执行计划。第一种方法查看执行时间较长的SQL 语句时, 需要等待该语句执行成功后才返回执行计划。如果只想得到执行计划可以采用explainplan 或者SQL> set autotrace traceonly, 它们不会真正的执行语句。通过执行计划来确定不合理的SQL 语句, 为SQL 优化做准备。

2.2共享SQL 语句

在第一次解析之后, Oracle 将SQL语句存放位在系统全局区域( SGA) 的共享池中,它可以为所有的数据库用户共享,大大地提高了SQL 的执行性能并节省了内存。当用户提交一个SQL 语句时,服务器进程在共享池中查找有无该条语句,如果有就跳过语法分析等过程,节省了SQL语句的分析和编译的开销只有在共享池中不存在等价SQL语句的情况下,才对该语句作语法分析,并为该语句分配新的共享SQL 区。对于编程者来说,要尽量提高SQL 语句的重用率,尽量使用Bind 变量,来减少语句的分析时间。但是Oracle 对语句的匹配是相当严格的,要达成共享, 它要遵循三条规则:

（1） SQL 语句必须完全相同( 包括空格、换行等)。下列SQL 语句都是不同的,

SELECT * from EMP; Select * From Emp; SELECT * FROM EMP;

（2）语句中的对象必须完全一致。

（3）语句中的绑定变量必须使用相同的名字。例如: 下面的两个SQL 语句是相同的,select ename from emp w here empno=:emp.no 和

select enam from emp where empno=:emp. no。

三、SQL语句优化技术

3.1基于索引的优化

索引是影响SQL 语句性能的一个重要因素, 在表上建立合适的索引, 可以避免全表扫描并减少I/ O 开销, 提高数据查询速度。但是创建索引会增加系统时间和空间的开销, 创建索引时必须要与实现应用系统的查询需求密切结合, 才能达到优化查询的目的。索引使用的一些原则：

（1） 当选择数据少于全表的20%, 并且表的大小超过ORACLE的5个数据块时,使用索引才会有效,否则用于索引的I/ O 加上用于数据的I/ O 就会大于做一次全表扫描的I/O,反而会降低查询效率;

（2） 由于表和索引的数据块通常是被同时读取的, 所以应该尽量将表和其相关联的索引放置在不同的磁盘上, 减少I/ O 冲突;

（3）索引的创建也是需要代价的, 对于DML 操作, 每个索引都要进行相应的删除、更新、插入操作。从而导致DML 操作的效率变低。因此定期的重构索引是有必要的, alter index < index name> rebuild < tablespacename> ;

（4）避免隐式转换和不必要的类型转换。例如: to_char( ) , to_number( ) , to_date( ) 等会导致该字段的索引失效, 从而对该表进行全表扫描, 对一个百万数量级的表进行全表扫描的会对系统带来严重的性能隐患;

3.2、基于Oracle 数据库对表访问的优化

3.2.1 全表扫描

全表扫描就是对表中每条记录进行顺序的访问。Oracle 采用一次读入多个数据块(database block)的方式对全表扫描进行优化。

3.2.2 选择最有效率的表名顺序

Oracle 的解析器按照从右到左的顺序对FROM 子句中的表名进行处理，FROM 子句中写在最后的表将最先被处理，当FROM 子句中包含多个表时，必须选择记录条数最少的表作为基础表。如果有3 个以上的表进行连接查询，那么，就需要选择交叉表作为基础表，交叉表就是被其他表所引用的那个表？

例如: student 表描述了location 表和category 表的交集?

SELECT * FROM location L, category C, student S WHERE S.sno BETWEEN 1000 AND 2000 AND S.catno=C.catno AND S.locn=L.locn

相对于下列SQL 语句更有效率:

SELECT * FROM student S, location L, category C WHERE S.catno=C.catno AND S.locn=L.locn AND S.sno BETWEEN 1000 AND 2000.

3.2.3 通过ROWID 访问表

为了提高访问表的效率，强烈采用基于ROWID 的访问方式情况。ROWID 包含了表中记录的物理位置信息。Oracle 数据和存放数据的物理位置之间的联系，采用索引进行实现。通常情况下，索引提供了快速访问ROWID 的方法，因此那些基于索引列的查询，能够得到性能上的提高。

3.3 SQL 语句编写需要满足的规则

根据上面两种Oracle 访问表的方式，进行SQL 语句的编写时，需要满足以下规则：

（1） 尽量使用索引。

（2） 选择联合查询的联合次序。在SQL 语句的编写中，应该注意首先需要选择要查询的主表，因为主表要扫描整个表数据，所以主表应该数据量最小。

（3） IN 或者NOT IN 语句在子查询中慎重使用，可以使用(NOT) EXISTS。

（4） 慎重使用视图的联合查询，尤其慎重使用比较复杂的视图之间的联合查询。一般来说，将对视图的查询分解为对数据表的直接查询能够取得更好的效果。

（5） 可以在参数文件中进行SHARED_POOL_RESERVED_SIZE 参数的设置，这个参数保留了一个连续的内存空间在SGA 共享池中，对于存放大的SQL 程序包非常有帮助。

（6） 对于某些经常使用的存储过程，可以通过Oracle 公司提供的DBMS_SHARED_POOL 程序固定在SQL 区中而不被换出内存，对于提高最终用户的响应时间是非常有利的。

3.4 实例

（1） 通过使用索引进行SQL 语句优化例如，比较下面两条SQL 语句：

语句A： SELECT deptno, deptname FROM dept WHERE  deptno  NOT IN (SELECT deptno FROM student);

语句B: SELECT deptno, deptname FROM dept WHERE  NOT EXISTS SELECT * FROM student WHERE dept.deptno=student.deptno);

通过执行发现,这两条查询语句实现的结果相同，但是在执行语句A 时，Oracle 会扫描整个student 表，而建立在student表上的deptno 索引没有使用到，当执行语句B 时，由于在子查询中使用了联合查询，Oracle 只扫描了student 表中的部分数据，并利用了deptno 列的索引，因此，语句B 的效率比语句A 的效率高。

四、 结束语

通过进行SQL 语句优化，提高了Oracle 数据库系统的性能,明显降低了其系统响应时间，提高了程序运行速度，使系统的顺利运行得到良好的保障。对于Oracle 数据库应用系统来说, SQL查询语句的性能优劣直接影响整个信息系统的效率, 效率高的查询语句和效率低的查询语句速度相差可以达到上百倍。只有认真分析Oracle 运行过程当中出现的各种性能问题, 才能保证Oracle 数据库高效可靠地运行。论文从影响SQ L 性能的最主要的几个方面入手, 分析了如何优化SQ L 查询的I/ O、内存参数的调整和SQL 语句的优化。数据库的性能调整是一个系统工程, 需要在大量的实践工作中不断地积累经验, 结合上述各种优化技术, 从而更好地进行数据库调优。