理解Oracle的优化器

什么是数据库的优化器

最近有些朋友希望我能够解释一下Oracle数据库的优化器在CBO里的一些特点，我想就写一篇文章吧，这样其他有同样问题的朋友们也能够参考一下。

那么，数据库的优化器是什么呢，优化器主要是用于决定SQL语句的执行计划，执行计划就是如何通过一系列的步骤从而得出SQL语句的执行结果的一个“计划”，顾名思义，“执行计划”就是一个计划而已，并不是真正执行了SQL语句，需要一个执行计划的根本原因是要取得我们的SQL语句所需要的结果通常有多种不同的方式，最简单最常见的比如：当数据库需要执行一个SQL语句

Select * from emp where empno=1234

的时候，既可以去找到组成emp表的所有数据块扫描一遍，从中找出所有empno的值为1234的记录，也可以从扫描empno的索引块里（假如存在这么一个索引的话）找到所有empno入口为1234的值相对应的rowid，再通过这些rowid找到相对应的记录从而得出同样的结果，这个最简单的例子就显示了优化器的一些基本特征：如何在数据库服务层面的概念（都是些数据块，索引块的概念在起作用）里执行一个SQL语句，从而得出SQL语句所需要的结果，其实两种方式都可以得出我们需要的结果，但是数据库的优化器会选择它认为代价最小的一种执行“计划”，这个就是现在所说的CBO（cost-based optimization）的含义。

优化器在数据库里已经存在了很长的时间了，在Oracle版本8或者之前，并不存在我们上面所介绍的优化器选择最小代价执行计划的问题，那个时候优化器决定执行路径的方式是通过一系列的规则去决定执行计划，比如对于上面的同样的例子，优化器的工作方式则基于规则的优化方式，如下图：

基于规则的优化方式就是根据一些预先定义好的规则库，逐步去决定执行计划，比如上述的优化就是根据一个索引规则：如果where col=?的col字段上建立了一个索引，就优先使用该索引来访问数据，如果没有索引，则通过全表扫描方式来访问数据，基于规则优化的优化器有一个规则库，每一个SQL语句的执行计划都会对照这个规则库的规则来生成，所以每一个SQL在一个数据库版本里都具有非常确定的执行计划，不会因为表或者索引的数据量的大小改变。这个既是规则优化的优势，也是规则优化的致命缺点。说是优势是因为规则优化的确定性，一个SQL语句只要在测试环境了测试好了，在生产环境就一定会有相同的执行计划从而不会出现系统忽慢忽快的表现；说这是规则优化的致命确定是因为执行计划不会因为一些显然的条件进行执行计划的变更，不会利用某些从我们自己来看显然能够加快执行速度的一些外部条件。举个例子来说如果有一张表person里总共有10万条的记录，person里有一个性别字段gender并建有索引，这个字段的取值只可能是F（代表女性），M（代表男性）又或者是BI(代表。。。：))如果我们这个表的数据出于某种原因绝大部分数据都是女性，比如说女性数据占了99990条，男性的数据就只有10条记录，现在我们来考察SQL语句

Select * from person where gender=’M’

的执行计划，在规则优化模式下，这个SQL语句永远会选择走索引的执行计划，这个执行计划的确是上面的语句的最优的执行计划？ 但是且慢，我们再来考察SQL语句

Select * from person where gender=’F’

的执行计划，这个时候我们会发现，使用索引的执行计划远要比不使用索引的执行计划慢的多，但是如果我们使用成本优化方式，我们会发现优化器会自动在第一个SQL语句里选择索引，而在第二个SQL语句里选择全表扫描，在两种条件下基于成本的优化方式都选择了正确的执行计划，这个就是成本优化的最大优点。

数据库的优化器通常工作在后台，但是数据库本身提供了命令让开发人员也能够看见优化器决定后的执行计划，如explain语句或者sqlplus的autotrace，又或者通过plsql developer或者Oracle的EM管理器，这样就为我们熟悉优化器的行为提供了很好的手段，我们可以通过各种方式去影响优化器，而可以清楚地看到每个影响是如何影响优化器的。

对于成本优化的一个通常疑惑是：对于执行计划所显示的执行成本，有些时候明明是成本数字显示出来低的执行计划，为什么执行的时候时间反而比成本数字显示高的执行计划的执行时间还要更长，我想根本原因还是在于大家需要记住，执行计划只是一个“计划”而已，在大部分的时候它选择的执行计划是正确的，但是又并非总是如此，这就如我们早上听交通台，说从国贸到天安门是通畅的，可是当我们走这条路的时候，却发现极其拥堵是一样的。当各种优化器的输入项有所误差（比如数据对象的统计信息没有更新），又或者哪怕根本没有误差，优化器有时候选择出来的执行计划对于一个具体的执行并非总是最好的，当你对于执行计划理解得越多，你就越能够理解为什么有些时候执行计划不能总是选择一个最佳的执行计划。

我的应用为什么不使用索引？

我想对于许多人的一个最大的疑惑就是当明明感觉使用索引要更快的时候，Oracle的优化器为什么选择了全表扫描，特别是许多使用习惯了规则优化的人来说，简直觉得Oracle数据库的新版本好像比以前退步了似的，可是真是如此吗？我们上一篇文章已经初步介绍了基于成本优化的Oracle优化器的概念，今天就来仔细研究这个问题。

优化器执行过程简介

上图是一个优化器如何生成执行计划的过程，优化器第一步首先是看看已经被编译（parse）的SQL语句是否能够定向到相关物化视图（或者11g的olap立方体），这个过程是Query Transformer的过程，这一个部分对于数据仓库的项目而言特别重要，我们以后会专门去介绍这部分的内容；然后就到了估计成本的过程，在估计SQL语句执行成本的时候，统计信息（存在在数据字典里）是至关重要的信息，因为在10g的优化器里已经不再允许规则优化方式，所以如果缺失了统计信息，则数据库将自动进行采用式的统计信息收集（请注意，这个10g的行为和9i的已经不一样了，9i的版本是没有统计信息的时候则专用规则优化，而10g已经取消了规则优化，所以在缺失统计信息的时候只好由优化器自动地去马上做一个统计信息收集），显而易见这个过程会影响SQL的执行效率，所以在10g的时候必须保证统计信息得到了有效的收集（创建完数据库之后一般而言会创建自动收集统计信息的任务，但是我们仍然需要确认该任务顺利完成并且该任务的收集是符合我们的要求的，我曾经碰到过一个客户，他的执行计划总是有问题，但是他声称他的数据库里已经有自动统计信息收集的任务存在，后来发现那个自动任务出于某种原因从来没有自动完成过）。

Estimator的目标是估计并计算一个执行计划的具体执行代价，Estimator通常是使用3个指标对执行计划进行评价，他们分别是Selectivity, Cardinality, 和Cost。

Cardinality：代表了一个结果集的记录的条数，如果你曾经使用过plsql developer来查看执行计划的话，你可以发现执行计划里就cardinality这一项。如下图：

这个记录数可能是一个join或者select或者group by产生的结果集，它可以被作为执行计划里下一步的输入，因而在一个更加复杂的执行计划里，可以看见Cardinality和Cost都会在每个步骤改变。

Cost：这个是成本优化里提到的最多的概念，也就是执行一个SQL语句的成本，它通常是由一个数值来代表，通常的概念就是，当Cost越高，则执行成本越高，则就意味着SQL语句所需要花费的资源和时间越长。但是Cost的具体的值本身并没有非常明确的含义，通常它的数值代表的是一个SQL可能花费的资源的综合情况（CPU，IO，内存等），但是并没有和特别明确的单位对应上，一般可以认为，如果经过我们的优化，可以发现Cost减少了，则该SQL的真正耗费的资源会减少。

Estimator的最终用途就是要计算不同执行计划的成本，然后选择一个成本最小的执行方式，这个就是成本优化的核心。

现在回过头来看我们开始提到的问题：为什么我的SQL不使用索引？建议大家对比使用索引方式和优化器自动选择的不使用索引方式的成本进行对比（可以考虑通过使用hint的方式强制优化器选择索引），对比之后可能你就能够明白，原因是如果你选择了索引方式，你会发现，Cost增加了，而成本优化就是要选择最小成本的执行计划，所以优化器没有选择索引的执行方式。可是，我们似乎又回到了那个问题：明明使用索引SQL执行更快呀！！怎么会成本更高呢？回答这个问题就需要考察：什么因素会影响成本？

在本文结束前需要澄清的一个概念就是，并非所有的全表扫描就一定比索引扫描慢，如果大家对于数据库的存储机制比较清楚的话，就不难理解这句话的意思：无论是表数据还是索引，都是由一个一个的数据块组成，通过索引选取一条记录的最少IO是两个（一个IO读取索引数据块找到表的数据的rowid，一个IO读取通过rowid读取表的数据块）；而如果使用全表扫描，最少的时候却只需要一个IO就可以完成任务（当然前提是该IO能够包含我们所需要的数据的数据块）