sql bind capture相关文档

How To Create a Database SQL Trace Based On Capture Bind Variable For A Session Alone (文档 ID 434437.1)

Not Every Bind Values Captured in  v$sql_bind_capture (文档 ID 1370816.1)

Using  DBMS_XPLAN.DISPLAY_CURSOR to Identify Bind Values Used During Parse (文档 ID 416324.1)

Extract the Values of Bind Variables used by all the SQL Cursors in a Stored Procedure from the Data Dictionary (文档 ID 1351206.1)

http://tech.e2sn.com/oracle/troubleshooting/oracle-s-real-time-sql-monitoring-feature-v-sql_monitor

 

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.