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
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