一个执行计划异常变更的案例 – 外传之查看绑定变量值的几种方法

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个执行计划异常变更的案例 – 外传之查看绑定变量值的几种方法

这篇外传之前有这么几篇文章:
《一个执行计划异常变更的案例 - 前传》
《一个执行计划异常变更的案例 - 外传之绑定变量窥探》

上一篇文章介绍了绑定变量以及11g之前绑定变量窥探的影响,这篇文章会介绍几种查看绑定变量值的方法。

上篇文章我们说了,绑定变量实际是一些占位符,可以让仅查询条件不同的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列只是来告诉你绑定变量列是字符型,还是数值型。
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法
我们此时换一下绑定变量值,发现vsql_bind_capture信息未变,dbsnake的书中曾说过当SQL执行硬解析时绑定变量值被捕获,并可从视图v$sql_bind_capture中查询。
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法
对于执行软解析/软软解析的SQL,默认情况下间隔15分钟才能被捕获,为了避免频繁捕获绑定变量值带来的系统性能开销,而且从常理上认为,既然使用了绑定变量,最佳方式就是值分布均匀,只需要SQL执行第一次硬解析时窥探一下,后续执行的SQL执行计划应该比较稳定,因此只要能比较实时地查看第一次绑定变量值即可。间隔15分钟受隐藏参数_cursor_bind_capture_interval控制,默认值是900s,15分钟。
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法
我们尝试将捕获绑定变量的间隔时间调短,该参数不支持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,datatype from 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 from dba_hist_sqlstat where sql_id = '...'order by snap_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 Snapshot Generation (文档 ID 554831.1)》,用其中的方法修改下AWR采集topnsql参数,
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法
默认值是
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法
含义是,
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法
此时重新执行SQL,默认AWR会一小时采集一次,此时可以手工采集AWR快照,
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法
此时再次查询DBA_HIST_SQLBIND,
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法
再次查询DBA_HIST_SQLSTAT,
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法

方法四:EM?
本以为任何可以使用SQL执行,都可以从EM中找到相应的界面,但未找出绑定变量值可以检索的地方,若是有朋友知道,可以告诉我们。
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法

一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法

总结
绑定变量值可以使用很多方法获取,这里只是列举了三种最常见的方法,我从网上看到有朋友还有用wrh_stat、v$sql等视图查询的例子,没有深究,我觉得碰见问题时,可以快速使用一些常用的方法解决问题就可以了,当然时间充裕的话,建议还是多从原理层了解一些,做到触类旁通则最好。

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个执行计划异常变更的案例 – 外传之查看绑定变量值的几种方法


 上一篇
NLS_LENGTH_SEMANTICS参数引申的问题 NLS_LENGTH_SEMANTICS参数引申的问题
由于某项目的特殊性,分布式开发环境,项目初期,对应的开发数据库环境有两套,两边都可能对表结构进行一些修改,因此写了一个工具,比对两边的结构元数据,其中碰到一个问题,很细微,但确实值得注意,在此记录下。 问题: 比对两个环境中同一张表的同一个
下一篇 
一个执行计划异常变更的案例 – 外传之聚簇因子(Clustering Factor) 一个执行计划异常变更的案例 – 外传之聚簇因子(Clustering Factor)
之前的几篇文章:《一个执行计划异常变更的案例 - 前传》《一个执行计划异常变更的案例 - 外传之绑定变量窥探》《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》《一个执行计划异常变更的案例 - 外传之rolling inva