sql bind capture相关文档

本文介绍在Oracle 11.2及更高版本中如何利用实时SQL监控特性(V$SQL_MONITOR)来查看正在执行的SQL语句的绑定变量值,这对于解决性能问题非常有用。文中还提供了通过SQL查询直接获取这些值的例子。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

Oracle's Real-Time SQL Monitoring feature (V$SQL_MONITOR)


Read bind variable values of currently executing SQL

If you are running on Oracle 11.2+ and have the licenses for Oracle Diagnostics & Tuning Packs and when Oracle's SQL monitoring feature actually kicks in, then you can use V$SQL_MONITOR to view the bind variable values of currently executing SQL. Normally the bind variable values live in the private memory (PGA) of a process, but when SQL Monitoring kicks in for a statement in Oracle 11.2, it will copy the current bind variable values to SGA (V$SQL_MONITOR), so these will be readable by other users (like you, who's trying to troubleshoot a performance problem) as well.

A little example, executed on Oracle 11.2.0.1 is here. I set up couple of bind variables and then run a query which is designed to take very long time to complete:

SQL> VAR ename VARCHAR2(100)
SQL> VAR deptno NUMBER
SQL> 
SQL> EXEC :ename:= 'TANEL'

PL/SQL procedure successfully completed.

SQL> EXEC :deptno:= 1234567890;

PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedit.sql

  1  SELECT
  2      COUNT(*)
  3  FROM
  4      scott.emp e
  5    , scott.dept d
  6    , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000000) x
  7    , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000000) y
  8  WHERE
  9      e.deptno = d.deptno
 10  AND d.deptno < :deptno
 11* AND e.ename != :ename
 12  /
 
Once the SQL monitoring kicks in (in about 5 seconds of CPU + IO wait time usage by default), the bind variable values will appear in V$SQL_MONITOR.BIND_XML column for my session:

SQL> select xmltype(binds_xml) from v$sql_monitor where sid = 26 and status = 'EXECUTING';

XMLTYPE(BINDS_XML)
-----------------------------------------------------------------------------------------------------------
<binds>
  <bind name=":DEPTNO" pos="1" dty="2" dtystr="NUMBER" maxlen="22" len="6">1234567890</bind>
  <bind name=":ENAME" pos="2" dty="1" dtystr="VARCHAR2(2000)" maxlen="2000" csid="873" len="5">TANEL</bind>
</binds>

Here you go, in addition to current bind variable values I even see the bind variable data types (and their max lengths) which can be useful when troubleshooting some types or problems (like cursor sharing or implicit datatype conversion related problems).

Note that there's an undocumented parameter _sqlmon_binds_xml_format which controls whether the bind values are shown in human-readable format or a hex-dump in the BINDS_XML column:

SQL> @pd _sqlmon_binds_xml_format
Show all parameters and session values from x$ksppi/x$ksppcv...

NAME                         VALUE         DESCRIPTION
---------------------------- ------------- -------------------------------------------------------
_sqlmon_binds_xml_format     default       format of column binds_xml in [G]V$SQL_MONITOR

SQL> @pvalid  _sqlmon_binds_xml_format
Display valid values for multioption parameters matching "sqlmon"...

  PAR# PARAMETER                                                 ORD VALUE                          DEFAULT
------ -------------------------------------------------- ---------- ------------------------------ -------
  2227 _sqlmon_binds_xml_format                                    1 DEFAULT
       _sqlmon_binds_xml_format                                    2 HEXDUMP


So, if you are not running on Oracle 11.2 or you don't have Diag+Tuning pack licenses  or if the SQL Monitoring just doesn't kick in due to some bug, then what to do?

Well then you can go back to the old-fashioned way of connecting to the target process via ORADEBUG, issuing an ERRORSTACK level 3 dump and read the contents of the tracefile. I have explained it here.


 

 

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值