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

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

上一篇文章介绍了绑定变量以及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中找到相应的界面,但未找出绑定变量值可以检索的地方,若是有朋友知道,可以告诉我们。
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法

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

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

上一篇:内核必须懂(一): 用系统调用打印Hello, world!


下一篇:JavaScript var关键字、变量的状态、异常处理、命名规范等介绍