_optim_peek_user_binds导致的性能问题

0    59    1

👉 本文共约2865个字,系统预计阅读时间或需11分钟。

案情:

客户新业务上线(数据库版本为 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表后,在收集统计信息前又重新插回来):

img

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

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

img

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

表字段统计信息:

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

表上的索引,谓词条件的两个字段是索引的前两个字段:

img

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

img

某几个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关闭自适应游标特性,还是一样会发生同样的问题。

建议:

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

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

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

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

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

关于上一期的优化方法,老虎刘的方法是:

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

SQL的问题是:

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

参考

参考:老虎刘谈SQL优化

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

20 + 17 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部