优化器是什么
优化器（Optimizer）是 Oracle 数据库中内置的一个核心子系统，可以理解为 Oracle 数据库中的一个核心模块或者一个核心功能组件。优化器的目的是按照一定的判断原则来得到它认为的目标 SQL 在当前情形下最高效的执行路径（Access Path）。
Oracle 数据库里的优化器又分为 RBO 和 CBO 这两种类型。
RBO 全称是 Rule-Based Optimizer，翻译过来是基于规则的优化器。
CBO 全称是 Cost-Based Optimizer，基于成本的优化器。
在得到目标 SQL 的执行计划时，RBO 所用的判断原则是一组内置的规则，这些规则是硬编码在 Oracle 数据库代码中的，RBO 会根据这些规则从目标 SQL 诸多可能的执行路径中选择一条来作为其执行计划；CBO 所用的判断原则为成本，会从目标 SQL 诸多可能的执行路径中选择成本值最小的一条来作为其执行计划。
基于规则的优化器（RBO）
基于规则的优化器（RBO）是通过编码在 Oracle 数据库中的一系列固定的规则，来决定目标 SQL 的执行计划。Oracle 事先给各种类型的执行路径定一个等级，从 1 到 15，等级 1 对应执行路径的执行效率最高，等级 15 对应执行路径的执行效率最低。对于等级相同的执行计划，oracle 根据目标对象在数据字典中缓存的顺序判断选择哪一种执行计划。在决定目标 SQL 的执行计划时，RBO 会从该 SQL 的诸多执行路径中选择一条等级最低的执行路径来作为其执行计划。
RBO 中等级 1 对应的执行路径是“single row by rowid（通过 rowID 来访问单行数据）”，等级 15 所对应的执行路径时“full table scan（全表扫描）”。
RBO 在 Oracle10g 已不再支持，但是代码并未删除，这意味着在 11g 中，可以通过修改优化器模式或使用 RULE Hint 来继续使用 RBO。
启用 RBO
在当前会话中将优化器模式修改为 rule。
SQL > alter session set optimizer_mode = 'RULE';
显式指定：可通过 /*+ RULE */ Hint 强制使用 RBO
RBO 的缺陷
RBO 的执行计划很难调整
目标 SQL 的写法及各个对象在该 SQL 文本中出现的先后顺序都会影响执行计划
Oracle 数据中的很多新特性、功能，均不支持 RBO
没有考虑目标 SQL 所涉及对象的实际数据量
固定规则难以适应数据或查询变化（不考虑目标 SQL 所涉及对象的实际数量和分布情况），可能选出次优或极差的执行计划
RBO 执行计划的调整
等价改写 SQL，Number 或 Date 类型的字段加上 0,Varchar 类型拼接上空字符’’,使本来走索引的现在不走索引，对于多表连接的改变 form 后表的先后顺序可以影响表连接顺序
如果出现多条等级值相同的执行路径，改变目标 SQL 中涉及相关对象数据字典缓存（Data Dictionary Cache）的缓存顺序
如果出现多条等级值相同的执行路径，改变目标 SQL 中涉及相关对象在 SQL 文本中出现的先后顺序，但是仅凭目标 SQL 各条执行路径等级值的大小就可以选择出执行计划，那么无论怎么调整相关对象在该 SQL 在文本中的位置，对于该 SQL 最终的执行计划都不会有任何影响。
基于成本的优化器（CBO）
为了解决 RBO 上诉缺陷，从 Oracle7 开始引入 CBO，CBO 会从目标 SQL 诸多可能的执行路径中选择一条成本值最小的执行路径来作为其执行计划。各条执行路径的成本值是根据目标 SQL 语句所涉及的表，索引，列等相关对象的统计信息计算出来的。
统计信息是指存储在 Oracle 数据库的数据字典的一组数据，且从多个维度描述了 Oracle 数据库里相关对象的实际数据量，实际数据分布等详细信息。
这里的成本是指 Oracle 根据相关对象的统计信息计算出来的一个值，它实际上代表了 Oracle 根据相关统计信息估算出来的目标 SQL 的对应执行步骤的 I/O,cpu 和网络资源的消耗量。
CBO 相关的基础概念
基数（CARDINALITY）
某个列唯一键的数量叫作基数。比如性别列，该列只有男女之分，故这一列基数为2。
主键列的基数等于表的总行数。基数的高低影响列的数据分布。
当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表中5%的数据时，应该走全表扫描。
这就意味着当某个列基数很低，该列数据分布就会很不均匀，由于该列分布不均匀，会导致SQL查询可能走索引，也可能走全表扫描。我们可以用以下语句来查看列的分布情况。
select 列,count(*) from 表 group by 列 order by 2 desc;CBO 估算Cardinality 的公式如下：
computed Cardinality = Original Cardinality * selectivity
选择性（SELECTIVITY）
基数与总行数的比值再乘以100%就是某个列的选择性。下面的脚本用于查询表中每个列的基数与选择性。当一个列的选择性大于20%时，说明该列的数据分布就比较均衡了。选择性的值越大，意味着返回的结果集的基数的值就越大，所以估算的成本值也就越大。
select a.column_name,b.NUM_ROWS,a.num_distinct CARDINALITY,round(a.num_distinct/b.NUM_ROWS * 100,2) SELECTIVITY,a.HISTOGRAM,a.num_buckets
from dba_tab_col_statistics a, dba_tables b where
a.owner=b.OWNER and a.table_name=b.TABLE_NAME and a.table_name='XXXX'Q:什么列需要建立索引？
A:当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须建立索引，从而提升SQL查询效率。
直方图（HISTOGRAM）
前面说过某个列基数很低，该列数据分布就会很不均匀，由于该列分布不均匀，会导致SQL查询可能走索引，也可能走全表扫描，这个时候很容易走错执行计划。
如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。
可传递性（Transitivity）
可传递性是 CBO 特有的概念，它是 CBO 在查询转换中所做的第一件事，其含义是指 CBO 可能会对原目标 SQL 做简单的等价改写，即在原目标 SQL 中加上根据该 SQL 现有的谓词条件推算出来的新的谓词条件，这样做的目的是提供更多的执行路径给 CBO 做选择。
可传递性可分为以下三种场景
1）简单谓语传递
比如原目标SQL中的谓语条件是“t1.c1=t2.c1 and t1.c1=10”,则CBO可能会给谓语条件额外加上“t2.c1=10”。
2）连接谓语传递
比如原目标SQL中的谓语条件是“t1.c1=t2.c1 and t2.c1=t3.c1”,则CBO可能会给谓语条件额外加上“t1.c1=t3.c1”。
3）外链接谓语传递
比如原目标SQL中的谓语条件是“t1.c1=t2.c1(+) and t1.c1=10”,则CBO可能会给谓语条件额外加上“t2.c1(+)=10”
小实验
CREATE TABLE t1 (c1 NUMBER,c2 varchar2(10));
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO t1 (c1, c2) VALUES (i, 'Data'||i);
END LOOP;
COMMIT;
END;
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE INDEX idx_c1 ON t2(c1);
explain plan for SELECT * FROM t1,t2 WHERE t1.c1=t2.c1 AND t1.c1=20;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
。。。。。
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."C1"=20)
5 - access("T2"."C1"=20)
18 rows selected.此时可以看到 t2 走了 idx_c1 的索引，说明该语句被改写成 SELECT * FROM t1,t2 WHERE t1.c1=t2.c1 AND t1.c1=20 and t2.c1=20;
CBO的局限性
CBO会默认目标SQL语句where条件中出现的各个列之间出现是独立的，没有任何关联。并且CBO会根据这个前提条件来计算selectivity和cardinality，进而估算成本并选择执行计划。但是这种假设并不全是正确的，生产中列与列之间存在关联的现象并不罕见。目前可以用来缓解上述负面影响的方法是使用动态采样和多列统计信息。但动态采样的准确性取决于采样数据的质量以及数量，而多列统计信息并不适合用于多表之间有关联的情形，所以这两种方法只能算是缓解，并不算是完美的解决方案。
CBO会假设所有的目标SQL都是独立运行的，并且互不干扰，但实际情况却不完全是这样。
CBO对直方图统计信息有多方限制。主要体现在如下2个方面：
在oracle 12c之前，frequency类型的直方图所对应的bucket的数量不能超过254，这样如果列的distinct数量超过254，oracle就会使用height balanced类型的直方图。对于height balanced类型的直方图而言，oracle不会记录所有的nonpopular value的值，所以此种情况下CBO选错执行计划的概率会比frequency类型的情形要高。
在oracle数据库里，如果针对文本类型的字段手机直方图统计信息，则oracle只会将文本的前32个字符（实际只取前15个）取出来并将其转换为浮点数，然后将浮点数作为上述文本字段的直方图统计信息记录在数据字典里。
CBO在解析多表关联的目标SQL时，可能会漏选正确的执行计划。在oracle 11gR2中，CBO在解析这种多表关联的目标SQL时，所考虑的各个表的连接顺序的总和受隐含参数_OPTIMIZER_MAX_PERMUTATIONS的限制。这意味着目标SQL不管有多少种连接顺序，CBO最多只考虑其中根据_OPTIMIZER_MAX_PERMUTATIONS计算出来的有限种可能性。
评论