Home » Oracle » Oracle Database » How to find the bind variable of the sql id

How to find the bind variable of the sql id

Sometimes a SQL_ID might show a different plan because of a particular value of the bind variable. So we would like to find out the bind variable. Also, we might want to know the bind variable for the sql_id being executed by the session. In this post, we will check out How to find the bind variable of the SQL ID

How to find the bind variable of the sql id

We can find the bind variable using the below sql statement

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
SELECT sql_id, t.sql_text sql_text, b.name bind_name, b.value_string bind_value FROM 
v$sql t JOIN v$sql_bind_capture b 
using (sql_id) WHERE b.value_string is not null AND sql_id='&sqlid' 
/

or

SELECT name, position, datatype_string, value_string
FROM v$sql_bind_capture
WHERE sql_id = '&1‘;

We can also find the value of the bind variable from sql monitoring report also

SET LONG 1000000 
SET LONGCHUNKSIZE 1000000 
SET LINESIZE 1000 
SET PAGESIZE 0 
SET TRIM ON 
SET TRIMSPOOL ON 
SET ECHO OFF 
SET FEEDBACK OFF 
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&1', type => 'TEXT') AS report FROM dual;

or If you know the sid of the session executing it

select xmltype(binds_xml) from v$sql_monitor where sid =&1 and status = 'EXECUTING';

How to find the Bind Variable using Errorstack

connect / as sysdba
oradebug setospid 12280
oradebug unlimit
oradebug dump errorstack 3
oradebug tracefile_name /* This tells you the file name  */
exit
  • The header section of the trace file shows you the Current SQL statement text and its SQL ID.
  • Now search the trace file with the keyword “Dump Cursor sql_id=”. Scroll down a bit and you will see the bind information.
  • The BIND value would be the value seen in the “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

How to extract Bind Variable for all the sql queries in a procedure

The table v$sql contains two columns program_id & program_line#, that provide information on the ‘Store Procedure’ name and and line number that issued the SQL statement. A join on v$sql and dba_objects gives the list of SQL IDs of the queries in a stored procedure. These can be further joined with V$SQL_BIND_CAPTURE to query the values of the bind variable

select s.sql_text, nvl(value_string,'null'), o.owner, o.object_name, s.program_line# from v$sql s, dba_objects o, v$sql_bind_capture sbc where o.object_id = s.program_id and sbc.sql_id = s.sql_id and o.object_name like '<Procedure_name>';
  • It is important to know the bind variable as it helps in tuning the sql id.
  • I hope you like this short post on How to find the bind variable of the SQL ID
See also  How to use Virtual Index in Oracle Database

Related Articles
Bind Variable in Oracle: Bind variables are the placeholder for values in sqlplus and PLSQL and it is replaced with values when the statement is executed
explain plan in Oracle: All about Explain Plan in Oracle, How to read oracle explain Plan, how to find the explain plan for a query in cursor
Sql plan Management in Oracle: check out this post on Sql plan Management in Oracle, how it works out, what are the benefits and how it helps in performance tuning
SQL Tuning Set in Oracle
How to implement custom Sql Profile in Oracle: check out this post on How to implement a custom Sql Profile in Oracle with details steps, How to find all plan_hash_value of the sql_id
Histograms in Oracle: check out What is a histogram in Oracle, types of histograms, and How Oracle decides about the types of Histograms

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top