【Oracle】优化器之RBO与CBO

目录

• ​​2.2.1 编写等价SQL​​
• ​​2.2.2 修改对象在数据字典中的缓存顺序​​
• ​​2.2.3 多表连接修改表顺序​​

• ​​3.3.1 简单谓词传递​​
• ​​3.3.2 连接谓词传递​​
• ​​3.3.3 外连接谓词传递​​

• ​​3.4 局限性​​

1.什么是优化器？

优化器(Optimizer)是Oracle数据库内置的一个核心子系统，可以理解为Oracle数据库中的一个核心模块或者一个核心功能组件。优化器的目的是得到目标SQL的执行计划。

Oracle中的优化器分为2种：

• RBO：是Rule-Based Optimizer的缩写，直译为：基于规则的优化器
• CBO：是Cost-Based Optimizer的缩写，直译为：基于成本的优化器

RBO所用的判断原则为一组内置的规则，这些规则是硬编码在Oracle数据库代码中的，RBO会根据这些规则从目标SQL诸多可能的执行路径中选择一条来作为其执行计划；然而CBO所用的判断原则为成本，CBO会从目标SQL诸多可能的执行路径中选择成本指最小的一条作为其执行计划，各个执行路径的成本值是根据目标SQL语句所涉及的表、索引、列等相关对象的统计信息计算出来的。

Oracle数据库里的SQL语句执行过程可以用图1-1来表示

2.基于规则的优化器RBO

Oracle 10g后就开始不支持RBO了，但是RBO的代码并没有从Oracle数据库删除，这就意味着我们可以通过修改优化器块来继续使用RBO

Oracle会在代码里事先给各种类型的执行路径定一个等级，一共有15个等级，从等级1到等级15，并且Oracle会认为等级越低执行效率越高。在决定目标SQL的执行路径的时候，如果可能的执行路径不止一条，则RBO会从该SQL诸多可能的执行路径中选择一条等级值最低的执行路径来作为其执行计划。

对于OLTP的SQL语句，通过ROWID来访问是效率最高的方式，而通过全表扫描来访问则是效率最低的方式。与之对应的，RBO的内置等级1所对应的执行路径就是"single row by rowid"(通过rowid来访问单行数据)，而等级15对应的执行路径就是"full table scan(全表扫描)"

2.1 表2列同时存在索引，RBO如何选择？

（1）创建表并建立索引

-- 建表
create table emp_tmp as select * from emp;
-- 创建索引
create index idx_mgr_tmp on emp_tmp(mgr);
create index 了IDX_DEPTNO_TMP on emp_tmp(deptno);

（2）切换到RBO模式并验证RBO选择了哪一个索引

-- 切换为RBO模式
SQL> alter session set optimizer_mode=rule;

会话已更改。

-- 产生执行计划
/**
知识补充：
1.set autotrace off 不生成autotrace报告，这是默认模式；
2.set autotrace on explain 只显示优化器执行路径报告
3.set autotrace on statistics 只显示执行统计信息
4.set autotrace on 显示执行计划和统计信息
5.set autotrace traceonly 同4，但是不显示查询输出
6.set autotrace traceonly explain 同explain plan，但是不执行语句，只产生计划
7.set autotrace traceonly statistics 同5，只显示执行路径
*/ 
SQL> set autotrace traceonly explain

-- 执行SQL语句
select * from emp_tmp where mgr > 100 and deptno > 100

输出的执行计划如下：

从上图我们可以看到，RBO走了索引了IDX_DEPTNO_TMP，但是没走IDX_MGR_TMP索引。

2.2 调整RBO计划

针对2.1中的案例而言，加入我们发现走索引IDX_MGR_TMP的效率要高于索引IDX_DEPTNO_TMP，那么我怎么才能让RBO听取我的意见呢？

2.2.1 编写等价SQL

我们可以采取在deptno列上进行一些变换，比如：deptno+0，那么就不会走IDX_DEPTNO_TMP索引了

-- 等价SQL
select * from emp_tmp where mgr > 100 and deptno + 0> 100;

输出的执行计划如下：

通过上图我们发现，索引真的听话了，走了IDX_MGR_TMP，但是还有另外一种方法，如下2.2.2

2.2.2 修改对象在数据字典中的缓存顺序

由于刚才我们是先创建的IDX_MGR_TMP索引，然后才创建的IDX_DEPTNO_TMP索引，所以在数据字典的缓存中是先缓存IDX_MGR_TMP，再缓存IDX_DEPTNO_TMP，因为"砌墙的砖头，后来者居上"，所以RBO就选择了索引IDX_DEPTNO_TMP，此处，我们可以这样做，我们把索引IDX_MGR_TMP删了重建，不就让索引IDX_MGR_TMP称为后来者了么？

-- 删除索引
drop index IDX_MGR_TMP;

-- 重建索引
create index idx_mgr_tmp on emp_tmp(mgr);

-- 查询
select * from emp_tmp where mgr > 100 and deptno > 100;

输出的执行计划如下：

果不然，真是可以通过修改数据字典的缓存顺序来调整RBO对索引的选择。

2.2.3 多表连接修改表顺序

如果目标SQL出现了有两条或者两条以上的执行路径的等级值相同的情况，可以通过改变目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序来调整目标SQL的执行计划。这通常适用于目标SQL中出现了多表连接的情形，在目标SQL中出现了有两条或者两条以上的执行路径等级值相同的前提条件下，RBO会按照从右到左的顺序来决定谁是驱动表，谁是被驱动表，进而会据此来选择执行计划，如果我们改变了目标SQL中的涉及的各个对象在该SQL文本中出现的先后顺序，也就改变了表连接的驱动表和被驱动表，进而就调整了该SQL的执行计划。

-- 建表
create table emp_tmp1 as select * from emp;

-- 执行SQL
select t1.mgr,t2.deptno from emp_tmp t1,emp_tmp1 t2 where t1.empno=t2.empno

输出的执行计划如下：

从上图可以看出，表emp_tmp1 在SQL文本中的右边，所以该表是驱动表，emp_tmp是被驱动表。上述执行计划走的是排序合并连接。

严格意义上来说，排序合并连接并没有驱动表和被驱动表的概念，这里只是为了方便阐述而人为的给排序合并连接添加了上述概念

但是上述结论存在一个前提：目标SQL一定有两条或者两条以上执行路径的等级值相同。仅凭等级值大小RBO难以选择执行计划

我们使用emp表与emp_tmp表关联看看又是怎么回事呢？emp表在emnpno上存在主键索引

select t1.mgr,t2.deptno from emp t1,emp_tmp t2 where t1.empno=t2.empno;

此时，执行计划走的是嵌套循环连接，且驱动表是emp_tmp

我们将emp表与emp_tmp调换位置后在看看执行计划又是什么样的呢？

从上述可以看出，没有变化，那么也就验证了结论：如果RBO仅凭目标SQL各执行路径等级值的大小就可以选择出执行计划，那么无论怎么调整该对象在SQL文本中的位置，对于该SQL的执行计划都不会有影响。

3.基于成本的优化器CBO

RBO存在明显缺陷，比如Oracle中很多很好的特性，在RBO中已经不再支持，RBO产生的计划很难调整等，这些都还是次要的，最大的诟病在与RBO是硬编码在数据库中，并没有考虑目标SQL中涉及的对象的实际数量、实际数据分布等情况，这样一旦规则不适用于该SQL中所涉及的实际对象时，根据RBO产生的执行计划就不是最优的执行计划了。

举个例子说明：

select * from emp where deptno=20

假设在emp表的deptno上事先存在一个名为IDX_DEPTNO_TMP的单键值B树索引，如果我们适用RBO,那么，不管EMP的数据量有多大，也不管DEPTNO列的分布情况如何，Oracle执行的时候都会先去走索引，然后在回表取EMP中的记录。Oracle此时是不会对EMP表全表扫描的，对于RBO而言，全表扫描的等级值高于索引范围扫描的等级值。

RBO的这种表现在数据量不大，或者数据量很大但是满足条件的记录很少的情况下，是没问题的。但是如果数量较大，并且列DEPTNO有四分之三都是deptno=20的记录，那么RBO先去扫描一遍索引，再回表取数据，这显然就没有直接全表扫描来的快了。

基于RBO的缺点，索引在Oracle 7之后就引入了CBO，CBO在选择目标SQL的执行路径的时候，所有的判断原则为成本，CBO会从SQL语句的诸多执行路径中选择一条成本值最小的执行路径来作为其执行计划。各执行路径的成本值是根据目标SQL语句所涉及的表、索引、列等相关对象的统计信息计算出来的。

Oracle数据库中的成本实际上是对执行目标SQL所需要耗费的I/O、CPU和网络资源的一个估算值。

特别说明：
Oracle 在对一条执行路径计算成本的时候，并不一定会从头到尾完整计算完，只要Oracle在计算过程中发现算出来的部分成本值已经大于之前保存下来的到目前最小的成本值，就立马会终止对当前执行路径的成本值的计算，并转而执行下一条新的执行路径的成本。这个过程会一直持续下去，直到目标SQL各个可能的执行路径全部计算完毕或预先定义好的待计算的执行路径数量的阈值。

3.1 集的势

Cardinality是CBO特有的属性，译为：集的势。它是指集合包含的记录数，说白了就是指定结果集的行数。

Cardinality实际上表示对目标SQL的某个具体的执行步骤的执行结果所包含记录数的估算。当然，如果针对整个目标SQL，那么此时的Cardinality就表示对该SQL最终执行结果所包含的记录数的估算。

Cardinality和成本值的估算息息相关，因为Oracle得到的结果集所需要消耗的I/O资源可以近似的看作随着记录数的递增而递增。所以，某个执行步骤所对应的的Cardinality值越大，那么它对应的成本值就越大，这个执行步骤所在执行路径的总成本值也就会越大。

3.2 可选择率

可选择率(Selectivity)也是CBO的特有的概念。它是指施加指定谓词条件后返回结果集的记录数与未施加任何谓词条件的原始结果集的记录数的比值。

可选择率的取值范围为0~1,值越小，可选择率越好。

可选择率和成本值也是息息相关的，因为可选择率越大，就意味着返回的结果集的Cardinality的值越大，所以估算的成本之就越大。

实际上，CBO就使用可选择率来估算Cardinality的，此处使用Original Cardinality来表示未施加任何谓词条件的原始结果集的记录数，用Computed Cardinality来表示施加指定谓词条件后返回的结果集的记录数，则公式如下：

Computed Cardinality = Original Cardinality *  selectivity

虽然看起来选择率的计算公式很简单，但实际上具体的计算过程是很复杂的，每一个具体的情况都会有不同的计算公式，特别的，在目标列上没有直方图且没有NULL值的情况下，用目标列做等值查询的可选择率是用如下公式来计算的：

selectivity = 1/NUM_DISTINCT 
--这里的NUM_DISTINCT 表示目标列的distinct值的数量

案例：

--------建立测试表
create table person(id int,name varchar2(40),sal number,addr varchar2(100));
insert into person values(1,'Jack',10000,'China');
insert into person values(2,'Tom',20000,'China');
insert into person values(3,'Alice',30000,'China');
insert into person values(4,'Json',50000,'China');


SQL> alter table person modify (sal not null);

Table altered.

SQL> create index idx_person_sal on person(sal);

Index created.
SQL> select count(1) from person;

  COUNT(1)
----------
        4


SQL> select count(distinct sal) from person;

COUNT(DISTINCTSAL)
------------------
                 4
                 
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'PERSON',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1',no_invalidate => false);

PL/SQL procedure successfully completed.   

SQL> set linesize 800
SQL> set pagesize 900

SQL> set autotrace traceonly explain
SQL> select * from person where sal=10000;

输出执行计划如下：

上述执行计划中已经存在列Cost(%CPU)，这就说明在SQL解析的时候使用的是CBO，这里的Rows就是上述执行计划中的每一个执行步骤所对应的Cardinality的值，列Cost(%CPU)记录上述执行计划中的每一个步骤所对应的成本值。

从上述显示的内容可以看出，现在执行计划走的是IDX_PERSON_SAL的索引范围扫描，Id=2的执行步骤所对应的列Rows的值是1，这就说明CBO评估出来的该步骤对应的Cardinality为1，同理，Id=0对应的Cardinality也为1

那么这两个值CBO是怎么计算出来的呢？

上文已经提到：在目标列上没有直方图且没有NULL值的情况下，目标列做等值查询的可选择率为：1/4,再根据公式

Computed Cardinality = Original Cardinality *  selectivity

可以轻松的得到通过where条件查询后返回的记录数为：4 * （1/4）=1，所以Id=2步骤结果对应的Cardinality也是1,又因为整个查询只有一个where条件，所以最终执行结果所对应的Cardinality也是1

我们现在把列SAL的值全部修改为10000，再次进行测试

SQL> update person set sal=10000;

4 rows updated.
SQL> commit;

Commit complete.

-- 重新收集一下统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'PERSON',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1',no_invalidate => false);

PL/SQL procedure successfully completed.

SQL> select * from person where sal=10000;

输出执行计划如下：

从上图可以看到Cardinality的值均从1变成4了，这很容易理解，现在SAL列ditcinct后的值已经变为1，所以针对列SAL等值查询的可选择率就从1/4变成了1,所以执行步骤所对应的Cardinality与最终结果所对应的Cardinality的值都会是4*1/1=4

假如表person现在的数据量有1000万条，并且所有的sal的值都是10000，那么此时的CBO会做如何选择呢？

此处不一定要真要往表插入1000万行记录，重要让表的数据量为1000万就行了（因为CBO计算成本的时候完全基于目标SQL的相关对象的统计信息，所以这里只需要改一下表person和索引IDX_PERSON_SAL的统计信息即可）

-- 调整表的统计信息
SQL> exec dbms_stats.set_table_stats(ownname=>'SCOTT',tabname=>'PERSON',numrows=>10000000,no_invalidate => false);

PL/SQL procedure successfully completed.
-- 将索引IDX_PERSON_SAL对应其索引叶子块的数量的统计信息修改为10万
SQL> exec dbms_stats.set_index_stats(ownname=>'SCOTT',indname=>'IDX_PERSON_SAL',numlblks=>100000,no_invalidate => false);

PL/SQL procedure successfully completed.
SQL> select * from person where sal=10000;

输出的执行计划如下：

从图中可以看到，现在的Cardinality值均变为10M（10M表示1000万），也就是说，当这种极端情况出现的时候，CBO就不会走索引了，进行的是全表扫描了。

此时我们将优化器修改为RBO,查看结果

alter session set optimizer_mode=rule;
SQL> select * from person where sal=10000;

从上图可以看到，RBO模式还是选择走索引，就没CBO那么智能了。

3.3 可传递性

可传递性(Transitivity)是CBO特有的概念，CBO在查询转换中所做的第一件事情，其含义是指CBO可能会对原目标的SQL做简单的等价改写。利用可传递性对目标SQL做简单的的等价改写仅仅适用于CBO，RBO不会做这样的事情。

3.3.1 简单谓词传递

t1.c1 = t2.c1 and t1.c1=10 等价于t1.c1 = t2.c1 and t1.c1=10 and t2.c1=10 

3.3.2 连接谓词传递

t1.c1=t2.c1 and t2.c1=t3.c1 等价于 t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=t3.c1 

3.3.3 外连接谓词传递

t1.c1=t2.c1(+) and t1.c1=10 等价于 t1.c1=t2.c1(+) and t1.c1=10 and t2.c1(+)=10

案例测试：

-- 建立两个表
create table t1(c1 number,c2 varchar2(10));
create table t2(c1 number,c2 varchar2(10));

-- 在表t2的c1列上建立索引
create index idx_t2 on t2(c1);

-- 插入测试数据
insert into t1 values(10,'aaa');
insert into t1 values(11,'bbb');
insert into t1 values(12,'ccc');
insert into t1 values(13,'ddd');

insert into t2 values(10,'aaa');
insert into t2 values(11,'bbb');
insert into t2 values(12,'ccc');
insert into t2 values(13,'ddd');

--- 修改为CBO优化器
alter session set optimizer_mode=all_rows;

select t1.c1,t2.c1 from t1,t2 where t1.c1=t2.c1 and t1.c1=10

输出的执行计划为：

通过上图可以看到，虽然我们没有针对表t2的列c1的简单谓词条件，但是Oracle还是走了t2表的索引，在下面id=4的驱动查询条件为​​ 4 - access("T2"."C1"=10)​​,在我们的SQL中并没有这个谓词条件，说明该谓词条件是等价改写过来的。SQL被改写为：

select t1.c1,t2.c1 from t1,t2 where t1.c1=t2.c1 and t1.c1=10 and t2.c1=10

3.4 局限性

CBO的诞生就是为了解决RBO的先天性缺陷，但是随着Oracle版本的不断变化，CBO变得越来越智能了，越来越强悍了，但是这并不意味着CBO就是完美的。以下是CBO的缺陷：

（1）CBO默认目标SQL语句where条件中出现的各个列之间是独立的，没有关联关系

（2）CBO会假设所有的目标SQL都是单独执行的，并且互不干扰。

我们执行目标SQL时所需要访问的索引叶子块、数据块等可能由于之前执行的SQL而已经缓存在Buffer Cache中了，所以这次执行时也不需要耗费物理I/O去相关的磁盘上读取数据，只需要在缓存中读取就可以了。所以,CBO是单独执行的，不考虑缓存的方式去计算成本值的话，就可能会高估走相关索引的成本，进而可能会导致选错执行计划。

（3）CBO对直方图统计信息有诸多限制

（4）CBO在解析多表关联的目标SQL的时候，可能会漏选正确的执行计划。

作者：奔跑的金鱼