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.