暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

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

章宇文 2017-04-20
739

编辑手记:在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


加入"云和恩墨大讲堂"微信群,参与讨论学习

搜索 盖国强(Eygle)微信号:eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。


关注 ,获得后续精彩分享

文章转载自章宇文,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论