一个执行计划异常变更的案例 – 前传

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个执行计划异常变更的案例 – 前传

今天快下班的时候,几位兄弟来聊一个问题,大致是昨天应用使用的数据库突然出现性能问题,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会扫描表高水位线以下的数据块,且为多块读,即一次IO会读取多个数据块,具体数据块数量取决于参数db_file_multiblock_read_count,而INDEX RANGE SCAN则是单块读,同时若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的书中有一个案例)等,有机会做一些实验,再呈现出来。

至于本文开始提到的这个问题,进一步了解相关信息后,可以详细地介绍下。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!

一个执行计划异常变更的案例 - 前传
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个执行计划异常变更的案例 – 前传


 上一篇
Oracle服务端和客户端版本补丁不一致造成Tuxedo应用程序出core案例 Oracle服务端和客户端版本补丁不一致造成Tuxedo应用程序出core案例
这是一个CU*系统调用的查询天气预报的Tuxedo服务,Oracle ProC程序,连接的是Oracle 10.2.0.4库,出现问题的现象是不定时的产生core,服务down,触发Tuxedo服务的自动重启机制,但一般MAXGEN配置是1
下一篇 
一个执行计划异常变更的案例 – 外传之绑定变量窥探 一个执行计划异常变更的案例 – 外传之绑定变量窥探
上一篇文章《一个执行计划异常变更的案例 - 前传》,介绍了一次执行计划异常变更的案例现象,这两天经过运行同事,以及罗大师的介绍,基本了解了其中的原因和处理方法,这个案例其实比较典型,涉及的知识点很多,有数据库新特性,有SQL相关的,还有应用