如何使用CBO,CBO与RULE的区别？

http://yun.baidu.com/disk/beinvited?uk=1292220315 百度网盘不错

ORACLE 优化器提供两种优化方式

 

当一条sql语句执行的时候那么会牵涉到走哪条执行计划(在sql被解析后经历逻辑优化和物理优化的时候会生成多个执行计划)，这时就用到了优化器，优化器如何选择执行计划会基于两种方式

 一种是基于规则的也就是RBO(RULE BASED optimization)

   这种规则一般是指的具体的一些限定条件，比如带有where empno =xx 这类的语句,在empno上有索引的话那么8i以前会走RBO，8i以后oracle一般会用基于CBO的优化方式

 

 一种是基于成本的优化方式也就是CBO(COST BASED OPTIMIZition)

  这个成本一般指的CPU和内存的消耗。oracle会根据表和索引的统计信息来计算运行sql语句采用不同的执行计划所要耗费的成本，比如表的统计信息一般就是指表大小，表含有多少行，每行长度等信息，oracle会选择一个消耗成本最低的执行计划来运行sql，所以一定要注意定期更新统计信息，否则有可能因为错误的统计信息造成优化器错误的选择，造成性能地下。

    

不一定说走索引就都好，因为如果表很小的话，那么如果要检索一条记录，可能I/O花费也就是1，如果走索引那么就是2因为要多访问一次索引块。

 

由于环境的原因造成优化器对同一sql语句选择不同的执行计划也是有可能的，这种环境因素可能是由于不同的优化器模式或是字符长度变化造成的

关于优化器模式有四种：

  1 rule

       这种还是基于规则的

  2 choose

       这种如果存在对表或索引的统计信息，那么会基于CBO选择执行计划，如果没有统计信息那么走RBO

  3 first_row

       这个选项是如果存在对表或索引的统计信息，那么选择一个(可以最快的返回第一批查询结果的)的执行计划.

first-N rows optimization instructs the query optimizer to choose a query execution plan that minimizes the response time to produce the first N rows of query results.

  4 all_row

       这个选项也是根据表或索引的统计信息选择一个可以最快返回所有行的执行计划，如果没有统计信息则走基于RBO选择的执行计划

 

关于如何设定选择哪种优化器模式：

 A instant 级别

   修改初始化参数,我们可以通过在init.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。

B session 级别

   当前会话输入 alter session set optimizer_mode = xxx scope = both;

C 语句级别 

    通过HINT 提示来选择

      select /*+ rule*/e.ename,d.deptno from emp e ,dept d

       where e.deptno = d.deptno

     强调一点：你设置的hint所包含的执行计划一定要有否则这个hint就相当于comment

 

 

为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢？如何解决呢？

 

 走不走索引取决于很多因素，以下是常见的几种因素

1 优化器模式选择是 all_row 因为返回行会很多如果选择走索引反而会消耗更多的成本 所以这种情况一般优化器会选择full table scan

2 可能是因为旧的统计信息，造成优化器选择执行计划参考了错误的数据而造成的

3 表比较小，一共没几行，如果走索引会很不划算。

 

走索引或走全表扫描是要看具体情况的，并没有好不好之说。

 

解决方案：1 修改初始化参数的optimizer_mode = rule or choose选项

               2  删除旧有的统计信息，重新收集表和索引统计信息

          删除统计信息：   analyze table table_name delete statistics;

          更新统计信息：   ANALYZE TABLE table_name COMPUTE STATISTICS; 
                                  ANALYZE INDEX index_name ESTIMATE STATISTICS;

 

select num_rows from user_tables --查看是否有表统计信息
where table_name = 'EMP'
and num_rows is not null;

 

select num_rows from user_indexes --查看是否有索引统计信息
where table_name = 'EMP'
and num_rows is not null;