[转载] Oracle优化器参考

本文讲述了Oracle优化器的概念、工作原理和使用方法，兼顾了Oracle8i、9i以及最新的10g三个版本。理解本文将有助于您更好的更有效的进行SQL优化工作。

RBO优化器

      RBO是一种基于规则的优化器，随着CBO优化器的逐步发展和完善，在最新的10g版本中Oracle已经彻底废除了RBO。正在使用Oracle8i或9i的人们或多或少的都会碰到RBO，因此在详细介绍CBO之前，我们有必要简单回顾一下古老的RBO优化器。
      在RBO中Oracle根据可用的访问路径和访问路径的等级来选择执行计划，等级越高的访问路径通常运行SQL越慢，如果一个语句有多个路径可走，Oracle总是选择等级较低的访问路径。

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 或column LIKE ‘c%’
 11级：用索引字段进行无界限的查找
     如，WHERE column >[=] expr 或 WHERE column  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优化器。

CBO优化器结构

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

 CBO主要包含以下组件：

l 查询转换器（Query Transformer）
l 评估器（Estimator）
l 计划生成器（Plan Generator）

查询转换器

      查询语句的形式会影响所产生的执行计划，查询转换器的作用就是改变查询语句的形式以产生较好的执行计划。
     从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的原因之一。

优化器模式及优化目标

      除了上述的CBO优化器外，Oracle还有一种基于规则的RBO优化器，在8i以后Oracle就不再发展RBO了，有很多新特性在RBO中也不被支持，在最新的10g中RBO已被彻底废除。在10g前RBO与CBO共存，用户可以通过设置初始化参数OPTIMIZER_MODE来决定到底使用哪个优化器，也可以用ALTER SESSION来改变当前SESSION中OPTIMIZER_MODE的值。除此之外在SQL中嵌入HINTS可以指定具体某个SQL使用哪个优化器。
      CBO虽然是基于成本的优化器，但仍然允许以“时间”或者说“响应速度”为优化目标，通过设置OPTIMIZER_MODE或者对具体语句嵌入HINTS都可以指定优化目标。

 OPTIMIZER_MODE选项如下：

l ALL_ROWS
l FIRST_ROWS_n
l FIRST_ROWS
l CHOOSE
l RULE

CHOOSE

      仅在9i及之前版本中被支持，10g已经废除。8i及9i中为默认值。
      这个值表示SQL语句既可以使用RBO优化器也可以使用CBO优化器，而决定该SQL到底使用哪个优化器的唯一因素是，所访问的对象是否存在统计信息。如果所访问的全部对象都存在统计信息，则使用CBO优化器优化SQL；如果只有部分对象存在统计信息，也仍然使用CBO优化器优化SQL，优化器会为不存在统计信息对象依据一些内在信息（如分配给该对象的数据块）来生成统计信息，只是这样生成的统计信息可能不准确，而导致产生不理想的执行计划；如果全部对象都无统计信息，则使用RBO来优化该SQL语句。

RULE

      仅在9i及之前版本中被支持，10g已经废除。
      不论是否存在统计信息，都将使用RBO优化器来优化SQL。

ALL_ROWS

      在10g中为默认值。
      不论是否存在统计信息，都使用CBO优化器，且把CBO的优化目标设定为“最小的成本”。

FIRST_ROWS

      CBO尽可能快速的返回结果集的前面少数行记录。
不论是否存在统计信息，都使用CBO优化器，FIRST_ROWS导致CBO使用“试探法”来产生执行计划，这种方式其成本可能会稍大一些。

FIRST_ROWS_n

       不论是否存在统计信息，都使用CBO优化器，并以最快的速度返回前n行记录，n可以是1,10,100,1000。

影响优化器模式及目标的HINTS：

 l RULE：意义同OPTIMIZER_MODE=RULE区别在于HINTS作用在语句级，10g中该HINTS已被废弃。
l CHOOSE：意义同OPTIMIZER_MODE=CHOOSE，10g中已被废弃。
l FIRST_ROWS：意义同OPTIMIZER_MODE=FIRST_ROWS，10g中已被废弃。
l ALL_ROWS：意义同OPTIMIZER_MODE=ALL_ROWS。
l FIRST_ROWS(n)：意义同OPTIMIZER_MODE=FIRST_ROWS_n。
l CPU_COSTING：启用CPU成本计算，也就是在总成本中考虑CPU的成本，缺省是启用的。该HINTS是10g中新增加的。
l NO_CPU_COSTING：关闭CPU成本计算，也就是在总成本中不考虑CPU的成本，只计算I/O的成本。该HINTS也是10g中新增加的。

访问路径

      访问路径就是从数据库里检索数据的方式。优化器首先检查WHERE子句和FROM子句的条件，确定有哪些访问路径是可用的。然后优化器使用这些访问路径或各访问路径的联合，产生一组可能存在的执行计划，再通过索引、字段、表的统计信息评估每个计划的成本，最后优化器选择成本最低的执行计划所对应的访问路径。
      如果SQL语句的FROM子句无SAMPLE或SAMPLE BLOCK，优化器在选择访问路径的时候会优先考虑语句中的HINTS。

 优化器可用的访问路径如下：

l 全表扫描（Full Table Scans）
l Rowid扫描（Rowid Scans）
l 索引扫描（Index Scans）
l 簇扫描（Cluster Scans）
l 散列扫描（Hash Scans）
l 表取样扫描（Sample Table Scans）

全表扫描

      全表扫描将读取HWM之下的所有数据块，所有行都要经WHERE子句过滤看是否满足条件。当Oracle执行全表扫描时会按顺序读取每个块且只读一次，如果能够一次读取多个块，可以有效的提高效率，初始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取多少个数据块。 通常我们认为应该避免全表扫描，但是在检索大量数据时全表扫描优于索引扫描，这正是因为全表扫描可以在一次I/O中读却多个块，从而减少了I/O的次数。在使用全表扫描的同时也可以使用并行来提高扫描的速度。

CBO优化器何时会选择全表扫描

1） 无合适的索引。
2） 检索表中绝大多数的数据。
3） 表非常小。比如，表中的块小于DB_FILE_MULTIBLOCK_READ_COUNT，只需一次I/O。如果这样的表被频繁使用应该alter table table_name storage(buffer_pool keep)。
4） 高并行度。如果在表级设置了较高的并行度，如alter table table_name parallel(degree 10)，通常会使CBO选择全表扫描。通常建议在语句级用HINTS来实现并行，如/*+full(table_name) parallel(table_name degree)*/。
5） 太旧的统计数据。如果表没有进行过分析或很久没有再次分析，CBO可能会错误的认为表含有及少的数据块。
6） 在语句中嵌入了全表扫描的HINTS。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-676966/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-676966/