【性能优化】一个执行计划异常变更的案例（上）

作者简介：

刘晨，网名bisal，Oracle 10g/11g OCM，并国内首批Oracle YEP成员，

博客：blog.itpub.net/bisal

案例介绍

今天快下班的时候，几位兄弟来聊一个问题，大致是昨天应用使用的数据库突然出现性能问题，DBA发现有一些delete语句执行时间骤长，消耗大量系统资源，导致应用响应时间变长积Q。

目前掌握的信息如下：

(1) 应用已经很久未做过更新上线了。 (2) 据开发人员反馈，从之前的应用日志看，未出现处理时间逐步变长的现象。 (3) 这是一套RAC+DG的环境，版本未知，猜测至少应该是11g的版本。 (4) 这次突然出现大量执行时间超长的SQL语句，是一系列delete语句，例如delete from table where key=:1or key=:2 … key=:13这种SQL，应用正常的处理逻辑中都会使用这条语句，因此并发较高，使用了绑定变量，key字段不是主键，但有索引。目前尚不知晓字段是否存在直方图。 (5) 表的数据量大约5000万，初步反馈得知key=0的记录大约1500万，执行时间超长的SQL语句都使用了key=0的条件，至于key=0的真实数据量，以及出现问题的SQL语句使用的绑定变量具体值，这些还需要开发再次确认。 (6) DBA反馈SQL语句执行计划发生了变化，从数据库层面做了一些操作后，问题解决，目前尚不知晓做了什么具体的操作。

已知的上述信息很有限，不能给出非常明确的出错原因，需要明日进一步和开发以及DBA了解后才能做深入的分析，了解真正的问题根源。

什么情况下可能造成SQL执行计划发生改变？

有很多种情况，这里抛砖引玉举一个例子。再次声明，以下实验和上面的问题可能没有直接关系，仅是引申的一些观点，上面问题的根源还有待进一步确认和排查。

实验： 创建测试表t1，其中name字段设置索引，取值为10000个A和1个B。

我们看下用查询条件name=’A’的SQL使用了什么执行计划

再看下使用查询条件name=’B’的SQL用了什么执行计划，

显而易见，因为取值为A的记录占据了10000/10001接近100%的比重，即这查询条件返回了几乎表的所有数据。使用全表扫描的成本一般会小于使用索引的成本，由于TABLE ACCESS FULL会扫描表高水位线以下的数据块，且为多块读，具体数据块数量取决于参数db_file_multiblock_read_count，而INDEX RANGESCAN则是单块读，同时若select字段不是索引字段的话，还需要回表，累积起来，IO次数就会可能很大，因此相比起来，全表扫描的IO可能会远小于索引扫描。

取值为B的记录占据了1/10001很小的比重，因此使用索引扫描，直接访问B*Tree二叉树，定位到这一条数据的rowid再回表查询所有select字段的成本要远小于扫描整张表数据的成本。

为了证明，可以查看这两条SQL对应的10053事件，如下是name=’A’的trace，可以看出全表扫描的成本值是49.63，索引扫描的成本值是351.26，全表扫描的成本更低一些。

如下是name=’B’的trace，可以看出全表扫描的成本值是49.40，索引扫描的成本值是2.00，索引扫描的成本值更低一些。

这个场景可以看出，Oracle的CBO模式会根据字段的取值比重调整对应的执行计划，无论如何，都会选择成本值最低的一个执行计划，这也是CBO优于以前RBO的地方，这里仅用于实验，因为一般OLTP的应用会使用绑定变量的写法，不会像上面这种使用常量值的写法。11g之前，可能带来的一些负面影响就是绑定变量窥探的作用，即对于使用绑定变量窥探的SQL语句，Oracle会根据第一次执行使用的绑定变量值来用于以后的执行，即第一次做硬解析的时候，窥探了变量值，之后的软解析，不再窥视。

如果上面实验的SQL语句使用了绑定变量，第一次执行时name=’A’，则接下来即使使用name=’B’的SQL语句仍会使用全表扫描，不会选择索引扫描，vice versa。相关的实验dbsnake的书中会有很详细的说明，可以参考。

11g之后，有了ACS自适应游标的新特性，会根据绑定变量值的情况可以重新生成执行计划，因此这种问题得到了缓解，当然这些都是有代价的，缓解了绑定变量窥探的副作用，相应地可能会导致有很多子游标，具体的算法可以参考dbsanke的书，这儿我就不班门弄斧了。11g默认绑定变量窥探是开启的，由以下隐藏参数控制，

综上所述，针对这场景，如果值的选择性显著影响执行计划，则绑定变量的使用并不可靠，此时选择字面值的方式可能会更合适一些，如果值的选择性几乎相同，执行计划不会显著改变，此时使用绑定变量是最优的选择，当然前提是OLTP系统。

对于多次执行SQL语句，执行计划发生变化的情况可能还有很多，例如11g的新特性Cardinality Feedback带来的一些bug，包含直方图的字段作为查询条件但统计信息不准(dbsnake的书中有一个案例)等，有机会做一些实验，再呈现出来。

接下来我将介绍跟案例相关的一些基础知识。

绑定变量窥探

首先什么是绑定变量？

一条SQL语句在解析阶段，会根据SQL文本对应的哈希值在库缓存中查找是否有匹配的Parent Cursor，进而找出是否有可重用的解析树和执行计划，若没有则要重新生成一遍，OLTP系统中，高并发的SQL若每次均需要重复执行这些操作，即所谓的硬解析，消耗会比较大，进而影响系统性能，所以就需要使用绑定变量。绑定变量其实就是一些占位符，用于替换SQL文本中具体输入值，例如以下两条SQL：

select * from t1 where id = 1; select * fromt1 where id = 2;

在Oracle看来，是两条完全不同的SQL，即对应SQL文本哈希值不同，因为where条件中一个id是1，一个是2，1和2的ASCII是不同的，可实际上这两条SQL除了查询条件不同，其他的文本字符均一致，尽管如此，这种情况下，Oracle还是会重复执行解析的操作，生成各自的游标。

两条记录，说明Oracle认为这两条SQL是不同。如果使用绑定变量，

select * from t1 where id = :1;

每次将不同的参数值带入:1中，语义和上面两条相同，但对应哈希值可是1个，换句话说，解析树和执行计划是可以重用的。

使用绑定变量除了以上可以避免硬解析的好处之外，还有其自身的缺陷，就是这种纯绑定变量的使用适合于绑定变量列值比较均匀分布的情况，如果绑定变量列值有一些非均匀分布的特殊值，就可能会造成非高效的执行计划被选择。如下是测试表：

其中name列是非唯一索引，NAME是A的有100000条记录，NAME是B的有1条记录，值分布是不均匀的，上一篇文章中我们使用如下两条SQL做实验，

select* from t1 where name = 'A'; select* from t1 where name = 'B';

其中第一条使用的是全表扫描，第二条使用了索引范围扫描，过程和原因上篇文章中有叙述，此处就不再赘述。

如上SQL使用的是字面值或常量值作为检索条件，接下来我们使用绑定变量的方式来执行SQL，为了更好地说明，此处我们先关闭绑定变量窥探(默认情况下，是开启的状态)，它是什么我们稍后再说。

首先A为条件

显示使用了全表扫描。再以B为条件，

发现仍旧是全表扫描，我们之前知道B值记录只有一条，应该使用索引范围扫描，而且这两个SQL执行计划中Rows、Bytes和Cost值完全一致。之所以是这样，是因为这儿用的未开启绑定变量窥探情况下的绑定变量，Oracle不知道绑定变量值是什么，只能采用常规的计算Cardinality方式，参考dbsnake的书，CBO用来估算Cardinality的公式如下：

ComputedCardinality = Original Cardinality * Selectivity Selectivity = 1 / NUM_DISTINCT

收集统计信息后，计算如下：

Computed Cardinality = 100001 * 1 / 2

约等于50001。因此无论是A还是B值，CBO认为结果集都是50001，占据一半的表记录总量，自然会选择全表扫描，而不是索引扫描。

下面我们说说绑定变量窥探，是9i引入的一个新特性，其作用就是会查看SQL谓词的值，以便生成最佳的执行计划，其受隐藏参数控制，默认为开启。

我们在绑定变量窥探开启的情况下，再次执行上述两条SQL(区别仅是不用explain plan，使用dbms_xplan.display_cursor可以得到更详细的信息)，首先A为条件的SQL

这次使用了全表扫描，窥探了绑定变量值是A。再使用以B为条件的SQL，

仍旧采用了全表扫描，绑定变量窥探值是A，因为只有第一次硬解析的时候才会窥探绑定变量值，接下来执行都会使用第一次窥探的绑定变量值。B的记录数只有1条，1/100001的选择率，显然索引范围扫描更合适。为了让SQL重新窥探绑定变量值，我们刷新共享池，

altersystem flush shared_pool;

此时清空了所有之前保存在共享池中的信息，包括执行计划，因此再次执行就会是硬解析，这次我们先使用B为条件，

可见窥探了绑定变量值是B，因为可以知道这个绑定变量:x的具体值，根据其值分布特点，选择了索引范围扫描。

再用A为查询条件，

此时仍旧窥探绑定变量值为B，因此还会选择索引范围扫描，即使A值应该选择全表扫描更高效。

总结来说，绑定变量窥探会于第一次硬解析的时候，“窥探“绑定变量的值，进而根据该值的信息，辅助选择更加准确的执行计划，就像上述示例中第一次执行A为条件的SQL，知道A值占比重接近全表数据量，因此选择了全表扫描。但若绑定变量列分布不均匀，则绑定变量窥探的副作用会很明显，第二次以后的每次执行，无论绑定变量列值是什么，都会仅使用第一次硬解析窥探的参数值，这就有可能选择错误的执行计划，就像上面这个实验中说明的，第二次使用B为条件的SQL，除非再次硬解析，否则这种情况不会改变。

简而言之，数据分布不均匀的列使用绑定变量，尤其在11g之前，受绑定变量窥探的影响，可能会造成一些特殊值作为检索条件选择错误的执行计划。11g的时候则推出了ACS(自适应游标)，缓解了这个问题。

虽然OLTP系统，建议高并发的SQL使用绑定变量，避免硬解析，可不是使用绑定变量就一定都好，尤其是11g之前，要充分了解绑定变量窥探副作用的原因，根据绑定变量列值真实分布情况，才能综合判断绑定变量的使用正确。

总结

本文首先描述了与SQL执行计划相关的案例，然后围绕案例介绍了相关的基础知识，11g之前使用绑定变量和非绑定变量在解析效率方面的区别，以及绑定变量在绑定变量窥探开启的情况下副作用的效果。

那么该SQL执行计划相关的案例如何处理，我们下期再展开讨论。