如何使用Oracle中autotrace来实现性能优化

大家在写SQL语句时，有没有考虑过或者审视过自己写的查询语句是否能足够好？至于“好”是指查询的时间比较小，也即在用户的可忍受的范围内。而且在大数据量的时候也能体现出来。

也许说到这里面，许多人是不是会问：直接交到Oracle或者其他数据库去处理不就得了，还要考虑什么哪？在此文中是以Oracle为例。

不知道有没有人听说过Oracle中的成本优化器。它是做什么的呢？简单来说：它是优化用户写的SQL语句的。（也许我理解得不太正确，如有错误请指出）

比如：SQL：select * from dual这么简单的SQL，也是一样经过Oracle的成本优化器的，但是在之前的Oracle版本是没有的，以前的版本是基于规则的优化器的。说到这里，我想有必要对这两种优化器作一个简要的解释：

基于规则的优化器（Rule-Based Optimization）：简称RBO，优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。在RBO中Oracle根据可用的访问路径和访问路径的等级来选择执行计划，等级越高的访问路径通常运行SQL越慢，如果一个语句有多个路径可走，Oracle总是选择等级较低的访问路径。但是在10g开始就被Oracle废除了，而在8i或9i是默认使用的

如下引用其他作者的原文：

RBO访问路径
	1级：用Rowid定位单行
	当WHERE子句中直接嵌入Rowid时，RBO走此路径。Oracle不推荐直接引用Rowid，Rowid可能会由于版本的改变而变化，行迁移、行链接、EXP/IMP也会使Rowid发生变化。
	2级：用Cluster Join定位单行
	两个表做等值连接，一方的连接字段是Cluster Key，且WHERE中存在可以保证该语句仅返回一行记录的条件时，RBO走此路径。
	3级：用带用唯一约束或做主键的Hash Cluster Key定位单行
	4级：用唯一约束的字段或做主键的字段来定位单行
	5级：Cluster Join
	6级：使用Hash Cluster Key
	7级：使用索引Cluster Key
	8级：使用复合索引
	9级：使用单字段索引
	10级：用索引进行有界限范围的查找
	如，column >[=] expr AND column <[=] expr或column BETWEEN expr AND expr
或column LIKE ‘c%’
	11级：用索引字段进行无界限的查找
	如，WHERE column >[=] expr 或 WHERE column <[=] expr
	12级：排序合并连接
	13级：对索引字段使用MAX或MIN函数
	14级：ORDER BY索引字段
	15级：全表扫描
	如果可以使用索引RBO会尽可能的去用索引而不是全表扫描，但是在下列一些情况RBO只能使用全表扫描：
	如果column1和column2是同一个表的字段，含有条件column1 < column2或column1 > column2或column1 <= column2或column1 >= column2，RBO会用全表扫描。
	如果使用column IS NULL或column IS NOT NULL或column NOT IN或column != expr或column LIKE ‘%ABC’时，不论column有无索引，RBO都使用全表扫描。
	如果expr = expr2，expr表达式作用了一个字段上，无论该字段有无索引，RBO都会全表扫描。
	NOT EXISTS子查询以及在视图中使用ROWNUM也会造成RBO进行全表扫描。
	以上就是RBO的全部可用访问路径。RBO优化器死板的根据规则来选择执行计划显然不够灵活，在RBO中也无法使用物化视图等Oracle提供的新特性，在Oracle8i时CBO已经基本成熟，因此Oracle强烈建议改用CBO优化器。下文将全面介绍CBO优化器。

基于成本的优化器（Cost-Based Optimization）：简称CBO ，它是是基于成本的优化器，它根据可用的访问路径、对象的统计信息、嵌入的Hints来选择一个成本最低的执行计划。

如下又引用别的作者的原文：

CBO主要包含以下组件：
	查询转换器（Query Transformer）
	评估器（Estimator）
	计划生成器（Plan Generator）

如下图所示：

	分析过的查询



	转换过的查询

	统计信息
	
	查询语句+评估值


	
	执行计划
	
	图1

查询转换器
	查询语句的形式会影响所产生的执行计划，查询转换器的作用就是改变查询语句的形式以产生较好的执行计划。
	从Oracle 8i开始就有四种转换技术：视图合并（View Merging）、谓词推进（Predicate Pushing）、非嵌套子查询（Subquery Unnesting）和物化视图的查询重写（Query Rewrite with Materialized Views）。
	视图合并：如果SQL语句中含有视图，经分析后会把视图放在独立的“视图查询块”中，每个视图会产生一个视图子计划，当为整个语句产生执行计划时，视图子计划会被直接拿来使用而不会照顾到语句的整体性，这样就很容易导致不良执行计划的生成。视图合并就是为了去掉“视图查询块”，将视图合并到一个整体的查询块中，这样就不会有视图子计划产生，执行计划的优良性得到提升。
	谓词推进：不是所有的视图都能够被合并，对于那些不能被合并的视图Oracle会将相应的谓词推进到视图查询块中，这些谓词通常是可索引的或者是过滤性较强的。
	非嵌套子查询：子查询和视图一样也是被放于独立查询块中的，查询转换器会将绝大多数子查询转换为连接从而合并为同一查询块，少量不能被转换为连接的子查询，会将它们的子计划安照一个高效的方式排列。
	物化视图的查询重写：当query_rewrite_enabled=true时，查询转换器寻找与该查询语句相关联的物化视图，并用物化视图改写该查询语句。

关于“窥视”（Peeking）:
	在Oracle9i中为查询转换器增加了一个功能，就是当用户使用绑定变量时，查询转换器可以“偷窥”绑定变量的实际值。
	我们知道使用绑定变量虽然可以有效的减少“硬分析”，但它带来的负面影响是优化器无法根据实际的数据分布来优化SQL，很有可能本可以走索引的SQL却做了全表扫描。“窥视”正是为了解决这个问题，但是它并没有彻底的解决，Oracle只允许第一次调用时进行“窥视”，接下来的调用即使绑定变量的值发生了变化，也仍然是使用第一次生成的执行计划，这就造成了一个错误的执行计划会被多次使用，10g中的“窥视”也是如此。

评估器
	评估器通过计算三个值来评估计划的总体成本：选择性（Selectivity）、基数（Cardinality）、成本（Cost）。
	选择性：是一个大于0小于1的数，0表示没有记录被选定，1表示所有记录都被选定。统计信息和直方图关系到选择性值的准确性。如：name=’Davis’，如果不存在统计信息评估器将根据所用的谓词来指定一个缺省的选择性值，此时评估器会始终认为等式谓词的选择性比不等式谓词小；如果存在统计信息而不存在直方图，此时选择性值为1/count(distinct name)；如果存在统计信息也存在直方图，选择性值则为count（name）where name=’Davis’ / count（name）where name is not null。
	基数：通常表中的行数称为“基础基数”（Base cardinality）；当用WHERE中的条件过滤后剩下的行数称为“有效基数”（Effective cardinality）；连接操作之后产生的结果集行数称为“连接基数”（Join cardinality）；一个字段DISTINCT之后的行数称为“DISTINCT基数”；“GROUP基数”（Group cardinality）比较特殊，它与基础基数和DISTINCT基数有关，例如：group by colx则GROUP基数就等于基础基数，但是group by colx,coly的GROUP基数则大于max ( distinct cardinality of colx , distinct cardinality of coly )且小于min ( (distinct cardinality of colx * distinct cardinality of coly) , base cardinality)。
	成本：就是度量资源消耗的单位。可以理解为执行表扫描、索引扫描、连接、排序等操作所消耗I/O、CPU、内存的数量。

计划生成器
	计划生成器的作用就是生成大量的执行计划，然后选择其中总体成本最低的一个。
	由于不同的访问路径、连接方式和连接顺序可以任意组合，虽然以不同的方式访问和处理数据，但是可以产生同样的结果，因此一个SQL可能存在大量不同的执行计划。但实际上计划生成器很少会试验所有的可能存在的执行计划，如果它发现当前执行计划的成本已经很低了，它将停止试验，相反当前计划的成本如果很高，它将继续试验其他执行计划，因此如果能使计划生成器一开始就找到成本很低的执行计划，则会大量减少所消耗的时间，这也正是我们为什么用HINTS来优化SQL的原因之一。

说了这么多，相信大家 都对这两种成本器有了大概的了解了。

因此在使用SQL时，如果使用SQL不当，Oracle会选择不同的优化器来生成执行计划 ，从而导致查询SQL的效率不尽如意，然而如上这些其实都可以在写SQL时可以注意的，使用Oracle中的小小工具就可以看出自己写的SQL是否已经按照最优的路径来生成执行计划了，是否足够。

下面将介绍一下Oracle的autotrace，使用它来分析SQL，然后再来优化SQL。

SQL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

以上为其用法。OFF为关闭，ON为打开，TRACE[ONLY]为只打印执行计划和统计信息，不打印数据。

EXP[LAIN]为只打印执行计划，不打印数据、[STAT[ISTICS]]为只打印统计信息，不打印数据

就使用上面比较简单的SQL:select * from dual测试一下：

看下图：

分析：需要从执行计划中看：

可以看到第1步操作中存在着全表遍历，还看COST列，看出使用CBO成本优化器。

而且还看统计信息中最重要的两项：db block gets和consistent gets，一个是从硬盘获取，一个从内存获取。

因为这个SQL太简单了，而且表只是为Oracle中一个测试表，也没什么可优化。

再看SQL，它上面的SQL修改成：select 'a' from dual.再测试一下：

如下图：

有没有发现，全表搜索没有了，再看统计信息中最重要两项都成0了。

其中就是因为这个SQL太简单了，大家可能觉得太忽悠人了吧。哈哈，其实呀，在这里只是简单的举了例子而已。

在现实开发中使用到SQL又臭又长，要优化的多着呢。但我这使用简单的SQL来介绍优化要注意的地方。

若想Oracle选择最优的执行计划来执行你的SQL，那么大家可以按照以上的方法试试。如果发现有全表搜索或者比较吃内存或者CPU的步骤，那么就可以从中找出相应的病症，下对应的药来解决。

或者我再从一个SQL来解释一下吧：

select c.*
  from lcms_course_chapterinfo c
 where c.isdeleted = 'N'
 start with c.chapter_id in
            (select c.chapter_id
               from lcms_course_chapterinfo c
              where c.is_must = '1'
                and c.isdeleted = 'N'
              start with c.chapter_id in
                         (select b.chapter_id
                            from lcms_termcourse_task      a,
                                 lcms_termcourse_task_chpt b,
                                 lcms_course_chapterinfo   c
                           where a.task_id = b.task_id
                             and b.chapter_id = c.chapter_id
                             and c.layer_type = '1'
                             and a.isdeleted = 'N'
                             and b.isdeleted = 'N'
                             and c.isdeleted = 'N')
             connect by prior c.chapter_id = c.parent_id)
connect by prior c.chapter_id = c.parent_id

此SQL是查询所有必修课的SQL。

而由于数据量比较大，因而我就不打印数据了，直接看执行计划和统计信息。

看下图：

不知大家有没有注意到执行计划 中多了些步骤呢？

大家注意看。前面的序号对应上面表格中的序号。

哈哈来到这里面大家看到了，其实都明白了吧，一步一步看，找出最耗时间的路径，再优化自己的SQL。

下班咯。下次再写。
再来：

优化方法：1、首先查询出步骤中最耗时间或者最耗内存的语句

2、判断其耗时间或内存的原因。

3、适当增加索引

。。。