在日常我们对OracleSQL优化过程中能够准确获取SQL的执行计划中真实的绑定变量值是很多分析工作的基石这样描述真的不过分。90% 以上的SQL性能问题差不多都需要结合实际参数值来判断这个执行计划是否合理同样一条SQL如果传的绑定变量值不同有可能会产生完全不同的执行路径并表现出不同的性性能来。不少DBA甚至是一些老炮DBA和开发一起排查慢SQL时经常会遇到“执行计划看起来没啥问题但咋就是实际执行的时候特别慢呢”。这种情况很有可能是因为没能拿到真实的绑定变量值导致分析的方向出现了问题。接下来我将以oracle内核的捕获机制作为出发点和大家聊聊生产环境中获取真实绑定变量值的实用方法也希望和大家交流希望大家能给出更好的建议。一、Oracle 绑定变量捕获的核心机制Oracle 对绑定变量值的捕获实际上是并非实时、无差别进行而是有其严苛的内部规则也是很多人容易忽略的关键点只有了解了这些规则才有可能避免查询时走弯路。1.1 触发捕获的两个必要条件Oracle 只会在以下两种场景会将绑定变量实际值写入系统视图硬解析阶段当含有绑定变量的 SQL 第一次执行或因为其它的各种原因如统计信息变更、DDL 操作等导致需要重新硬解析时Oracle 会自动捕获本次执行的所有绑定变量值。软解析 / 软软解析阶段当 SQL 已经存在共享池中以软解析或软软解析方式重复执行时Oracle 也有可能捕获绑定变量值但默认情况下两次捕获的最小时间间隔为 15 分钟。这个 15 分钟的间隔其实也是 Oracle 为了平衡性能和诊断需求而设计的 —— 如果每次执行都捕获绑定变量值这样也会给一些高并发系统带来巨大CPU和内存开销。1.2 一个极其重要的捕获限制Oracle 对绑定变量的捕获有一个非常特殊的限制这也是生产环境中很多DBA老炮容易踩坑的地方Oracle 永远不会捕获 INSERT 语句 VALUES 子句中的绑定变量值无论这条 INSERT 语句是硬解析还是软解析执行。这个限制是从 Oracle 10g 版本开始并延续到后面的很多版本。很多 DBA 在排查批量插入性能问题时花费大量时间查询 v$sql_bind_capture 视图却始终得不到任何结果正是这个原因。二、实时绑定变量值查询方法当目标 SQL 的游标还存在于共享池中时咱们可以通过v$sql_bind_capture视图查询最近捕获的绑定变量值。这也是是生产环境中最常用的实时查询方法。2.1 基础查询语句--- 查询指定SQL_ID的实时绑定变量值 SELECT sql_id, name AS bind_name, position AS bind_position, datatype_string AS data_type, value_string AS bind_value, TO_CHAR(last_captured, yyyy-mm-dd hh24:mi:ss) AS capture_time FROM v$sql_bind_capture WHERE sql_id target_sql_id ORDER BY position;2.2 查询结果解读sql_id目标 SQL 的唯一标识name绑定变量的名称如1、:2 或自定义名称position绑定变量在 SQL 中的位置datatype_string绑定变量的数据类型value_string捕获到的绑定变量值字符串形式last_captured最后一次捕获的时间戳2.3 常见问题排查如果查询v$sql_bind_capture没有返回结果通常可能有以下三种原因1目标 SQL 的游标已经被老化age out出共享池2目标 SQL 是 INSERT VALUES 语句受限于前面提到的捕获限制3距离上一次捕获的时间间隔不足 15 分钟且期间没有发生硬解析三、历史绑定变量值查询方法当目标 SQL 的游标已经被老化出共享池时我们需要查询 AWR中的历史数据。Oracle 会将捕获到的绑定变量值定期写入 AWR中保留时间由 AWR 保留策略决定通常默认是 8 天。3.1 通过 dba_hist_sqlbind 查询dba_hist_sqlbind视图存储了历史上所有被捕获的绑定变量信息也是查询历史数据的首选--- 查询指定SQL_ID的历史绑定变量值 SELECT snap_id, name AS bind_name, position AS bind_position, value_string AS bind_value, TO_CHAR(last_captured, yyyy-mm-dd hh24:mi:ss) AS capture_time, was_captured FROM dba_hist_sqlbind WHERE sql_id target_sql_id ORDER BY snap_id, position;3.2 通过 dba_hist_sqlstat 查询dba_hist_sqlstat视图的bind_data列以二进制格式存储了绑定变量数据可以使用dbms_sqltune.extract_bind函数来进行解析-- 解析dba_hist_sqlstat中的绑定变量数据 SELECT snap_id, dbms_sqltune.extract_bind(bind_data, 1).value_string AS bind_1, dbms_sqltune.extract_bind(bind_data, 2).value_string AS bind_2, dbms_sqltune.extract_bind(bind_data, 3).value_string AS bind_3 FROM dba_hist_sqlstat WHERE sql_id target_sql_id AND bind_data IS NOT NULL ORDER BY snap_id;注意AWR 数据默认每小时生成一次快照因此历史绑定变量值的时间精度为小时级。如果需要更精确的时间信息可以结合dba_hist_active_sess_history视图一起进行分析。四、INSERT 语句绑定变量值获取解决方案前面也提到了Oracle 不会捕获 INSERT VALUES 语句的绑定变量值这给批量插入性能问题的排查带来了很大困难。下面介绍一个小技巧可以绕过这个限制。4.1 核心技巧改写 INSERT 语句将标准的INSERT INTO table VALUES(...)语句改写为INSERT INTO table SELECT ... FROM dual WHERE ...的形式。这样绑定变量就出现在了 WHERE 子句中Oracle 就会正常捕获它们的值。改写示例--- 原语句无法捕获绑定变量 INSERT INTO t (id, name) VALUES (:n, :v); -- 改写后可以正常捕获绑定变量 INSERT INTO t (id, name) SELECT :n, :v FROM dual WHERE 11;4.2 效果验证通常可以通过以下步骤验证这个技巧的有效性1执行改写后的 INSERT 语句2查询 v$sqlarea 确认 SQL 已经执行3查询 v$sql_bind_capture 查看绑定变量值--- 执行改写后的INSERT语句 DECLARE n NUMBER(10); v VARCHAR2(128); BEGIN n : 1001; v : test_user_001; EXECUTE IMMEDIATE INSERT INTO t (id, name) SELECT :n, :v FROM dual WHERE 11 USING n, v; COMMIT; END; / -- 查看绑定变量捕获结果 SELECT sql_id, name, position, value_string, TO_CHAR(last_captured, yyyy-mm-dd hh24:mi:ss) AS capture_time FROM v$sql_bind_capture WHERE sql_id IN (SELECT sql_id FROM v$sqlarea WHERE sql_text LIKE INSERT INTO t (id, name) SELECT%);执行结果会显示Oracle 已经成功捕获到了:n1001 和:vtest_user_001 这两个绑定变量值。4.3 性能影响评估很多人会担心这种改写会带来性能开销。实际上SELECT ... FROM dual WHERE 11是一个非常轻量级的操作Oracle 优化器会自动将其优化为常量表达式不会产生任何实际的表扫描或 IO 操作。通常在生产环境的高并发批量插入场景中这样改写的性能损耗可以忽略不计。五、高级技巧与特殊场景处理5.1 修改绑定变量捕获间隔如果需要更频繁地捕获绑定变量值可以通过修改隐藏参数_cursor_bind_capture_interval来调整捕获间隔单位为秒。默认值是 900 秒。--- 查看当前捕获间隔 SELECT ksppinm AS parameter_name, ksppstvl AS parameter_value FROM x$ksppi JOIN x$ksppcv ON x$ksppi.indx x$ksppcv.indx WHERE ksppinm _cursor_bind_capture_interval; -- 修改捕获间隔为5分钟300秒 ALTER SYSTEM SET _cursor_bind_capture_interval 300 SCOPEBOTH;风险提示减小捕获间隔会增加系统的 CPU 和内存开销。在高并发系统中建议不要将间隔设置得小于 300 秒。修改前务必在测试环境进行充分验证。5.2 使用 10046 事件获取绑定变量值当上述方法都无法获取到绑定变量值时还可以使用 Oracle 的 10046 事件进行 SQL 跟踪。这是底层可靠的方法可以捕获到 SQL 执行时的所有绑定变量值不受任何捕获规则的限制。--- 开启当前会话的10046跟踪级别12包含绑定变量和等待事件 ALTER SESSION SET EVENTS 10046 trace name context forever, level 12; -- 执行需要跟踪的SQL语句 ... --- 关闭跟踪 ALTER SESSION SET EVENTS 10046 trace name context off; --- 查找跟踪文件 SELECT value FROM v$diag_info WHERE name Default Trace File;生成的跟踪文件中会包含完整的绑定变量值信息可以使用 tkprof 或其他工具进行解析。六、常见误区与最佳实践6.1 常见误区1误区一认为 v$sql_bind_capture 会捕获所有执行的绑定变量值实际上它只捕获硬解析时和每隔 15 分钟软解析时的变量值2误区二花费大量时间查询 INSERT VALUES 语句的绑定变量值记住 Oracle 永远不会捕获这类语句的 VALUES 子句绑定变量3误区三随意修改_cursor_bind_capture_interval 参数过小的间隔会给系统带来不必要的性能压力6.2 最佳实践1优先使用 v$sql_bind_capture对于实时问题这是最快、最方便的方法2合理利用 AWR 历史数据对于已经发生的问题AWR 是最主要的数据源3规范 SQL 编写对于需要排查性能问题的 INSERT 语句尽量使用 INSERT SELECT 的形式4谨慎修改隐藏参数除非万不得已否则不要修改_cursor_bind_capture_interval 参数5结合多种方法当一种方法无法获取数据时尝试使用其他方法如 10046 事件七、总结想要获取 Oracle SQL 的绑定变量值是性能优化工作中不可或缺的一环。上面也讲解了 Oracle 绑定变量的捕获机制以及实时查询、历史查询、INSERT 语句特殊处理等多种实用方法。但是在实际工作中咱们还是需要根据不同的场景选择合适的方法对于实时问题优先使用v$sql_bind_capture视图对于历史问题使用dba_hist_sqlbind和dba_hist_sqlstat视图对于 INSERT 语句使用改写为 INSERT SELECT 的技巧对于特殊场景使用 10046 事件进行跟踪掌握这些方法能够帮助我们快速准确地定位 SQL 性能问题能够提升我们的运维效率。