【错综复杂】一个执行计划异常变更的案例(中)

前文回顾:

一个执行计划异常变更的案例(上)

上篇文章我们说了,绑定变量实际是一些占位符,可以让仅查询条件不同的SQL语句可以重用解析树和执行计划,避免硬解析。绑定变量窥探则是第一次执行SQL硬解析时,会窥探使用的绑定变量值,根据该值的分布特征,选择更合适的执行计划,

其缺点在于如果绑定变量列值分布不均匀,由于只有第一次硬解析才会窥探,所以可能接下来的SQL执行会选择错误的执行计划。

有时可能我们需要查看某条SQL使用了什么绑定变量值,导致执行计划未用我们认为最佳的一种。

方法一:10046

使用level=4的10046事件,查看生成的trace文件:

可以看出绑定变量值是’Z’。

方法二:v$sql_bind_capture

首先找出SQL对应的sql_id:

从v$sql_bind_capture可以看出两个绑定变量占位符以及对应的值

这里有一点值得注意的就是,DATATYPE_STRING列的描述是“绑定变量数据类型的文本表示”,开始我认为就是绑定变量字段的数据类型,但实际看来不是,DATATYPE_STRING列只是来告诉你绑定变量列是字符型,还是数值型。

我们此时换一下绑定变量值,发现v$sql_bind_capture信息未变,dbsnake的书中曾说过当SQL执行硬解析时绑定变量值被捕获,并可从视图v$sql_bind_capture中查询。

常理上认为,既然使用了绑定变量,在值分布均匀的情况下,只需要SQL执行第一次硬解析时窥探一下,后续执行的SQL执行计划应该比较稳定。对于执行软解析/软软解析的SQL,默认情况下间隔15分钟才能被捕获,为了避免频繁捕获绑定变量值带来的系统性能开销,只要能比较实时地查看第一次绑定变量值即可。间隔15分钟受隐藏参数_cursor_bind_capture_interval控制,默认值是900s。

我们尝试将捕获绑定变量的间隔时间调短,该参数不支持session级别修改,

执行alter system级别操作

等大约一分钟,此时可以从v$sql_bind_capture查询刚使用的绑定变量值,

方法三:AWR信息

(1) DBA_HIST_SQLBIND视图包含了v$sql_bind_capture的快照。

因此对应的SQL语句,和v$sql_bind_capture很像,

select name,datatype_string,value_string,datatypefrom DBA_HIST_SQLBIND where sql_id='...'

(2) 另一个视图,DBA_HIST_SQLSTAT记录了SQL统计信息的历史信息,他是基于一些标准,捕获来自于V$SQL的统计信息。可以使用如下SQL,

select snap_id, dbms_sqltune.extract_bind(bind_data,1).value_string bind1, dbms_sqltune.extract_bind(bind_data,2).value_string bind2, dbms_sqltune.extract_bind(bind_data,3).value_string bind3 fromdba_hist_sqlstat wheresql_id = '...' order bysnap_id;

其中dbms_sqltune.extract_bind(bind_data,1).value_string取决于SQL中绑定变量的数量。

第一次执行这两条SQL时,并未有任何结果返回,我猜测可能是这条SQL不符合AWR采集的标准。从MOS中查到这篇文章:《How to Control the Set of Top SQLs Captured During AWR SnapshotGeneration (文档 ID 554831.1)》,用其中的方法修改下AWR采集topnsql参数,

默认值是

含义是

此时重新执行SQL,默认AWR会一小时采集一次,此时可以手工采集AWR快照,

此时再次查询DBA_HIST_SQLBIND

再次查询DBA_HIST_SQLSTAT,

绑定变量值可以使用很多方法获取,这里只是列举了三种最常见的方法。

接下来我们通过实例分析一下:

有一条SQL,使用了绑定变量,查看V$SQLAREA发现version_count是2

查看V$SQL,发现有两条记录,分别对应了0和1两个child cursor:

再查看这两个child cursor对应的执行计划: child cursor:0

child cursor:1

发现除了成本代价略有不同,其他访问路径完全一致。应用保证使用的相同用户执行这条SQL语句,绑定变量窥探关闭。问题就来了,为何同一条SQL有两个child cursor,且执行计划一致?

再抛一下,通过V$SQL_SHARED_CURSOR视图可以查看游标失效的原因,对比这两个cursor,不同之一就是这个ROLL_INVALID_MISMATCH字段的值,0号cursor值为N,1号cursor值为Y,

另外,REASON字段,0号cursor显示了内容,1号cursor该字段值为空。

RollingInvalidate Window Exceeded(3)

这个问题通过Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (文档 ID 557661.1)这篇文章能够很好地解释。

大体意思是在10g之前,使用dbms_stats采集对象统计信息,除非no_invalidate设为TRUE,否则所有缓存在Library Cache中的游标都会失效,下次执行时需要做硬解析。

隐患:对于一个OLTP系统,会产生一次硬解析风暴,消耗大量的CPU、库缓存以及共享池latch的争用,进而影响应用系统的响应时间。

如果设置no_invalidate为FALSE,则现有存储的游标不会使用更新的对象统计信息,仍使用旧有执行计划,直到下次硬解析,要么因为时间太久,导致cursor被刷出,要么手工执行flush刷新了共享池,这两种情况下会重新执行硬解析,根据更新的对象统计信息,生成更新的执行计划。这么做其实还是有可能出现硬解析风暴,特别是OLTP系统,高并发时候,有SQL语句频繁访问。

使用dbms_stats.gather_XXX_stats的时候,有个参数no_invalidate,

默认是AUTO_INVALIDATE,这表示是由Oracle来决定什么时候让依赖的游标失效。10g之后,如果采集对象统计信息使用的no_invalidate参数是auto_invalidate,则Oracle会采用如下操作,来缓解可能的硬解析风暴。

1.执行dbms_stats,所有依赖于这个已分析对象的缓存cursor游标会被标记为rolling invalidation,并且记录此时刻是T0。 2.下次某个session需要解析这个标记为rollinginvalidation的cursor游标时,会设置一个时间戳,其取值为_optimizer_invalidation_period定义的最大值范围内的一个随机数。之所以是随机数,就是为了分散这些 invalidation的游标,防止出现硬解析风暴。参数_optimizer_invalidation_period默认值是18000秒,5小时。记录这次解析时间为T1,时间戳值为Tmax。但此时,仍是重用了已有游标,不会做硬解析,不会使用更新的统计信息来生成一个新的执行计划。 3.接下来这个游标(标记了rollinginvalidation和时间戳)的每次使用时,都会判断当前时刻T2是否超过了时间戳Tmax。如果未超过,则仍使用已存在的cursor。如果Tmax已经超过了,则会让此游标失效,创建一个新的版本(一个新的child cursor子游标),使用更新的执行计划,并且新的子游标会标记V$SQL_SHARED_CURSOR中ROLL_INVALID_MISMATCH的值。

这些和我上面碰见的情况基本一致。

MOS是附带了一个实验,可以根据实验来体会下这种情况。 1.为了容易观察,设置_optimizer_invalidation_period为1分钟,

2.创建测试表,并采集统计信息,

3.执行一次目标SQL,并查看V$SQL_SHARED_CURSOR信息,

此时查看这条SQL的解析和执行次数都是1

4.再执行一次目标SQL,

select count(*) from X;

查看这条SQL的解析和执行次数是2,

有人曾说过,11g中未必会按照_optimizer_invalidation_period参数定义的时间产生新的子游标,我上面用的环境是11g,确实如此,等了2分钟,执行目标SQL,仍只有一个子游标。这样的好处就是更加的随机。如果严格按照参数设置的时间失效,则有可能频繁使用的游标会在超时后某一时刻集中做硬解析,还是会有资源的影响,只是时间推迟了,因此如果是在超时值基础上又有随机分布,则可能会将硬解析的影响降到最低。

又等了一段时间,再查询V$SQL,

确实产生了两个子游标,这里需要注意FIRST_LOAD_TIME的时间是一样的,因为他是parent父游标的创建时间,显然这两个子游标肯定是对应同一个父游标,不同的就是LAST_LOAD_TIME,这是子游标的使用时间。

再看看V$SQL_SHARED_CURSOR,

两个子游标信息,只有一个R项值有差别,R是ROLL_INVALID_MISMATCH,0号子游标是N,1号子游标是Y,看看官方文档对这个字段的说明,

表示的就是标记为rolling invalidation的游标,已经是超过了时间窗口,此时0号子游标已经过期,1号子游标使用最新的统计信息,来生成最新的执行计划。

这就解释了为何同一条SQL,执行计划一致,但却有两个子游标的情况。

MOS中还描述了一些游标使用的场景

1.如果一个游标被标记为rolling invalidation,但是再不会做解析,则这个游标不会失效,最终还是可能根据LRU被刷出共享池。 2.如果一个游标被标记为rolling invalidation,后面只会解析一次,那么这个游标依然不会失效(仅仅使用时间戳标记),最终还是可能根据LRU被刷出共享池。 3.频繁使用的游标,在超过时间戳Tmax值后,下次解析时就会被置为失效。 很明显,上面的这些方法是有效的,因为失效标记仅仅适用于这些频繁重用的游标,对于其他场景的游标可以忽略,未有影响。

总结

1.凡事有因果,同一条SQL,执行计划相同,但产生了两个子游标,总会有其的原因,上面游标失效标记可能是一个原因,当然还有可能是其他原因。

2.对于FIRST_LOAD_TIME这些字段的理解,还是要准确些,不能断章取义。

该案例到此还没有结束,但希望你在读的过程中已经有了新的收获。

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

原文发表时间:2017-01-19

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏计算机技术翻译

ElasticMQ 0.7.0:使用Akka和Spray的长轮询,非阻塞实现

原文地址:https://dzone.com/articles/elasticmq-070-long-polling-non

1919
来自专栏大内老A

控制并发访问的三道屏障: WCF限流(Throttling)体系探秘[上篇]

WCF是一个基于多线程的消息监听、接收和处理框架体系,能够同时应付来自相同或者不同客户端的服务调用请求,并提供完善的同步机制确保状态的一致性。一方面,我们期望W...

1677
来自专栏逸鹏说道

c# 温故而知新: 线程篇(一) 上

Thread 目录: 目录: 1 线程基础的简单介绍 2 线程同步与线程异步的简单介绍 3 前台线程与后台线程的简单介绍 4 细说下Thread 最为关键的构造...

2448
来自专栏开源优测

JMeter逻辑控制器08

前言 在jmeter中逻辑控制器主要分类两类: 控制jmeter测试计划中节点的逻辑执行顺序等等 对jmeter的节点进行分组,方便结果统计等等 进一步简化下,...

3415
来自专栏大闲人柴毛毛

柴毛毛大话设计模式——开发常用的设计模式梳理

写在最前 本文是笔者的一点经验总结,主要介绍几种在Web开发中使用频率较高的设计模式。 本文篇幅较长,建议各位同学挑选感兴趣的设计模式阅读。 在阅读的同时,也...

4247
来自专栏架构师之旅

JVM内存管理------JAVA语言的内存管理概述

引言 内存管理一直是Java语言自豪与骄傲的资本,它让JAVA程序员基本上可以彻底忽略与内存管理相关的细节,只专注于业务逻辑。不过世界上不存在十全十...

1817
来自专栏Jed的技术阶梯

Spark性能调优05-Shuffle调优

在Spark的源码中,负责shuffle过程的执行、计算和处理的组件主要就是ShuffleManager,也即shuffle管理器。而随着Spark的版本的发展...

753
来自专栏数据和云

深入解析:由SQL解析失败看开发与DBA的性能之争

李华 云和恩墨高级技术顾问 以下案例来自大讲堂的一次分享,从这个案例中我们可以了解“错误的SQL”可能对数据库产生的种种影响。如何找到这些错误的、解析失败的S...

2715
来自专栏Java Edge

大道缓存1 缓存特征2 缓存介质3 缓存分类和应用场景缓存实战

用户请求从界面(浏览器/App)到网络转发、应用服务再到存储(数据库或文件系统),然后返回到界面呈现内容。

301
来自专栏数据和云

【重磅推荐】从Library Cache等待事件深入剖析SQL解析

编辑手记:在很多生产系统中,程序员经意不经意写下的一条SQL都可能带来性能上的巨大隐患,而DBA就要不断在这些问题中出生入死,这些不正确的SQL可能给我们带来哪...

2724

扫描关注云+社区