Oracle里的优化器

1 What

优化器（Optimizer） 是Oracle数据库中内置的一个 核心子系统。

其目的是：按照一定的判断原则来得到它认为的目标SQL在当前情形下最高效的执行路径（Access Path）。

根据判断原则，可分为 RBO 和 CBO 两种类型的优化器：

• RBO （Rule-Based Optimizer）：基于（内置的）规则的优化器，规则 硬编码在Oracle数据库的代码中；
• CBO（Cost-Based Optimizer）：基于成本的优化器，成本根据目标SQL语句所涉及的表、索引、列等相关对象的统计信息计算出来。

以下是Oracle数据库里的SQL语句的执行过程：

1. 首先执行对目标SQL的语法、语义和权限的检查；
2. 如果通过检查，Oracle会在Library Cache中查找匹配的Shared Cursor;
   如果找到匹配的Shared Cursor，则重用其存储的解析树和执行计划；
   如不匹配，则根据一些规则来决定是否对目标SQL执行查询转换；
3. 10g后，Oracle会计算比较经过查询转换后的等价改写SQL的成本和原始SQL的成本，低于才执行查询转换；
4. 接着根据不同的优化器类型，Oracle采取不同的判断原则，从查询转换后的诸多执行路径中选一条作为执行计划；
5. 最后根据这个执行计划去实际执行该SQL，将执行结果反馈给用户。

1.1 RBO ---- 基于规则的优化器

Oracle一共有15个等级的执行路径，默认等级值低的执行路径的执行效率会比等级值高的执行效率要高；所以RBO会从决定目标SQL的执行计划中选择一条等级值最低的执行路径来作为执行计划。

开启RBO模式：

alter session set optimizer_mode='RULE';

如果RBO选择的执行计划不是当前情形下最优的执行计划，有以下几个办法调整：

1. 使用 RULE Hint 和 DRIVING_SITE Hint 可以调整RBO的执行计划且不自动启用CBO；

2. 等价改下目标SQL：
   比如在sql的where条件中对number或者date类型的列加0，

   select * from 表格 where a+0 > 参数
   

   如果是varchar2类型的，加可以加个空字符串

   select * from 表格 where a || '' = 参数
   

   对于执行路径一样的情况：假如出现执行路径一样的情况，这时候就要根据数据字典缓存先后顺序来确定等级，确定哪条作为执行计划。

3. 通过调整相关对象在数据字典缓存中的缓存顺序，改变目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序来调整执行计划。

1.2 CBO ---- 基于成本的优化器

CBO会从目标SQL诸多可能的执行路径中选择一条成本值最小的路径来作为执行计划；

各路执行路径的成本值是根据目标SQL语句所涉及的表、索引、列等相关对象的统计信息计算出来的；

成本 即 对执行目标SQL所要耗费的I/O、CPU和网络资源的一个估算值；

即CBO会认为消耗系统I/O和CPU资源最少的执行路径即最佳选择。

1.2.1 Cardinality 集的势

表示对目标SQL的某个具体步骤的执行结果所包含记录数的估算；

其值越大，对应成本值越大。

1.2.2 Selectivity 可选择率

其公式表示：
S e l e c t i v i t y = 施 加 指 定 谓 词 条 件 后 返 回 结 果 集 的