目录
一、什么是优化器
为 SQL 生成最佳的执行计划，比如什么时候是全表扫描 (FTSfulltable scan) ，什么时 候是索引范围搜索 (Index Range Scan) ，或是全索引扫描 (INDEX fastfullscan ，INDEX_FFS) ；
如果是表于表之间连接的时候，它会负责让表之间以一种什么样子的形式来关联，比如 hash_join 还是 nested loops 或者是 merge join 。这些因素直接决定了 SQL 的执行效 率。
1 、优化器的种类
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 后才出现的，很多的时侯过 期统计信息会令优化器做出一个错误的执行计划, 因些应及时更新这些信息。
二、优化器的模式
基于规则的优化器以 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 计算成本侧重于最佳吞吐量。
三.RBP 与CBO的比较
1.RBO 比较呆板，只要有索引，就是用索引，无论效率高低。
create table t as select 1 id,object_name from dba_objects;
update t set id = 99 where rownum=1;
commit;
select id,count(*) from t group by id;
ID COUNT(*)
---------- ----------
99
1
1
72407
create index t_ind on t(id);
set autotrace trace explain
select /*+ rule +*/ * from t where id = 99;
Note
-----
- rule based optimizer used (consider using cbo) select * from t where id=99;
select * from t where id=1;
2.验证动态采样
SQL> set linesize 700
SQL> set autotrace trace explain;
SQL> select /*+ dynamic_sampling(t 0) +*/ * from t where id = 1;
//现在 T 表还没有被分析 hints/*+ dynamic_sampling(t0)+*/ 是让 CBO 无法通过动态采样 获得表中实际数据的情况 此时，CBO 只能根据数据字典表 T 的非常有限的信息 (比如表 的 extents 数量，数据块的数量) 来猜测表中的数据
从结果中可以看到，CBO 猜出的 ID=1 的数据量只有 353 条，这个数值对于表的总和来说， 是一个比较小的值，所以 CBO 选择了索引而不是全表扫描。但是实际 ID 等于 1 的数据 有 8w 多，但是实际情况
SQL> select * from t where id = 1;
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
通过动态采样 (如果表没有做分析，Oracle 自动通过动态采样的方式来收集分析数据) ， CBO 估算出表中的实际数据量 8w 多条 ( cardinality) ，从执行计划中 看到，这个数据值还 是相差不大，CBO 断定 ID=1 的数据基本上等同于表中的数据，所以选择了全表扫描。
SQL> select * from t where id = 99;
通过对表的分析，CBO 就可以获得 T 表和索引的充足的信息。上面的例子显示，CBO 从分 析数据中得到了 id=99 的数据记录是 1 ，所以选择了索引，这种情况下，索引的效率是 相 当高的。
3.分析表的意义
SQL> set autotrace off;
SQL> update t set id = 99;
SQL> commit;
SQL> set autotrace trace explain;
SQL> select * from t where id = 99;
然后我们把表中所有记录的 id 更新为 99 ，因为没有对表迚行分析，所以表中的分析数据 还是之前的信息，CBO 并不知道，可以看到，此时 rows 的值为 1 ，就是说，此时 CBO 仍 然认为表 T 中 id=99 的值只有 1 条，所以选择的仍然是索引。
SQL> exec dbms_stats.gather_table_stats(USER,'T',cascade=>true);
重新对表迚行分析后，CBO 获得了正确的 rows 值 所以是全表扫描，这种情况下全表扫描 是最佳的执行计划。
SQL> select * from t where id = 99;
4.多表查询 rows 的意义
在多表关联查询或者是 SQL 中有子查询时，每个关联表或者是子查询的 rows 的值对主查 询的影响非常大，甚至可以说，CBO 就是依赖于各个关联表或者子查询 rows 值来计算出 最后的执行计划。
对于多表查询，CBO 使用每个关联表返回的行数决定使用什么样的访问方式，来做表关联 (比如 nestd loops join 或者是 hash join ) ；对于子查询，它的 rows 将决定 子查询是使用索 引还是使用全表扫描的方式访问数据。
下面通过完整的子句查询的例子。来关注在子查询中，rows 是如何影响主查询的执行计划
。
create table t1 (id int,name varchar2 (1000));
create table t2 (id int,name varchar2 (1000));
create index ind_t1 on t1 (id);
create index ind_t2 on t2 (id);
create index ind_t2_name on t2 (name);
insert into t1 select object_id,object_name from dba_objects;
commit;
exec
dbms_stats .gather_table_stats (user, 'T1 ',cascade=>true,method
_opt=> 'for all indexed columns ');
select * from t1 where id in (select /*+ dynamic_sampling (t2 0)
cardinality (t2 30000) +*/ id from t2 where name= 'AA ');
其中：
cardinality(t2 30000)的作用是告诉 CBO 从 t2 表将获得 30000 条数据。 dynamic_sampling(t2 0)的作用是禁止动态采样。
通过这种方式，模拟子查询中返回的结果数，同时为了让 CBO 完全依赖这个信息生成 执行计划，禁止子查询使用动态采样(dynamic_sampling 设置为 0)。
可以看到，当 CBO 得到来自于子查询中返回的结果集 ( row source ) 的记录数为 30000 条时候，采用了 hash join 的执行计划，hash join 通常适用于两张关联的表都比较大的时候。 下面我们将子查询的结果集变得很小
select * from t1 where id in (select /*+ dynamic_sampling (t2 0)
cardinality (t2 1) +*/ id from t2 where name= 'AA ');
将子查询的返回值设置为 1 ，即：cardinality(t2 1)
此时 CBO 选择了两表通过 nested loops join 迚行关联的执行计划，因为子查询只 有 1 条
记录，这时候 CBO 会选择最合适这种情况的 nested loop join 关联方式。 从这里我们得到这样的一个结论：
子查询的 rows 值直接影响了主查询的执行计划，如果 CBO 对子查询的 ROWS 判断 有误，那么主查询的执行计划很有可能是错误的。
在看多表查询时候，一定要注意每个操作 rows 值，如果这个数值明显不对，那么很可能 操作的表的分析数据出了问题或者表没有分析。比如上面的例子，你确切的知道 T2 表的数 据很小，而在执行计划显示却是 10000 条，这显然不对，这个时候就要去检查问题的 所在， 看看 T2 表是不是曾经有很多数据，删除之后没有做重做分析等。
220
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
