Oracle SQL优化实践系列1——优化器与成本

《SQL优化器最佳实践》——韩锋 著

本文大部分内容取自韩锋《SQL优化器最佳实践》，文章用于记录本人学习过程，非商用使用。

第2章 优化器与成本

话题一、【优化器】

什么是优化器？优化器的作用是什么？

优化器是数据库最核心的功能，也是最复杂的一部分。它负责将用户提交的SQL语句根据各种判断标准，制定出最优的执行计划，并交由执行器来最终执行。优化器算法的好坏、能力的强弱，直接决定了语句的执行效率。Oracle的优化器也是在不断演变中的，早期使用的是基于规则的优化器（Rule Based Optimizer,RBO），后期逐步演变为基于成本的优化器（Cost Based Optimizer,CBO）。下面将介绍分别介绍两种优化器，一起他们之间的区别和简单使用方式。

基于规则的优化器RBO

基于规则的优化器内部采用了一种规则列表，其中每一种规则代表了一种执行路径并被赋予一个等级，不同的等级代表不同的优先级别。等级越高的规则越会被优先采用。具体规则内容如下（随着oracle自身技术的发展，CBO优化器成为首选，所以对此部分不感兴趣的读者可跳过）：

[path 1] Single Row by Rowid：根据ROWID，返回一条记录。这种规则发生在SQL语句的where部分，制定了记录的ROWID或者使用了CURRENT OR CURSOR形式的SQL。（备注：CURRENT OR CURSOR游标使用过程中，表示当前游标指定数据）

[path 2] Single Row By Cluster Join：根据聚蔟连接，返回一条记录。这种规则发生在SQL语句中WHERE部分，包含了两表关联，且关联字段为一个聚簇，同时还存在一个过滤条件为一个表的唯一索引或主键。（此处的聚簇连接不是很明白，感觉应该是聚簇表中各表的关联连接的意思o(╯□╰)o）

[path 3] Single Row By Hash Cluster Key with Unique or Primary Key：根据哈希聚簇键，返回一条记录。这种规则发生在SQL语句的Where部分包含的过滤条件中，字段是一个哈希聚簇键且这个字段唯一或主键索引字段。

[path 4] Single Row by Unique or Primary Key：根据主键或唯一索引键值，返回一条记录。这种规则发生在SQL语句中where部分，为唯一或主键所有字段的等值连接条件。

[path 5] Clusterd Join：根据聚簇连接，返回一组记录。这种规则跟path2类似，只不过过滤条件中没有唯一限制，可以返回多条记录。

[path 6] Hash Cluster Key:根据哈希聚簇键值，返回一条记录。同上

[path 7] Indexed Cluster Key：根据一个索引的聚簇键字段，返回一组记录。

[path 8] Composite Index：根据一个组合索引字段，返回一组记录。这种规则中where部分需要制定组合索引字段且通过逻辑“与”运算符进行连接。

[path 9] Single-Column Indexes：根据单一索引字段，返回一组记录。

[path 10] Bound Range Search on Indexed Columns：根据索引子弹的有限范围搜索，返回一组记录。这里所说的有限范围搜索，包括字段的等值比较、大于等于和小于等于、between...and、like等过滤条件。

[path 11] Unbounded Range Search on Indexed Columns：根据索引字段的无线范围搜索，返回一组记录。这里所说的无线范围搜索，包括字段的大于等于、小于等于过滤条件。

[path 12] Sort Merge Join：根据排序合并关联，返回一组数据。

[path 13] Max or Min of Indexed Column：获取一个索引字段的最大、最小值。这种规则需要遍历整个索引。

[path 14] Order by on Indexed Column： 根据一个索引字段，进行排序操作。

[path 15] Full Table Scan：通过全表扫描方式，获取一个结果集。

RBO优化器的优点：1) RBO的判断有规律可以参照，易于用户对其判断进行正确的预测；2）在已经创建了战略性索引的前提下这些规则的普遍适用性就变得非常高。

RBO优化器的缺点：1）忽视了具有实际意义的统计信息而导致判断误差比较大；2）使用RBO，执行计划一旦出了问题，很难对其做调整；3）使用RBO，容易受SQL写法的不同导致选择不同执行计划；4）Oracle中很多高版本中出现的好的特性、功能不能在RBO下使用

基于成本的优化器CBO

基于成本的优化器在坚持实事求是原则的基础上，通过对具有现实意义的诸多要素的分析和计算来完成最优路径的选择工作。随着Oracle版本的不断演变，CBO优化器变得越来越智能，但需要注意的是，CBO仍然存在一些特殊情况，导致其可能产生较差的执行计划。CBO存在的问题主要包括：多列关联关系、SQL无关性、直方图统计信息、复杂多表关联。

CBO优化器的优点：1）考虑到了对象特征信息，因此更具有现实性的最优化；2）可以通过对统计信息的管理来控制最优化；3）减少最坏情况的发生概率，不容易产生特别差的执行计划（个人认为RBO容易出现选择比较差的执行计划的情况，但也可以通过制定执行计划选择到最好的执行计划，而CBO通过成本控制，不容易认为控制执行路线的选择，只不过其选择最坏情况的概率远远低于RBO）

CBO优化器的缺点：1）算法十分复杂，难以提前预测执行计划；2）依然在不断演变中，不同版本间变化较大，造成数据库升级的风险较大。

上面简单说明了基于成本的优化器，但到底成本是什么，又是通过怎样的方式去依赖成本决定执行路线呢？

成本

成本是指花费在单数据块读取上的时间，加上花费在多数据块读取上的时间，再加上CPU处理时间，然后将总和处以单数据快读取所花费的时间。（这句话看完有没有感觉一脸懵逼...）

据作者书中描述，由于甲骨文并未公开oracle成本的计算方式，并且其计算方式也在随着版本的更迭而改变，所以成本的具体概念也在不断变化中，总结起来就是：成本是一条语句的预计执行总时间，以单数据块读取时间单元的形式来表示。执行总时间可以理解，但单数据快读取时间单元的形式是什么呢？其实就是你这台机器读取单数据块所花费的时间。每条语句预计花费的时间（相同的机器上）可以通过算法得到，而不同的机器上肯定最后得到的时间不同，那就需要一个统一的单位来评估到底一个执行计划是否高效，所以就有了单数据块读取时间单元。--以上属于个人理解，如有错误，敬请指正。

下面公布一下作者书中所写的成本计算公式：

Cost=(#SRDs * sreadtim + #MRDs * mreadtim + #CPUCyles / cpuspeed) / sreadtim

#SRDs：单数据块读取的次数

#MRDs：多数据块读取的次数

#CPUCycles：CPU时钟频率

#sreadtim：随机读取单数据块的平均时间，单位为毫秒

#mreadtim：顺序读取多数据块的平均时间，也就是多数据块平均读取时间，单位为毫秒

#cpuspeed：代表有负载CPU速度，CPU速度为每分钟CPU周期数，也就是一个CPU一秒能处理的操作数，单位是百万次/秒

成本的内容介绍就这些，个人认为可以先不必纠结其含义和计算方式，后面的技术内容逐步深入后应该会有很多的理解。

优化器相关参数
优化器相关Hint

剩余的两个内容暂时不再扩展，如果后面的内容发现需要此处知识点再扩充。如有需要可以留言