Oracle 里的优化器

最新推荐文章于 2024-04-16 19:26:43 发布

DB架构

最新推荐文章于 2024-04-16 19:26:43 发布

阅读量2.2k

点赞数 4

分类专栏： DBA 文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/u011868279/article/details/128791911

版权

DBA 专栏收录该内容

183 篇文章 12 订阅

订阅专栏

对于关系型数据库而言，优化器是最核心的部分。主要是因为优化器负责解析SQL。而大家都是通过SQL来访问存储在数据库中的数据。故此，优化器的好坏直接决定该关系型数据库的强弱。

同时，想要做好SQL优化，就必须深入了解优化器。这是基础。

1.什么是优化器（Optimizer）？

优化器是Oracle数据库中内置的一个核心子系统，可以理解为Oracle数据库中的一个核心模块或者一个核心功能组件。

2.优化器的目的是什么？

优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前情形下最高效的执行路径（Access Path）,优化器的目的就是为了得到目标SQL的执行计划。

3.优化器有几种类型？

依据选择执行计划时所用的判断原则，Oracle数据库里的优化器又分为RBO和CBO这两种类型。RBO 是Rule-Based Optimizer（基于规则的优化器）的缩写。

CBO是Cost-Based Optimizer（基于成本的优化器）的缩写。

4.当得到目标SQL的执行计划时，RBO和CBO各自使用的判断原则是什么？

在得到目标SQL的执行计划时，

RBO所用的判断原则为一组内置的规则，这些规则是硬编码在Oracle数据库的代码中的，RBO会根据这些规则从目标SQL诸多可能的执行路径中选择一条来作为其执行计划；

而CBO所用的判断原则为成本，CBO会从目标SQL诸多可能的执行路径中选择成本值最小的一条来作为其执行计划，各个执行路径的成本值是根据目标SQL语句所涉及的表、索引、列等相关对象的统计信息计算出来的。

5.Oracle数据库里SQL语句的执行过程是怎样的？

5.基于规则的优化器（RBO）的工作原理是什么？

基于规则的优化器（RBO）通过硬编码在Oracle数据库代码中的一系列固定的规则，来决定目标SQL的执行计划。具体来说就是这样：Oracle会在代码里事先给各种类型的执行路径定一个等级，一共有15个等级，从等级1到等级15。并且Oracle会认为等级值低的执行路径的执行效率会比等级值高的执行效率要高，也就是说在RBO的眼里，等级1所对应的执行路径的执行效率最高，等级15所对应的执行路径的执行效率最低。在决定目标SQL的执行计划时，如果可能的执行路径不止一条，则RBO就会从该SQL诸多可能的执行路径中选择一条等级值最低的执行路径来作为其执行计划。

6.RBO是一种适用于OLTP类型SQL语句的优化器，在这样的前提条件下，大家来猜一猜RBO的等级1和等级15所对应的执行路径分别是什么？

对于OLTP类型的SQL语句而言，显然通过ROWID来访问是效率最高的方式，而通过全表扫描来访问则是效率最低的方式。RBO内置的等级1所对应的执行路径就是“single row by rowid（通过rowid来访问单行数据）”，而等级15所对应的执行路径则是“full table scan（全表扫描）”。

从Oracle 10g开始，RBO已不再被Oracle支持，但RBO的相关实现代码并没有从Oracle数据库的代码中移除，这意味着即使是在Oracle 11gR2中，我们依然可以通过修改优化器模式或使用RULE Hint来继续使用RBO。

7.相比于CBO,RBO有哪些缺陷？

在使用RBO的情况下，执行计划一旦出了问题，很难对其做调整
如果使用了RBO，则目标SQL的写法，甚至是目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序，都可能会影响RBO对于该SQL执行计划的选择。
Oracle数据库中很多很好的特性、功能均不能在RBO下使用，因为它们均不被RBO所支持。

8. Oracle数据库中有哪些特性、功能不能被RBO所支持？

目标SQL中涉及的对象有IOT（Index Organized Table）。
目标SQL中涉及的对象有分区表。
使用了并行查询或者并行DML。
使用了星型连接。
使用了哈希连接。
使用了索引快速全扫描。
使用了函数索引。

9. 在使用RBO的情况下，一旦RBO选择的执行计划并不是当前情形下最优的执行计划，应该如何对其做调整呢？

很难对RBO选择的执行计划做调整的，其中非常关键的一个原因就是不能使用Hint，因为如果在目标SQL中使用了Hint，就意味着自动启用了CBO，即Oracle会以CBO来解析含Hint的目标SQL。这里仅有两个例外，就是RULE Hint和DRIVING_SITE Hint，它们可以在RBO下使用并且不自动启用CBO。

10.是不是在使用RBO的情况下就没办法对执行计划做调整了？

当然不是这样，只是这种情况下我们的调整手段会非常有限。其中的一种可行的方法就是等价改写目标SQL，例如在目标SQL的where条件中对NUMBER或DATE类型的列加上0（如果是VARCHAR2或CHAR类型，可以加上一个空字符，例如 ||''），这样就可以让原本可以走的索引现在走不了。对于包含多表连接的目标SQL而言，这种改变甚至可以影响表连接的顺序，进而就可以实现在使用RBO的情况下对该目标SQL的执行计划做调整的目的。

11.RBO会从目标SQL诸多可能的执行路径中选择一条等级值最低的作为其执行计划，但如果出现了两条或者两条以上等级值相同的执行路径的情况，那么此时RBO会如何选择呢？

此时RBO会依据目标SQL中所涉及的相关对象在数据字典缓存（Data Dictionary Cache）中的缓存顺序和目标SQL中所涉及的各个对象在目标SQL文本中出现的先后顺序来综合判断。这也就意味着我们还可以通过调整相关对象在数据字典缓存中的缓存顺序，改变目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序来调整其执行计划。

看一个在使用RBO的情况下对目标SQL的执行计划做调整的实例。创建一个测试表EMP_TEMP：

[root@oracle-db-19c ~]# su - oracle
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 29 18:12:01 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn scott/tiger@PDB1
Connected.
SQL> show user;
USER is "SCOTT"
SQL> set pagesize 200
SQL> set linesize 200
SQL> create table emp_temp as select * from emp;

Table created.

SQL> create index idx_mgr_temp on emp_temp(mgr);

Index created.

SQL> create index idx_deptno_temp on emp_temp(deptno);

Index created.

SQL>

范例1：


SQL> 
SQL> show user;
USER is "SCOTT"
SQL> select * from emp_temp where mgr > 100 and deptno > 100;

no rows selected

SQL>

对于范例SQL 1而言，其where条件中出现了列MGR和DEPTNO，而在列MGR和DEPTNO上分别存在着索引IDX_MGR_TEMP和IDX_DEPTNO_TEMP。

12.如果在启用RBO的情形下执行范例SQL 1，则Oracle会选择走上述两个索引中的哪一个？

实际验证一下。在当前Session中将优化器模式修改为RULE，表示在当前Session中启用RBO：

SQL> alter session set optimizer_mode='RULE';

Session altered.

SQL> set autotrace traceonly explain
SQL> select * from emp_temp where mgr > 100 and deptno > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 1670750536

-------------------------------------------------------
| Id  | Operation                   | Name            |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP        |
|*  2 |   INDEX RANGE SCAN          | IDX_DEPTNO_TEMP |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MGR">100)
   2 - access("DEPTNO">100)

Note
-----
   - rule based optimizer used (consider using cbo)

SQL>

id2的执行步骤说明Oracle在执行上述范例SQL 1时使用的是RBO，且选择的是走对索引IDX_DEPTNO_TEMP的索引范围扫描。

范例SQL 1的where条件中有“mgr>100”，所以RBO实际上是可以选择走列MGR上的索引IDX_MGR_TEMP的，只不过RBO这里并没有选择走该索引，而是选择走列DEPTNO上的索引IDX_DEPTNO_TEMP。

13.假如我们发现走索引IDX_DEPTNO_TEMP不如走索引IDX_MGR_TEMP的执行效率高，或者说我们就想让RBO选择走索引IDX_MGR_TEMP，那么应该如何做呢？

在使用RBO的情况下，可以通过等价改写目标SQL（加0或者空字符串的方式）来调整该SQL的执行计划。列DEPTNO的类型为NUMBER，所以我们可以在列DEPTNO上加0，来达到不让RBO选择走其上的索引IDX_DEPTNO_TEMP的目的。在列DEPTNO上加0后即形成了如下形式的范例SQL 2：

SQL> alter session set optimizer_mode='RULE';

Session altered.

SQL> set autotrace traceonly explain

SQL> select * from emp_temp where mgr > 100 and deptno+0 > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 2973289657

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |
|*  2 |   INDEX RANGE SCAN          | IDX_MGR_TEMP |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"+0>100)
   2 - access("MGR">100)

Note
-----
   - rule based optimizer used (consider using cbo)

SQL>

此时Id = 2的执行步骤已经从之前的“INDEX RANGE SCAN | IDX_DEPTNO_TEMP”变为了现在的“INDEX RANGE SCAN |IDX_MGR_TEMP”，这说明我们确实迫使RBO改变了执行计划，即我们的调整已经生效了。

14. 如果目标SQL出现了有两条或者两条以上的执行路径的等级值相同的情况，该如何调整执行计划？

方法一：

我们可以通过调整相关对象在数据字典缓存中的缓存顺序来影响RBO对于其执行计划的选择。对于范例SQL 1而言，对索引IDX_DEPTNO_TEMP走索引范围扫描和对索引IDX_MGR_TEMP走索引范围扫描的等级值显然是相同的，所以我们就可以通过调整这两个索引在数据字典缓存中的缓存顺序来改变执行计划。

刚才我们先创建索引IDX_MGR_TEMP，再创建索引IDX_DEPTNO_TEMP，所以索引IDX_MGR_TEMP 和IDX_DEPTNO_TEMP在数据字典缓存中的缓存顺序是，先缓存IDX_MGR_TEMP，再缓存IDX_DEPTNO_TEMP。这种情形下RBO选择的是走对索引IDX_DEPTNO_TEMP的索引范围扫描，如果我们现在把索引IDX_MGR_TEMP先Drop掉再重新创建一次，那么就相当于是先创建索引IDX_DEPTNO_TEMP，再创建索引IDX_MGR_TEMP，也就是说此时这两个索引在数据字典缓存中的缓存顺序就刚好颠倒过来了。按照此前介绍的知识，此时RBO应该就会选择走对索引IDX_MGR_TEMP的索引范围扫描。

验证实验：

先Drop索引IDX_MGR_TEMP,再重建。

SQL> 
SQL> drop index idx_mgr_temp;

Index dropped.

SQL> create index idx_mgr_temp on emp_temp(mgr);

Index created.

SQL> alter session set optimizer_mode='RULE';

Session altered.

SQL> set autotrace traceonly explain
SQL> select * from emp_temp where mgr > 100 and deptno > 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 2973289657

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     |
|*  2 |   INDEX RANGE SCAN          | IDX_MGR_TEMP |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO">100)
   2 - access("MGR">100)

Note
-----
   - rule based optimizer used (consider using cbo)

SQL>

id= 2的执行步骤已经从之前的“INDEX RANGE SCAN | IDX_DEPTNO_TEMP”变为了现在的“INDEX RANGE SCAN |IDX_MGR_TEMP”，说明我们确实迫使RBO改变了执行计划，这也说明当目标SQL有两条或者两条以上的执行路径的等级值相同时，我们确实可以通过调整相关对象在数据字典缓存中的缓存顺序来影响RBO对于其执行计划的选择。

方法二：

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

范例3 SQL 3:

select t1.mgr, t2.deptno
  from emp_temp t1, emp_temp1 t2
 where t1.empno = t2.empno;

对于范例SQL 3而言，表EMP_TEMP和EMP_TEMP1唯一的表连接条件为“t1.empno = t2.empno”，而在表EMP_TEMP和EMP_TEMP1的字段EMPNO上均没有任何索引，按照前面介绍的知识，表EMP_TEMP1 在SQL文本中的位置是在表EMP_TEMP的右边，所以此时RBO会将表EMP_TEMP1作为表连接的驱动表，而将表EMP_TEMP作为表连接的被驱动表。

SQL> create table emp_temp1 as select * from emp;

Table created.

SQL>

SQL> alter session set optimizer_mode='RULE';

Session altered.

SQL> set autotrace traceonly explain
SQL> select t1.mgr, t2.deptno
  2    from emp_temp t1, emp_temp1 t2
  3   where t1.empno = t2.empno;

Execution Plan
----------------------------------------------------------
Plan hash value: 1323777565

-----------------------------------------
| Id  | Operation           | Name      |
-----------------------------------------
|   0 | SELECT STATEMENT    |           |
|   1 |  MERGE JOIN         |           |
|   2 |   SORT JOIN         |           |
|   3 |    TABLE ACCESS FULL| EMP_TEMP1 |
|*  4 |   SORT JOIN         |           |
|   5 |    TABLE ACCESS FULL| EMP_TEMP  |
-----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"="T2"."EMPNO")
       filter("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)

SQL>

从上面显示的内容可以看出，现在范例SQL 3的执行计划走的是排序合并连接，且驱动表确实是表EMP_TEMP1。

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

将范例SQL 3中的表EMP_TEMP和EMP_TEMP1在该SQL的SQL文本中的位置换一下，即形成了如下形式的范例SQL 4：

select t1.mgr, t2.deptno
  from emp_temp1 t2, emp_temp t1
 where t1.empno = t2.empno;

SQL> 
SQL> select t1.mgr, t2.deptno
  2    from emp_temp1 t2, emp_temp t1
  3   where t1.empno = t2.empno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2135683657

-----------------------------------------
| Id  | Operation           | Name      |
-----------------------------------------
|   0 | SELECT STATEMENT    |           |
|   1 |  MERGE JOIN         |           |
|   2 |   SORT JOIN         |           |
|   3 |    TABLE ACCESS FULL| EMP_TEMP  |
|*  4 |   SORT JOIN         |           |
|   5 |    TABLE ACCESS FULL| EMP_TEMP1 |
-----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"="T2"."EMPNO")
       filter("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)

SQL>

在范例SQL 4的执行计划走的也是排序合并连接，且驱动表确实已经由之前的表EMP_TEMP1变为了现在的表EMP_TEMP。这说明我们确实使RBO改变了执行计划，也说明当目标SQL有两条或者两条以上的执行路径的等级值相同时，我们确实可以通过改变目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序来影响RBO对于其执行计划的选择。

注意，这种位置的先后顺序对于目标SQL执行计划的影响是有前提条件的，那就是仅凭各条执行路径等级值的大小RBO难以选择执行计划，也就是说该目标SQL一定有两条或者两条以上执行路径的等级值相同。换句话说，如果RBO仅凭各条执行路径等级值的大小就可以选择目标SQL的执行计划，那么无论怎么调整相关对象在该SQL的SQL文本中的位置，对于该SQL最终的执行计划都不会有任何影响。

验证一下上述结论。看看如下的范例SQL 5：

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

对于范例SQL 5而言，表EMP和EMP_TEMP唯一的表连接条件为“t1.empno =t2.empno”。对于表EMP而言，列EMPNO上存在主键索引PK_EMP，而对于表EMP_TEMP而言，列EMPNO上不存在任何索引。所以在使用RBO的情况下，范例SQL 5的执行路径将不再仅限于排序合并连接（RBO不支持哈希连接），也就是说RBO此时有可能可以仅凭各条执行路径等级值的大小就选择出范例SQL 5的执行计划。

SQL> 
SQL> select t1.mgr, t2.deptno
  2    from emp t1, emp_temp t2
  3   where t1.empno = t2.empno;

Execution Plan
----------------------------------------------------------
Plan hash value: 367190759

-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  NESTED LOOPS                |          |
|   2 |   NESTED LOOPS               |          |
|   3 |    TABLE ACCESS FULL         | EMP_TEMP |
|*  4 |    INDEX UNIQUE SCAN         | PK_EMP   |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP      |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)

SQL>

从上面显示的内容可以看出，现在范例SQL 5的执行计划走的是嵌套循环连接，且驱动表是表EMP_TEMP。

将范例SQL 5中的表EMP和EMP_TEMP在该SQL的SQL文本中的位置换一下，即形成了如下形式的范例SQL 6：

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

SQL> 
SQL> select t1.mgr, t2.deptno
  2    from emp_temp t2, emp t1
  3   where t1.empno = t2.empno;

Execution Plan
----------------------------------------------------------
Plan hash value: 367190759

-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  NESTED LOOPS                |          |
|   2 |   NESTED LOOPS               |          |
|   3 |    TABLE ACCESS FULL         | EMP_TEMP |
|*  4 |    INDEX UNIQUE SCAN         | PK_EMP   |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP      |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)

SQL>

从上面显示的内容可以看出，现在范例SQL 6的执行计划走的还是嵌套循环连接，且驱动表依然是表EMP_TEMP。这就验证了我们之前提到的观点：如果RBO仅凭目标SQL各条执行路径等级值的大小就可以选择出执行计划，那么无论怎么调整相关对象在该SQL的SQL文本中的位置，对于该SQL最终的执行计划都不会有任何影响。

RBO最大的问题在于它是靠硬编码在Oracle数据库代码中的一系列固定的规则来决定目标SQL的执行计划的，而并没有考虑目标SQL中所涉及的对象的实际数据量、实际数据分布等情况，这样一旦固定的规则并不适用于该SQL中所涉及的实际对象时，RBO根据固定规则产生的执行计划就很可能不是当前情况下的最优执行计划了。

15.基于成本的优化器（CBO）的工作原理是什么？

为了解决RBO的上述先天缺陷，从Oracle 7开始，Oracle就引入了CBO。之前已经提到过，CBO在选择目标SQL的执行计划时，所用的判断原则为成本，CBO会从目标SQL诸多可能的执行路径中选择一条成本值最小的执行路径来作为其执行计划，各条执行路径的成本值是根据目标SQL语句所涉及的表、索引、列等相关对象的统计信息计算出来的。

统计信息是这样的一组数据：它们存储在Oracle数据库的数据字典里，且从多个维度描述了Oracle数据库里相关对象的实际数据量、实际数据分布等详细信息

成本是指Oracle根据相关对象的统计信息计算出来的一个值，它实际上代表了Oracle根据相关统计信息估算出来的目标SQL的对应执行步骤的I/O、CPU和网络资源的消耗量，这也就意味着Oracle数据库里的成本实际上就是对执行目标SQL所要耗费的I/O、CPU和网络资源的一个估算值。

16.网络资源消耗是指什么？

网络资源消耗适用于那些使用了dblink的分布式目标SQL，CBO在解析该类SQL时知道在实际执行它们时所需要的数据并不全部在本地数据库中（需要去远程数据库中取数据），所以此时的网络资源消耗就会被CBO考虑在内。这里需要注意的是，Oracle会把解析这种分布式目标SQL所需要考虑的网络资源消耗折算成对等的I/O资源消耗，所以实际上你可以认为Oracle数据库里的成本仅仅依赖于执行目标SQL时所需要耗费的I/O和CPU资源。

另外需要注意的是，在Oracle未引入系统统计信息之前，CBO所计算的成本值实际上全部是基于I/O来估算的，只有在Oracle引入了系统统计信息之后，CBO所计算的成本值才真正依赖于目标SQL的I/O和CPU消耗。

从上述对CBO的介绍中我们可以看出：CBO会从目标SQL诸多可能的执行路径中选择一条成本值最小的执行路径来作为其执行计划，这也就意味着CBO会认为那些消耗系统I/O和CPU资源最少的执行路径就是当前情况下的最佳选择。注意，这里的“消耗系统I/O和CPU资源”（即成本）的计算方法会随着优化器模式的不同而不同。

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

16.什么是集的势（基数）

Cardinality（基数）是CBO特有的概念，直译过来就是“集的势”，它是指指定集合所包含的记录数，说白了就是指定结果集的行数。这个指定结果集是与目标SQL执行计划的某个具体执行步骤相对应的，也就是说Cardinality实际上表示对目标SQL的某个具体执行步骤的执行结果所包含记录数的估算。当然，如果是针对整个目标SQL，那么此时的Cardinality就表示对该SQL最终执行结果所包含记录数的估算。

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

17.可选择率的定义是什么？

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

可选择率可以用如下的公式来表示：

从上述计算可选择率的公式可以看出，可选择率的取值范围显然是0～1，它的值越小，就表明可选择性越好。毫无疑问，可选择率为1时的可选择性是最差的。

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

实际上，CBO就是用可选择率来估算对应结果集的Cardinality的，上述关于可选择率的计算公式等价转换后就可以用来估算Cardinality的值。这里我们用“Original Cardinality”来表示未施加任何谓词条件的原始结果集的记录数，用“Computed Cardinality”来表示施加指定谓词条件后返回结果集的记录数，CBO用来估算Cardinality的公式如下：

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

来看一下CBO会如何计算列MGR的可选择率和该SQL返回结果集的Cardinality。

SQL> delete from emp where mgr is null;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter table emp modify (mgr not null);

Table altered.

SQL> create index idx_emp_mgr on emp(mgr);

Index created.

SQL> select count(*) from emp;

  COUNT(*)
----------
        13

SQL> select count(distinct mgr) from emp;

COUNT(DISTINCTMGR)
------------------
                 6

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

PL/SQL procedure successfully completed.

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 29 20:38:25 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn scott/tiger@PDB1;
Connected.
SQL> set autotrace traceonly
SQL> set linesize 800
SQL> set pagesize 900
SQL> select * from emp where mgr=7902;


Execution Plan
----------------------------------------------------------
Plan hash value: 351129165

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     2 |    76 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP         |     2 |    76 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_EMP_MGR |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MGR"=7902)


Statistics
----------------------------------------------------------
         62  recursive calls
          0  db block gets
         94  consistent gets
          0  physical reads
          0  redo size
       1105  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Oracle在解析目标SQL时就会默认使用CBO。注意到上述执行计划的显示内容中有列Rows和列Cost (%CPU)，这说明Oracle在解析范例SQL 7时确实使用的是CBO。这里列Rows记录的就是上述执行计划中的每一个执行步骤所对应的Cardinality的值，列Cost (%CPU) 记录的就是上述执行计划中的每一个执行步骤所对应的成本值。

从对范例SQL 7的实际执行过程我们可以得到如下结论。

（1）RBO确实是靠硬编码在Oracle数据库代码中的一系列固定的规则来决定目标SQL的执行计划的，并没有考虑目标SQL中所涉及的对象的实际数据量、实际数据分布等情况。而CBO则恰恰相反，CBO会根据反映目标SQL中相关对象的实际数据量、实际数据分布等情况的统计信息来决定其执行计划，这就意味着CBO选择的执行计划可能会随着目标SQL中所涉及的对象的统计信息的变化而变化。CBO的这种变化是颠覆性的，这意味着只要统计信息相对准确，则用CBO来解析目标SQL会比在同等条件下用RBO来解析得到正确执行计划的概率要高。

（2）Cardinality和Selectivity的值会直接影响CBO对于相关执行步骤成本值的估算，进而影响CBO对于目标SQL执行计划的选择。

18.什么是可传递性？

可传递性（Transitivity）也是CBO特有的概念，它是CBO在图1-1的查询转换中所做的第一件事情，其含义是指CBO可能会对原目标SQL做简单的等价改写，即在原目标SQL中加上根据该SQL现有的谓词条件推算出来的新的谓词条件，这么做的目的是提供更多的执行路径给CBO做选择，进而增加得到更高效执行计划的可能性。这里需要注意的是，利用可传递性对目标SQL做简单的等价改写仅仅适用于CBO，RBO不会做这样的事情。

在Oracle里，可传递性又分为如下这三种情形。

1．简单谓词传递

比如原目标SQL中的谓词条件是“t1.c1=t2.c1 and t1.c1=10”，则CBO可能会在这个谓词条件中额外地加上“t2.c1=10”，即CBO可能会将原谓词条件“t1.c1=t2.c1 and t1.c1=10”修改为“t1.c1=t2.c1 and t1.c1=10 and t2.c1=10”。改写前后的谓词条件显然是等价的，因为如果t1.c1=t2.c1且t1.c1=10，那么我们就可以推算出t2.c1也等于10。

2．连接谓词传递

比如原目标SQL中的谓词条件是“t1.c1=t2.c1 and t2.c1=t3.c1”，则CBO可能会在这个谓词条件中额外地加上“t1.c1=t3.c1”，即CBO可能会将原谓词条件“t1.c1=t2.c1 and t2.c1=t3.c1”修改为“t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=t3.c1”，同理，这里改写前后的谓词条件也是等价的。

3．外连接谓词传递

比如原目标SQL中的谓词条件是“t1.c1=t2.c1(+) and t1.c1=10”，则CBO可能会在这个谓词条件中额外加上“t2.c1(+)=10”，即CBO可能会将原谓词条件“t1.c1=t2.c1(+) and t1.c1=10”修改为“t1.c1=t2.c1(+) and t1.c1=10 and t2.c1(+)=10”。

Oracle利用可传递性对目标SQL做简单的等价改写的目的是为了提供更多的执行路径给CBO做选择，进而增加得到更高效执行计划的可能性。

19.CBO的局限性有哪些？

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

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

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

CBO对直方图统计信息的限制体现在如下两个方面。

在Oracle 12c之前，Frequency类型的直方图所对应的Bucket的数量不能超过254，这样如果目标列的distinct值的数量超过254，Oracle就会使用Height Balanced类型的直方图。对于Height Balanced类型的直方图而言，因为Oracle不会记录所有的nonpopular value的值，所以在此情况下CBO选错执行计划的概率会比对应的直方图统计信息是Frequency类型的情形要高。
在Oracle数据库里，如果针对文本型的字段收集直方图统计信息，则Oracle只会将该文本型字段的文本值的头32字节给取出来（实际上只取头15字节）并将其转换成一个浮点数，然后将该浮点数作为上述文本型字段的直方图统计信息存储在数据字典里。这种处理机制的先天缺陷就在于，对于那些超过32字节的文本型字段，只要对应记录的文本值的头32字节相同，Oracle在收集直方图统计信息的时候就会认为这些记录该字段的文本值是相同的，即使实际上它们并不相同。这种先天性的缺陷会直接影响CBO对相关文本型字段的可选择率及返回结果集的Cardinality的估算，进而就可能导致CBO选错执行计划。

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

20.优化器的模式有哪些？

优化器的模式用于决定在Oracle中解析目标SQL时所用优化器的类型，以及决定当使用CBO时计算成本值的侧重点。这里的“侧重点”是指当使用CBO来计算目标SQL各条执行路径的成本值时，计算成本值的方法会随着优化器模式的不同而不同。

优化器的模式是由参数OPTIMIZER_MODE的值来决定的，OPTIMIZER_MODE的值可能是RULE、CHOOSE、FIRST_ROWS_n（n = 1, 10,100, 1000）、FIRST_ROWS或ALL_ROWS。

OPTIMIZER_MODE的各个可能的值的含义为如下所示。

1．RULE

RULE表示Oracle将使用RBO来解析目标SQL，此时目标SQL中所涉及的各个对象的统计信息对于RBO来说将没有任何作用。

2．CHOOSE

CHOOSE是Oracle 9i中OPTIMIZER_MODE的默认值，它表示Oracle在解析目标SQL时到底是使用RBO还是使用CBO取决于该SQL中所涉及的表对象是否有统计信息。具体来说就是：只要该SQL中所涉及的表对象中有一个有统计信息，那么Oracle在解析该SQL时就会使用CBO；如果该SQL中所涉及的所有表对象均没有统计信息，那么此时Oracle就会使用RBO。

3．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中的任意一个值，其含义是指当OPTIMIZER_MODE的值为FIRST_ROWS_n（n = 1, 10, 100,1000）时，Oracle会使用CBO来解析目标SQL，且此时CBO在计算该SQL的各条执行路径的成本值时的侧重点在于以最快的响应速度返回头n（n = 1, 10, 100,1000）条记录。

4．FIRST_ROWS

FIRST_ROWS是一个在Oracle 9i中就已经过时的参数，它表示Oracle在解析目标SQL时会联合使用CBO 和RBO。这里联合使用CBO和RBO的含义是指在大多数情况下，FIRST_ROWS还是会使用CBO来解析目标SQL，且此时CBO在计算该SQL的各条执行路径的成本值时的侧重点在于以最快的响应速度返回头几条记录（类似于FIRST_ROWS_n）；但是，当出现了一些特定情况时，FIRST_ROWS转而会使用RBO中的一些内置的规则来选取执行计划而不再考虑成本。比如当OPTIMIZER_MODE的值为FIRST_ROWS时有一个内置的规则，就是如果Oracle发现能用相关的索引来避免排序，则Oracle就会选择该索引所对应的执行路径而不再考虑成本，这显然是不合理的。与之相对应的，在OPTIMIZER_MODE的值为FIRST_ROWS的情形下，你会发现索引全扫描出现的概率会比之前有所增加，这是因为走索引全扫描能够避免排序的缘故。

5．ALL_ROWS

ALL_ROWS是Oracle 10g以及后续Oracle数据库版本中OPTIMIZER_MODE的默认值，它表示Oracle会使用CBO来解析目标SQL，且此时CBO在计算该SQL的各条执行路径的成本值时的侧重点在于最佳的吞吐量（即最小的系统I/O和CPU资源的消耗量）。