Wednesday, March 27, 2013

V$SQL_BIND_CAPTURE

This simple example, tested on Oracle 11.2, shows how to use V$SQL_BIND_CAPTURE to see the value of bind variables used in a WHERE clause. First I created a table called TAB1 with 1 row of data: 

SQL> create table tab1 (col1 varchar2(10))
  2  /
 
Table created.
 
SQL> insert into tab1 values ('ANDREW')
  2  /
 
1 row created.
 
SQL> select * from tab1
  2  /
 
COL1
----------
ANDREW
 
SQL>

Then I created a bind variable called VAR1. I used this to select the row of data with COL1 set to ANDREW then change it to BRIAN:

SQL> variable var1 varchar2(10)
SQL> begin
  2  select 'ANDREW' into :var1 from dual;
  3  update tab1 set col1 = 'BRIAN' where col1 = :var1;
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
SQL>

I checked that the update had worked:

SQL> select * from tab1
  2  /
 
COL1
----------
BRIAN
 
SQL> 

Then I looked in V$SQL_BIND_CAPTURE to see the value of the bind variable like this: 

SQL> select
  2  a.sql_fulltext, b.name, b.value_string
  3  from v$sql a, v$sql_bind_capture b
  4  where a.sql_id = b.sql_id
  5  and a.child_address = b.child_address
  6  and sql_text like 'UPDATE TAB1%'
  7  /
 
SQL_FULLTEXT
-------------------------------------------------------
NAME
------------------------------
VALUE_STRING
-------------------------------------------------------
UPDATE TAB1 SET COL1 = 'BRIAN' WHERE COL1 = :B1
:B1
ANDREW
 
SQL> 

N.B. This only seems to work for bind variables in WHERE clauses. I also tried to use it to look at the bind variable in the SQL below: 

update tab1 set col1 = :var1 

In this case, the value of the bind variable was not shown.

2 comments:

Martin Preiss said...

Andrew,

v$sql_bind_capture is the first place where I look for bind variables but I take the results with a grain of salt since I saw Tanel Poder's article http://blog.tanelpoder.com/2010/10/18/read-currently-running-sql-statements-bind-variable-values/ and his comment: "V$SQL_BIND_CAPTURE is not a reliable way for identifying the current bind variable values in use. Oracle’s bind capture mechanism does not capture every single bind variable into SGA (it would slow down apps which run lots of short statements with bind variables). The bind capture only selectively samples bind values, during the first execution of a new cursor and then every 15 minutes from there (controlled by _cursor_bind_capture_interval parameter), assuming that new executions of that same cursor are still being started (the capture happens only when execution starts, not later during the execution)."

Martin

Andrew Reid said...

Dear Martin,

Thank you for taking the time to comment on this post. I try to read Tanel Poder's posts too. Unfortunately most of them are way over my head! I'll try to take a look at your blog when I get a moment.

Kind regards,

Andrew