执行计划 - Oracle谓词越界与绑定变量窥探

编辑手记:在SQL执行的过程中,选择不同的执行计划所产生的性能差异非常大,因此能够符合业务地选择正确的执行计划非常重要。但在真实环境中,总会受到一些因素的影响,今天我们来分析谓词越界和绑定变量窥探对SQL执行计划的影响。

案例场景

最近有一客户晚上新导入了一批数据到数据库中,第二天发现业务变慢,主要是其中有一条核心业务SQL执行计划走错导致。

结果排查发现客户在导入数据后并未重新收集统计信息,SQL使用绑定变量,窥探的变量刚好是越界,导致SQL第一次硬解析生成的执行计划走错。再加上10G的库导致接下来的执行计划直接沿用内存中的执行计划。

默认情况下直到SQL对应的share cursor被age out出了share pool才会重新解析,那么如果存在晚上大量跑批的应用,每天都会被刷出去,于是第二天业务运行都会存在执行计划极不稳定的情况,不过手工让SQL重新解析也有多种方法

导致问题的主要两个原因:

1、统计信息陈旧,谓词越界导致执行计划走错 2、10g绑定变量窥探的bug,导致之后所有的执行计划都走错 另外,如果字段数据倾斜,字段上有直方图信息,在10g里面也会由于绑定变量窥探从而使SQL大部分变量的执行计划走错。 首先在测试环境测试一下客户的场景:

创建测试表并初始化数据然后默认方式收集统计信息:

从上面可以看出id2的high value为100,且大部分数据都是id2=100,由于直方图中记录了数据的分布情况,在查询id2=100的SQL走索引快速全扫,另外由于199已经大于id2字段的high value,查询id2>199走的索引范围扫(结果=0):

模拟批量导入数据:

此时表中id2>199的数据已经有了大部分,但是由于统计信息未更新,谓词越界,再次查询大于199的SQL依旧走的索引范围扫:

现在模拟变量窥探的问题,首先查询id2大于100的数据:

再次查询id2>99的数据(此时查询表中绝大部分数据应该走索引快速全扫):

从上面的执行计划可以看出,即使未批量导入数据,SQL第二次执行直接使用第一次窥探id2>100解析生成的执行计划(Peeked Binds中可以看出),所以在导入大量数据之后性能的影响就会更大。指定no_invalidate=>false重新收集表的统计信息,再次执行SQL执行计划正确:

另外第一个等值查询的SQL如果使用绑定变量,如果第一次查询变量值id2=1,那么SQL会走索引范围扫,之后该SQL都会沿用这个执行计划,而大多数大多数情况下是查id2=100或在id2=200,理论上应该走索引快速全扫,而走了效率低的索引范围扫。 其次在字段统计信息中存在low_value/high_value两个字段,这个字段主要记录了列上的最大值和最小值,如果排除变量窥探和直方图的影响(也就是执行计划不变),在最大值和最小值区间SQL的cardinality是不变的,但是在变量值小于low_value或者大于high_value时,cardinality是会变化的,且偏移越远值越少:

这里将内存中的执行计划置为失效,这里方法有很多种,暂不做一一介绍:

从上面可以看出rows和bytes值都有差异,如果数据差异大,cost也会变化。也就是字段在没有直方图没有索引的情况下,为什么变量窥探出来的COST不一样。

这里需要注意的是,变量窥探一般情况下在select语句使用绑定变量都会去窥探,与字段上有无索引、直方图信息无关,虽然个人认为在没有直方图和索引的情况下意义不大,但是oracle都会去窥探变量值然后根据变量值生成执行计划,可以修改隐含参数"_optim_peek_user_binds"为FALSE禁用变量窥探(可能会引起性能问题),不过11g中引入自适应游标共享后这个问题得到了改善,在10g中直方图和变量窥探是相互矛盾的,为了性能的稳定性,需要人为去做好控制,不收集直方图信息或者不使用绑定变量,当然具体的方案都需要根据具体的情况进行分析测试。

最后需要注意的是默认情况下只收集在where条件中使用过的字段的直方图,视图sys.col_usage$中记录是否使用过不做任何查询或者DML收集统计信息:

执行带where条件的SQL,再次收集统计信息:

此时还是没有直方图,再次执行SQL,再次收集统计信息,发现字段上有了直方图信息,且name字段也没有直方图

下面执行where条件为name的SQL:

再只执行一次查询,执行两次收集统计信息就会收集直方图信息

也就是在执行一次查询SQL,然后收集两次统计信息后列上有了直方图信息,所以收集直方图与SQL的执行次数无关,第一次执行dbms_stats.gather_table_stats会将name的使用记录flush到SYS.COL_USAGE$中,然后再次收集就会判断这个列是否需要收集。

当然也可以手工指定method_opt参数直接对哪些列收集直方图,还可以指定for all column size repeat只对存在直方图的列收集直方图信息,

关于method_opt参数的说明可以参考官博:

How does the METHOD_OPT parameter work? (https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt ) 查询low_value/high_value脚本如下:

最后推荐大家阅读下老熊的两篇博客: 1、Oracle数据库升级迁移、SPA及统计信息 http://www.laoxiong.net/oracle-database-migration-upgrade-spa-statistics.html 2、怎样保持Oracle数据库SQL性能的稳定性 10g&11g中如何删除列上的直方图信息: How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future? 链接如下: https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-04-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

增量数据丢失的原因分析(r8笔记第26天)

今天开发的同事找到我,让我帮他们补一部分数据,因为有一个表的数据已经快一个月没有增量数据了,这个需求听起来有些奇怪是不? 问题的背景是在统计库中存在一个表,供部...

2988
来自专栏腾讯大数据的专栏

Storm上的实时统计利器-easycount

背景 Storm是TRC(腾讯实时计算)平台的核心组件。与Hadoop不同,storm之上没有像hive,pig之类的解放应用开发人员效率的工具。开发原生的st...

2629
来自专栏Golang语言社区

Go语言实践:从新手入门到上线真实的小型服务所遇到的那些坑

摘要: Teamwork团队在去年写了近20万行Go代码,建造了一堆速度奇快的小型HTTP服务,本文列出了他们总结的9条经验教训。 为什么选择Go语言?Go...

3237
来自专栏Java帮帮-微信公众号-技术文章全总结

学Java-Spring使用Quartz任务调度定时器

睁开眼看一看窗外的阳光,伸一个懒腰,拿起放在床一旁的水白开水,甜甜的味道,晃着尾巴东张西望的猫猫,在窗台上舞蹈。你向生活微笑,生活也向你微笑。 请你不要询问我...

2643
来自专栏用户画像

3.1.5 内存管理

在单道批处理系统阶段,一个系统一个时间段内只执行一个程序,内存的分配及其简单,仅分配给当前运行进程即可。而引入了多道程序并发执行之后,进程之间共享的不仅仅是处理...

643
来自专栏CSDN技术头条

Go语言实践:从新手入门到上线真实的小型服务所遇到的那些坑

Teamwork团队在去年写了近20万行Go代码,建造了一堆速度奇快的小型HTTP服务,本文列出了他们总结的9条经验教训。 为什么选择Go语言?Go语言,又称G...

1897
来自专栏Golang语言社区

Go语言实践:从新手入门到上线真实的小型服务所遇到的那些坑

摘要: Teamwork团队在去年写了近20万行Go代码,建造了一堆速度奇快的小型HTTP服务,本文列出了他们总结的9条经验教训。 为什么选择Go语言?Go...

2698
来自专栏FreeBuf

基于时延的盲道研究:受限环境下的内容回传信道

在一次漏洞赏金活动中,挖到个命令注入的洞,我先以时延作为证明向厂商提交该漏洞,厂商以国内网络环境差为由(的确得翻墙)拒收,几次沟通,告知若我能取回指定文件 se...

615
来自专栏数据和云

【循序渐进Oracle】Oracle段空间管理技术

在Oracle数据库内部,对象空间是以段的形式(Segment)存在和管理的,通过不同的段类型Oracle将段区分开来,在Oracle 9i中,主要的段类型有:...

2627
来自专栏Java帮帮-微信公众号-技术文章全总结

solr使用教程二【面试+工作】

6.3高亮显示 我们经常使用搜索引擎,比如在baidu 搜索 java ,会出现如下结果,结果中与关键字匹配的地方是红色显示与其他内容区别开来。 solr 默认...

3587

扫描关注云+社区