执行计划 - 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 条评论
登录 后参与评论

相关文章

来自专栏令仔很忙

UML总结

   描述类与类之间的使用与被使用关系,而其使用关系具有偶然性的、临时性的、非常弱的,但是被使用的一方会影响到使用的一方,比如说:“动物”和“氧气”,动物的生活...

551
来自专栏Renderbus云渲染农场

vray渲染速度慢的影响因素和提升技巧-Renderbus

模型因素 较为复杂的模型(特别是存在较多细小转角的模型),会耗费更多的渲染计算时间。模型的复杂程度对渲染的影响较大,这个问题可以说是“硬件伤”。

1694
来自专栏灯塔大数据

每周学点大数据 | No.12数据流中的频繁元素

No.12期 数据流中的频繁元素 Mr. 王:我们再来讲一个例子,数据流中的频繁元素。我们先来说说大数据的数据流模型。 小可:数据流,是流动的数据的意思吗?和...

2987
来自专栏张耀琦的专栏

OpenGL4.3 新特性: 计算着色器 Compute Shader

计算着色器是一个完全用于计算任意信息的着色器阶段( Stage ) 。虽然它可以渲染,但它通常用于与绘制三角形和像素无关的任务。

3130
来自专栏灯塔大数据

每周学点大数据 | No.42 Hash join

No.42期 Hash join Mr. 王:那我们就来看看 Hash join 具体是怎么做的吧。 两个表直接拿过来,我们不对其做任何排序和预处理。对这两...

2896
来自专栏周明礼的专栏

WebGL,真正进入三维的世界

WebGL真正强大的地方,在于它为我们提供了三维图像的绘制能力。当然这主要的得益于WebGL的计算速度,要知道,绘制三维图形,我们需要进行大量的(逐顶点甚至是逐...

3.3K1
来自专栏Spark学习技巧

Kylin及数据仓库的技术概念详解

一 cube 1, Table cube数据源的hive表的定义,在build cube之前需要进行同步。 2, Data Model 这描述了一个星型...

2348
来自专栏Brian

R语言性能Tips和GC

最近团队在使用R语言作为算法的实践语言,通过人工策略和xgboost算法进行一些价格算法的控制和输出,发现一些代码中对于内存、CPU、程序设计思想以及现代统计算...

640
来自专栏xingoo, 一个梦想做发明家的程序员

[翻译]CURAND Libaray--Host API--(2)

2.3 返回值 所有的CURAND host端的函数返回值都是curandStatus_t.如果调用没有错误,则返回成功,即返回值为CURAND_STATUS_...

18310
来自专栏Fish

《GPU高性能编程 CUDA实战》(CUDA By Example)读书笔记

写在最前 这本书是2011年出版的,按照计算机的发展速度来说已经算是上古书籍了,不过由于其简单易懂,仍旧被推荐为入门神书。先上封面: ? 由于书比较...

2555

扫码关注云+社区