原理篇01–优化器与成本
优化器是数据库最核心的功能，也是最复杂的一部分。它负责将用户提交的SQL语句根据各种判断标准，制定出最优的执行计划，并交由执行器来最终执行。优化器算法的好坏、能力的强弱，直接决定了语句的执行效率。综合比较来说，Oracle的优化器是功能最强大的。当然，优化器本身是数据库系统中最为复杂的一个部分。
成本是优化器（基于成本的优化器）中反映 SQL语句执行代价的一个指标。优化器通过比较不同执行计划的成本，选择成本最小的作为最终的执行计划。如何理解成本、成本如何计算也就成为我 们学习基于成本的优化器的关键所在。
1. 优化器
优化器在整个SQL语句的执行过程中充当了非常重要的角色。
下图是一个SQL语句从提交到最终得到结果的示意图，从中我们可以看到优化器充当的角色及其主要功能。
Oracle的优化器也是在不断演变中的。在早期的版本中，Oracle使用一种基于规则的优化器。顾名思义，它是按照某种特定的规则来制定执行计划的。这种方式比较简单直观，但对数据库自身情况及SQL语句中对象本身的情况都没有考虑。在后期的Oracle版本中，又推出了另外一种优化器——基于成本的优化器。
基于规则的优化器
基于规则的优化器（Rule Based Optimizer， RBO ）内部采用了一种规则列表，其中每一种规则代表一种执行路径并被赋予一个等级，不同的等级代表不同的优先级别。 等级越高的规则越会被优先采用。
Oracle会在代码里事先给各种类型的执行路 径定一个等级，一共有15个等级，从等级1到等级15。Oracle会认为等级值低的执行路径的执行效率 比等级值高的执行效率高。在决定目标SQL的执行 计划时，如果可能的执行路径不止一条，则RBO就 会从该SQL多种可能的执行路径中选择一条等级最 低的执行路径来作为其执行计划。
·Single Row by Rowid： 根据ROWID，返回一 条记录。这种规则发生在SQL语句的WHERE部分，指定了记录的ROWID或者使用了CURRENT OF CURSOR形式的SQL。
·Single Row by Cluster Join： 根据聚簇连接，返回一条记录。这种规则发生在SQL语句中 WHERE部分，包含了两表关联，且关联字段为一个聚簇，同时还存在一个过滤条件为一个表的唯一 索引或主键。
·Single Row by Hash Cluster Key with Unique or Primary Key： 根据哈希聚簇键，返回一条记录。这种规则发生在SQL语句的WHERE部分所包含的过滤条件中，字段是一个哈希聚簇键且这个字段为唯一或主键索引字段。
·Single Row by Unique or Primary Key： 根据主键或唯一索引键值，返回一条记录。这种规则发生在SQL语句中WHERE部分，为唯一或主键所有字段的等值连接条件。
·Clustered Join： 根据聚簇连接，返回一组记录。这种规则跟Path 2类似，只不过过滤条件中没有唯一限制，可以返回多条记录。
·Hash Cluster Key： 根据哈希聚簇键值，返回一条记录。这种规则跟Path 3类似，只不过过滤条件中没有唯一限制，可以返回多条记录。
·Indexed Cluster Key： 根据一个索引的聚簇键字段，返回一组记录。
·Composite Index： 根据一个组合索引字段，返回一组记录。这种规则中WHERE部分需要指定组合索引字段且通过逻辑“与”运算符进行连接。
·Single-Column Indexes： 根据单一索引字段， 返回一组记录。
·Bounded Range Search on Indexed Columns： 根据索引字段的有限范围搜索，返回一组记录。这里所说的有限范围搜索，包括字段的等值比较、大于等于和小于等于、BETWEEN…AND、LIKE等过滤条件。
·Unbounded Range Search on Indexed Columns： 根据索引字段的无限范围搜索，返回一组记录。这里所说的无限范围搜索，包括字段的大于等于、小于等于过滤条件。
·Sort Merge Join： 根据排序合并关联，返回一组记录。
·MAX or MIN of Indexed Column： 获取一个索引字段的最大、最小值。这种规则需要遍历整个索引。
·ORDER BY on Indexed Column： 根据一个索引字段，进行排序操作。
·Full Table Scan： 通过全表扫描方式，获取一个结果集。
随着Oracle自身技术的发展，在一般的工作场景中，很少会涉及使用RBO的情况。CBO优化器成为首选。只有在极个别的情况下，需要手工调整行计划时，可采取指定优化器参数或引用相关的提示。且需要注意的是，因为RBO技术出现比较早，很多新的技术其不支持，因此在很多情况下即使手工指定使用RBO优化器，也可能会失效，Oracle仍然会使用CBO优化器。
基于成本的优化器
基于成本的优化器（Cost Based Optimizer， CBO）在坚持实事求是原则的基础上，通过对具有现实意义的诸多要素的分析和计算来完成最优路径的选择工作。成本可以理解为SQL执行的代价。成本越低，SQL执行的代价越小，CBO也就认为是一个更优异的执行路径。
但是CBO仍然存在一些特殊情况，导致其可能产生较差的执行计划。这也是以后CBO发展，需要弥补的弱点。
CBO存在的问题主要有以下几个方面：
- 多列关联关系：在默认情况下，CBO认为 WHERE条件中的各个字段之间是独立的，并据此计算其选择率，进而估计成本来选择执行计划。
- SQL无关性： CBO认为SQL语句运行都是相对独立的，之间没有任何关系；但在实际运行中可能是有关联的。
- 直方图统计信息：对于文本型字段的直方图收集，Oracle只会提取前32字节（对于多字节字符集来说更加严重），这样获得的数据会失真， 可能会导致优化器获得错误的执行计划。
- 复杂多表关联： 对于复杂的多表关联，其可能的表间关联顺序组合随着表的数量增加呈几何级数增长。而CBO至多只会考虑其中根据参数_OPTIMIZER_MAX_PERMUTATIONS计算出来的有限种可能，也意味着只要该目标SQL正确的执行计划不在上述有限种可能之中，则CBO一定会漏选最优的执行计划。
在通常情况下，选用CBO优化器，这也是Oracle强大之所在。在极个别的情况下，也存在对CBO优化器不适合使用的情况下，原因可能是BUG或者CBO设计问题。此时可以考虑使 用RBO优化器，但即使是这种情况，也要严格限制特定范围，一般只在语句级使用RBO优化器。
优化器相关参数
参数名 | 取值说明 |
optimizer_mode | RULE：使用RBO优化器 CHOOSE：根据实际情况，如果数据字典中包含被引用的表的统计数据，即引用的对象已经被分析，则使用CBO优化器，否则为RBO优化器 ALL_ROWS：为CBO优化器使用的第一种具体的优化方法，以数据的吞吐量为主要目标，以便可以使用最少的资源完成语句 FIRST_ROWS：为优化器使用的第二种具体的优化方法，以数据的响应时间为主要目标，以便快速查询出开始的几行数据 FIRST_ROWS_(1/10/100/1000)：为优化器使用的第三种具体的优化方法，让优化器选择一个能够把响应时间减到最小的查询执行计划，以迅速产生查询结果的前n行 |
optimizer_features_enable | 控制使用的优化 器特征的版本 |
优化器相关Hint
在SQL优化中，除了可以通过修改参数的方式干预优化器工作外，还可以使用Hint—提示的方式进行干预，而且这种方式更加精准、不影响其他SQL，故使用场景更加广泛。
--格式范例
select /*+ all_rows */ empno,ename,sal,job from emp where empno=7369;名称 | 说明 |
ALL_ROWS | 优化器启用CBO,且执行计划选择吞吐量最佳的路径（10g后为默认模式） |
FIRST_ROWS(n) | 优化器启用CBO,且执行计划选择最快响应且返回前n条记录的路径 |
RULE | 优化器对目标SQL启用RBO |
2 成本
Oracle绝大多数情况下就是使用基于成本的优化器对SQL语句制定执行计划的。 认识成本有利于理解优化器的行为，也更容易找出产生较差执行计划的原因。但对于成本及其计算方法，Oracle公司并没有开放很多资料，因而只能从一些公开的资料揣摩其工作原理、计算方法等。
成本的基本概念
成本是指花费在单数据块读取上的时间，加上花费在多数据块读取上的时间，再加上所需的CPU 处理时间，然后将总和除以单数据块读取所花费的时间。也就是说，成本是语句的预计执行时间的总和，以单数据块读取时间单元的形式来表示。
计算公式
Cost=(#SRDs*sreadtim + #MRDs*mreadtim + #CPUCycles/cpuspeed )/sreadtim参数 | 说明 |
#SRDs | 单数据块读取的次数 |
#MRDs | 多数据块读取的次数 |
#CPUCycles | CPU时钟频率 |
sreadtim | 随机读取单数据块的平均时间，单位为毫秒 |
mreadtim | 顺序读取多数据块的平均时间，单位为毫秒 |
cpuspeed | 代表有负载CPU速度，CPU速度为每秒钟CPU周期数，也就是一个CPU一秒能处理的操作数，单位是百万次/秒 |
