Oracle优化器详解

1、什么是优化器

为 SQL 生成最佳的执行计划，比如什么时候是全表扫描（FTS full table scan），什么时候是索引范围搜索（Index Range Scan），或是全索引扫描（INDEX fastfullscan，INDEX_FFS）；
如果是表于表之间连接的时候，它会负责让表之间以一种什么样子的形式来关联，比如hash_join 还是 nested loops 或者是 merge join。这些因素直接决定了 SQL 的执行效率。

2、优化器的种类

 Rule Based Optimizer（RBO）基于规则 （8i 之前使用的）
 Cost Based Optimizer（CBO）基于成本，或者讲统计信息

RBO方式： 自 ORACLE6 版以来被采用，有着一套严格的使用规则，只要你按照它去写 SQL 语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说对数据不“敏感”；CBO 计算各种可能“执行计划”的“代价”，即 cost，从中选用 cost 最低的方案，作为实际运行方案。各“执行计划”的 cost 的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布并不清楚，必须要分析表和相关的索引（使用ANALYZE 命令），才能搜集到 CBO 所需的数据。 优化器在分析 SQL 语句时,所遵循的是 Oracle 内部预定的
一些规则。比如我们常见的，当一个 where 子句中的一列有索引时去走索引。

CBO 方式：它是看语句的代价(Cost),这里的代价主要指 Cpu 和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做 analyze 后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。

3、优化器的模式

基于规则的优化器以 ORACLE 为中心，基于成本的优化器以数据为中心。数据库锁采用的优化器模式可以通过初始化参数 optimizer_mode 来设置。下面列出了可能使用的优化器模式：
1）first_rows_n(n=1、10、100、1000)
first_rows_n(n=1、10、100、1000)可以是 first_rows_1、first_rows_10、first_rows_100、first_rows_1000 中的任意一个值，他表示 oracle 在解析目标 sql 时，oracle 会使用 cbo 来解析目标 sql，且此时 cbo 在计算各条执行路径的成本时的侧重点在于以最快相应速度返回前 n 条数据。

2）first_rows
first_rows 是一个在 oracle 9i 中就过时的一个参数，他表示 oracle 在解析目标 sql 时会联合使用cbo 和 rbo。在大部分情况下，oracle 还是会选用 cbo 作为解析目标 sql，此时 oracle 的侧重点是以最快的相应速度返回前 n 行。在一些特俗情况下，oracle 会选用 rbo 来解析目标 sql 而不考虑成本。比如当 optimizer_mode 为 first_rows 时有一个内置的规则，就是 oracle 如果发现能用相关索引来避免排序，则 oracle 就会选择该索引所对应的路径而不考虑成本值。

3）all_rows
all_rows 是 oracle 10g 及以后 oracle 的版本中 optimizer_mode 的默认值，它表示 oracle 会使用 cbo 来解析目标 sql，此时 cbo 计算目标 sql 的各个执行路径的成本的侧重点是最佳吞吐量。当optimizer_mode 为 first_rows 时，cbo 计算成本侧重于最快响应时间；当 optimizer_mode 为all_rows 时，cbo 计算成本侧重于最佳吞吐量。

查看数据库当前优化器模式：

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

4、RBO 和 CBO 的比较

RBO 比较呆板，只要有索引，就是用索引，无论效率高低。

在用户层面默认用的是CBO,RBO已经不再用了。在数据库内部，有时会用RBO。

SQL>create table t as select 1 id,object_name from dba_objects;
SQL>update t set id = 99 where rownum=1;
SQL>commit;

SQL>select id,count(*) from t group by id;
           ID         COUNT(*) 
          ---------- ----------
           99        1
           1          72407
SQL>create index t_ind on t(id);

--打开执行计划
SQL>set autotrace trace explain

--使用hint语法指定使用RBO优化器
SQL>select /*+ rule +*/ * from t where id = 99;
Note
-----
- rule based optimizer used (consider using cbo)

SQL>select * from t where id=99;  --使用索引
SQL>select * from t where id=1;  --使用全表扫描