Oracle数据库设计中SQL语句优化的研究.docVIP

Oracle数据库中SQL语句优化研究 中国电信股份有限公司石嘴山分公司 任凤 摘要： 简要介绍数据库中SQL 语句优化技术，并结合具体的事例，探讨了基于Oracle 数据库的SQL 语句优化。 关键词： 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 对语句的匹配是相当严格的,要达成共享,