Find bind variable value of a SQL that is currently executing (Doc ID 1905278.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
GOAL
Purpose of this document is to demonstrate how you can find the bind variable values of a SQL that is currently executing.
SOLUTION
There are two reliable ways to get the bind variable values of a SQL that is currently executing.
1. SQL Monitoring (V$SQL_MONITOR.BINDS_XML)
2. Errorstack Trace
We will look at both of these methods in details.
SQL Monitoring (V$SQL_MONITOR.BINDS_XML)
SQL Monitoring was introduced in 11.1 but the column BIND_XML was added only in 11.2
We run a query with bind variables that takes little longer to complete.
SQL> VAR BIND1 NUMBER;
SQL> EXEC :BIND1:=999;
SQL> select count(*) from DBA_OBJECTS A,DBA_OBJECTS B WHERE A.OBJECT_ID>:BIND1;
While this SQL is still running, we now query v$sql_monitor from a different session.
SQL> select xmltype(binds_xml) from v$sql_monitor where sid =74 and status = 'EXECUTING';
XMLTYPE(BINDS_XML)
------------------------
<binds>
<bind name=":BIND1" pos="1" dty="2" dtystr="NUMBER" maxlen="22" len="3">999</bind>
</binds>
<binds>
<bind name=":BIND1" pos="1" dty="2" dtystr="NUMBER" maxlen="22" len="3">999</bind>
</binds>
Note that SQL Monitoring kicks in for SQL statements that run for 5 seconds or more.
ERRORSTACK
Errorstack trace has lot of information in it. In this document, we will focus on the bind variable section of the trace file alone.
This is how you can take an errorstack trace. 12280 is the OS Process ID of the session that runs the SELECT.
connect / as sysdba
oradebug setospid 12280
oradebug unlimit
oradebug dump errorstack 3
oradebug tracefile_name /* This tells you the file name */
exit
Header section of the trace file shows you the the Current SQL statement text and its SQL ID.
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=9z7qgrmf5at7b) -----
select count(*) from DBA_OBJECTS A,DBA_OBJECTS B WHERE A.OBJECT_ID>:BIND1
...
....
Now search the trace file with the keyword "Dump Cursor sql_id=9z7qgrmf5at7b". Scroll down a bit and you will see the bind information.
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b479dde1cc8 bln=22 avl=03 flg=05
value=999
The bind value would be the value seen in "value=" variable. Also note that "oacdty=" would mean the datatype of the bind. Some of the datatype identifiers are
01 - Char
02 - Number
08 - Long
09 - Varchar
12 - Date
112- CLOB
113- BLOB
114- BFILE
