疑案重审，真相大白:_optim_peek_user_binds是惯犯

案情：

客户新业务上线（数据库版本为 10.2.0.5）一段时间后，系统CPU都在20%左右。19日，系统CPU使用率突然接近100%，交易处理速度也严重下降。20日凌晨1:15分之后，系统CPU突然又降到20%左右，交易速度也有大幅提高（只有几毫秒）；

21日凌晨2点左右又变慢，直到22日下午对相关表做truncate后，应用也做了代码调整（处理完的交易记录马上删除，表始终保持较小的状态），系统才恢复稳定运行。

对于20日凌晨系统性能突然变好的问题，某服务公司已经给出一份报告，怀疑是当时开发人员对业务相关表做了truncate操作（大表数据量小了，消耗的资源就少），然而开发人员表示没有做任何操作。到底研发人员有没有做truncate表操作？事情的根本原因是什么？客户需要对这个问题重新分析，给出明确的结论。

根据当时收集的信息，19日的 CPU使用率高主要是由下面的SQL引起：

select CCYGRP ,QUTSEQ ,SELRAT ,BUYRAT ,BOKRAT

from FXTTRT

where (CCYGRP=:b0 and DELTYP='R')

order by QUTSEQ;

使用的是全表扫描的执行计划。在业务繁忙时段，有很多并发进程在执行这个个SQL，FXTTRT表有100多万条记录，而且还在不断增加，CPU使用率高是预期行为。

根据sqlhc工具收集的信息，下面FXTTRT表的统计信息的历史数据可以说明，17日到23日，表的数据在持续增长，20日发生truncate表操作的可能性非常小（除非先对表做了备份，truncate表后，在收集统计信息前又重新插回来）：

结合研发人员声称没有做truncate操作，上面信息已经基本推翻了原报告怀疑FXTTRT表被truncate的结论（可以看出16日前做过一次truncate操作）。

因为AWR在当前系统只保留一周，4月1日案情重审时已经找不到19日和20日的执行计划，只有一些当时分析问题时收集的SQL执行效率的数据，红框内20日的SQL执行效率高前后两天19日和21日好多2倍：

根据性能数据的巨大差别可以推断，如果当时表没有做truncate，一定是执行计划发生了改变，使用了更好的执行计划。我们来看看是否有更好执行计划的可能：

表字段统计信息：

因为两个谓词条件使用的字段唯一值较小，所以第一次的分析认为走全表扫描就是最好的执行计划（如果字段上的数据分布均匀，唯一值这么小的字段确实不适合使用索引）。

表上的索引，谓词条件的两个字段是索引的前两个字段：

再看看表上两个字段的实际数据分布情况：

某几个CCYGRP字段的值，对应的记录数是1，那就说明，在使用某些绑定变量的时候，确实是可以使用表上的索引。

检查参数_optim_peek_user_binds=TRUE，即绑定变量窥视是默认的开启状态。

通过以上信息可以判断，SQL在使用某些绑定变量时（返回的结果很少），是可以使用索引的，20号全天的性能都很好，CPU使用率低，说明使用的绑定变量在大部分情况下都是返回较少的结果，适合使用索引。

SQL执行计划为什么会在20日凌晨自动变好，21日凌晨又自动变差呢？接着分析：

_optim_peek_user_binds=TRUE时，执行计划改变一般发生在硬解析时，硬解析时“窥视”到的绑定变量如果适合使用全表扫描，那么在下一次硬解析前，都会使用全表扫描这个执行计划；如果硬解析时“窥视”到的绑定变量适合使用索引，那么在下一次硬解析前，都会使用索引这个执行计划。而硬解析一般发生在统计信息收集之后。

19日22:01，FXTTRT表收集了统计信息，收集统计信息的参数NO_INVALIDATE默认为AUTO_INVALIDATE，这个值为18000（秒），即收集统计信息后的5小时内，会将当前的执行计划（游标）失效，重新对SQL进行硬解析，生成新的执行计划。这个时间点也与20日凌晨性能1时候突然变好相吻合（21日凌晨系统性能又变差也是在这个时间点）。

综上所述，可以推断出是执行计划改变使20日凌晨CPU使用率突降，业务量及处理效率增加。效率低的时候使用的是全表扫描的执行计划，效率高的时候使用的是索引的执行计划。彻底推翻了一审做出的表被truncate的误判。

后来我们也找到了该系统备份的历史AWR数据，找到了20日当天的实际执行计划，确实是发生了改变，由性能差的全表扫描，变成了性能好的索引扫描。为上述推断提供了铁证。

总结：

这种情况在oracle 10g及以下版本比较多见，绑定变量窥视的特性有时会导致一些性能问题，尤其是在数据存在严重倾斜的表字段上使用绑定变量，对于绑定变量使用的不同值，可能生成多个执行计划的情况。

如果SQL硬解析生成执行计划时“窥视”到的绑定变量刚好只适合少部分数据，不适合其他大多数数据，那么直到下一次硬解析前（一般是收集统计信息后才会触发硬解析），都会使用这个不优的执行计划。

很多系统为了保持性能稳定，会将“绑定变量窥视”功能关闭，即设置_optim_peek_user_binds=false。这种情况下，如果是上面的SQL，生成的执行计划将一直是全表扫描，因为不能窥视到具体的变量值，只能按照字段的数据是均匀分布来计算（即使存在直方图信息），在字段唯一值少的情况下，是不会使用索引的。这样虽然执行计划稳定了，但性能降低了。

如果数据库升级到了11g的版本，增加了一个叫“自适应游标”（简称ACS）的特性，可以针对不同的绑定变量，使用不同的执行计划，避免此类问题的发生。但是如果在11g关闭自适应游标特性，还是一样会发生同样的问题。

建议：

对于存在数据倾斜（分布不均）、唯一值少的字段，一般不建议使用绑定变量，使用常量是最佳选择。因为唯一值少，不会发生大量硬解析导致性能问题。这需要研发人员在开发阶段就做好这方面的工作。

如果sql代码已经在数据倾斜字段使用了绑定变量，如何解决此类问题？

建议使用sql profile固定执行计划。不管绑定变量窥视参数是否开启，SQL执行计划始终保持一致。

固定的执行计划是经常使用的绑定变量值，虽然对某些绑定变量的值效率不佳，但保证了大部分情况执行计划是好的。

关于上一期的优化方法，老虎刘的方法是：

创建operateuserid,PutoutDate,businessType 3个字段上的联合索引，做索引覆盖，避免回表操作。因为count(serialNo)中的serialNo是主键，为非空，索引覆盖不需要加这个字段，如果是可为空的字段，则索引覆盖也要增加这个字段。

SQL的问题是：

使用的绑定变量:3和:4，not like 后面的字符串没有加%，是没有意义的，有可能是研发人员的笔误。