【接确保Oracle数据库sql语句高效执行的优化总结(一)】

2.4 10053和10046事件

2.4.1 10053事件

我们在查看一条SQL的执行计划的时候，只能看到CBO 最终告诉我们的执行计划结果，但是不知道CBO 是根据什么来做的。 如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。无法进行分析判断。

10053事件就提供了这样的功能。它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。

对于10053事件的trace文件，我们只能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件。

10053事件有两个级别：

? Level2：2级是1级的一个子集，它包含以下内容：

Column statistics Single Access Paths Join Costs Table Joins Considered Join Methods Considered (NL/MS/HA)

? Level1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：

Parameters used by the optimizer Index statistics

启用10053事件：

关闭10053事件：

说明：

（1）sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。应该通过v$sql_plan查看SQL的真实的执行计划。

（2）10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。

2.4.2 10046事件

10046 事件主要用来跟踪SQL语句，它并不是ORACLE 官方提供给用户的命令，在官方文档上也找不到事件的说明信息。 但是用的却比较多，因为10046事件获取SQL的信息比SQL_TRACE 更多。 更有利于我们对SQL的判断。

10046 事件按照收集信息内容，可以分成4个级别：

? Level 1： 等同于SQL_TRACE 的功能

? Level 4： 在Level 1的基础上增加收集绑定变量的信息

? Level 8： 在Level 1 的基础上增加等待事件的信息

? Level 12：等同于Level 4+Level 8，即同时收集绑定变量信息和等待事件信息。

启动10046事件

关闭10046事件

也可以使用oradebug 命令来执行10046：

具体的内容参考：

2.5 统计信息

优化器收集的统计信息包括如下内容：

1）Table statistics

Number of rows

Number of blocks

Average row length

2）Column statistics

Number of distinctvalues (NDV) in column

Number of nulls incolumn

Data distribution(histogram)

3）Index statistics

Number of leaf blocks

Levels

Clustering factor

4）System statistics

I/O performance and utilization

CPU performance andutilization

统计信息收集如下数据：

（1）表自身的分析： 包括表中的行数，数据块数，行长等信息。

（2）列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况。

（3）索引的分析： 包括索引叶块的数量，索引的深度，索引的聚合因子等。

这些统计信息存放在数据字典里，如：

（1）DBA_TABLES

（2）DBA_OBJECT_TABLES

（3）DBA_TAB_STATISTICS

（4）DBA_TAB_COL_STATISTICS

（5）DBA_TAB_HISTOGRAMS

（6）DBA_INDEXES

（7）DBA_IND_STATISTICS

（8）DBA_CLUSTERS

（9）DBA_TAB_PARTITIONS

（10）DBA_TAB_SUBPARTITIONS

（11）DBA_IND_PARTITIONS

（12）DBA_IND_SUBPARTITIONS

（13）DBA_PART_COL_STATISTICS

（14）DBA_PART_HISTOGRAMS

（15）DBA_SUBPART_COL_STATISTICS

（16）DBA_SUBPART_HISTOGRAMS

统计信息的准确程度，直接决定SQL的效率。 所以需要定期的收集相关对象的统计信息。Oracle 的Statistic 信息的收集分两种：自动收集和手工收集。

Oracle 的Automatic StatisticsGathering 是通过Scheduler 来实现收集和维护的。 Job 名称是GATHER_STATS_JOB, 该Job收集数据库所有对象的2种统计信息：

（1）Missing statistics（统计信息缺失）

（2）Stale statistics（统计信息陈旧）

该Job 是在数据库创建的时候自动创建，并由Scheduler来管理。Scheduler 在maintenance windows open时运行gather job。 默认情况下，job 会在每天晚上10到早上6点和周末全天开启。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

Scheduler Job的 stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False，那么GATHER_STATS_JOB 会中断，而没有收集完的对象将在下次启动时继续收集。

Gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。 该过程收集对象statistics的条件如下：

（1）对象的统计信息之前没有收集过。

（2）当对象有超过10%的rows 被修改，此时对象的统计信息也称为stale statistics。

 

第二篇：Oracle数据库设计中SQL语句优化研究

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 对语句的匹配是相当严格的,要达成共享, 它要遵循三条规则:

（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 语句的优化。数据库的性能调整是一个系统工程, 需要在大量的实践工作中不断地积累经验, 结合上述各种优化技术, 从而更好地进行数据库调优。

参考文献:

[ 1] 蒋年德, 李英. Oracle 环境下管理信息系统的优化设计 计算机应用研究, 2003,

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

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

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

[ 5] 谷小秋, 李德昌. 索引调整优化Oracle9i 工作性能研究

[6] . 计算机工程与应用, 2005,

作者简介：任凤 ，女 ，移动通信工程师，出生日期：19xx年8月20日，工程硕士，目前主要从事CDMA网络资源管理工作，研究方向数据挖掘。现工作在中国电信股份有限公司石嘴山分公司，邮编：753000，联系电话：189xxxxxxxx