转载

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

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

上一篇文章介绍了绑定变量以及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，
一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法