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

相关文章

来自专栏大数据风控

R文本挖掘-中文分词Rwordseg

我们的数据分析工作,不仅仅有对数据的分析,还有对文字资料整合的统计分析。在进行词频统计之前,有一项必须要做的工作就是中文的分词。 语料库的处理 语料库 语料...

2756
来自专栏技术专栏

Spark SQL/Hive调优

任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。 ...

3312
来自专栏别先生

使用kettle来根据时间戳或者批次号来批量导入数据,达到增量的效果。

1、Kettle是一款国外开源的ETL工具,纯java编写,可以在Window、Linux、Unix上运行,数据抽取高效稳定。下载图形化界面的zip包格式的,直...

1231
来自专栏mukekeheart的iOS之旅

MySQL学习笔记(一)

一、MySQL基础知识 MySQL 是一个真正的多用户、多线程 SQL 数据库服务器。 SQL(结构化查询语言)是世界上最流行的和标准化的数据库语言。MySQL...

2398
来自专栏架构师之路

业界难题-“跨库分页”的四种方案

一、需求缘起 分页需求 互联网很多业务都有分页拉取数据的需求,例如: (1)微信消息过多时,拉取第N页消息 (2)京东下单过多时,拉取第N页订单 (3)浏览58...

5894
来自专栏Python中文社区

Python量子力学计算模拟以及数据可视化

專 欄 ❈Pytlab,Python 中文社区专栏作者。主要从事科学计算与高性能计算领域的应用,主要语言为Python,C,C++。熟悉数值算法(最优化方法,...

6499
来自专栏数据和云

拨开迷雾:关于resize datafile理解的错误

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ resize数据文件的时候,常常会报ORA-03297错误,也许很多...

3324
来自专栏颇忒脱的技术博客

面向程序员的网络基本知识 - 子网分割

本系列文章旨在向程序员分享一些网络基本知识,让程序员具备基本的网络常识,以便与网络工程师沟通。本系列文章不会涉及如何组建网络、如何配置交换机/路由器等硬件相关的...

1063
来自专栏云计算教程系列

如何在Ubuntu 16.04上使用MySQL全文搜索提高搜索效果

全文搜索(FTS)是搜索引擎用于在数据库中查找结果的技术。您可以使用它来为商店、搜索引擎、报纸等网站上的搜索结果提供支持。

944
来自专栏哈雷彗星撞地球

Objective-C 中如何测量代码的效率背景

因此,我们不可避免的要用到一些方法来计算代码的执行效率。计算代码的执行效率可以使用的API有:

945

扫码关注云+社区