Friday, December 3, 2010

10g: Use V$SQL_BIND_CAPTURE to find bind variables values


In your database, you probably see a lot of queries running using bind variables,
like :B1 in the following script, and you don't know its value:
DELETE FROM ASO_PRICE_ADJ_ATTRIBS WHERE PRICE_ADJUSTMENT_ID = :B1

If you need to find the value of this variable, first, query V$SQL to find the SQL_ID:
SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE 'DELETE FROM ASO_PRICE_ADJ_ATTRIBS%';

SQL_IDSQL_TEXT
5tya0jzb74tu9DELETE FROM ASO_PRICE_ADJ_ATTRIBS WHERE PRICE_ADJUSTMENT_ID = :B1

Then, use the SQL_ID in V$SQL_BIND_CAPTURE to find the value:
SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED
FROM V$SQL_BIND_CAPTURE
WHERE SQL_ID='5tya0jzb74tu9';

NAMEVALUE_STRINGDATATYPE_STRINGLAST_CAPTURED
:B115499478NUMBER03/12/2010 03:15:39 πμ
:B115075864NUMBER19/11/2010 02:31:16 πμ
:B115148907NUMBER20/11/2010 02:31:50 πμ
:B114251241NUMBER15/10/2010 03:45:30 μμ

You can see that at various points in time the query is used with different :B1 values.
From the LAST_CAPTURED column you can decide which value is the one you are interested.

Of course, you may combine these queries:
SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED
FROM V$SQL_BIND_CAPTURE A, V$SQL B
WHERE A.SQL_ID = B.SQL_ID
AND SQL_TEXT LIKE 'DELETE FROM ASO_PRICE_ADJ_ATTRIBS%'
ORDER BY LAST_CAPTURED;

You may use DBA_HIST_SQLBIND instead of V$SQL_BIND_CAPTURE, if you want to base your conclusions on AWR historical data.

3 comments:

  1. I could only get it to work in WHERE clauses.

    ReplyDelete
    Replies
    1. Yes, bind variables in SQL are variables that substitute literals in the WHERE clauses.
      If, by any chance, you are interested in finding the values the variables get in PL/SQL stored procedures, when they are used in SQL cursors, you may use the following query from Oracle Note 1351206.1: "Extract the Values of Bind Variables used by all the SQL Cursors in a Stored Procedure from the Data Dictionary":

      SELECT S.SQL_TEXT, NVL(VALUE_STRING,'null'), O.OWNER, O.OBJECT_NAME, S.PROGRAM_LINE# AS VALUE_STRING
      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 UPPER(O.OBJECT_NAME) LIKE '[proc_name]';

      Delete