Wednesday, February 21

Viewing bind variable values in 10g

Oracle 10g introduced a couple of new nice views to help tune queries that use bind variables.
One cool view is v$sql_bind_capture, this view hold the latest captured value for each bind variable in queries that has been run.
First have a look in v$sql to find the SQL query you are looking for, join the sql_id to v$sql_bind_capture and to view the bind variable values for that query.
Example:
select
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b
using (sql_id)
where
b.value_string is not null
and sql_id='f8pavn1bvsj7t'
/

SQL_TEXT BIND_NAME BIND_STRIN
------------------------------------------- ---------- ----------
select con#,obj#,rcon#,enabled,nvl(defer,0) :1 9110
from cdef$ where robj#=:1
I found a pretty bad example here, an Oracle internal dictionary query, but it should show the point.

10 comments:

Anonymous said...

just what i'm looking for

found you by googling: sql bind variable values

you were ranked # 4 in the search list

thanks man :)

Anonymous said...

Is there any way by whcih we can find out previous bind variable values, like for example I want to see bind variable passed to the query for it's run?

-Thanks,
Jayashree

Anonymous said...

try this one:
http://seilerwerks.wordpress.com/2008/01/29/vsql_bind_capture-not-quite-capturing-sql-binds/
you will a solution:
select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = …..

MosheElisha said...

v$sql_bind_capture only captures the bind vars when the explain plan is created.
After executing the same select with a different bind value the new bind value does not appear in v$sql_bind_capture. It seems that Oracle uses this table only when it creates the explain plan and not when the explain plan already exists.

Execute this:

declare
my_var varchar2(10) := 'MYVAR_VAL1';
l_result number;
begin
select count(1)
into l_result
from dual
where dummy = my_var;
end;

And you can see the bind value using:

select * from v$sql_bind_capture where value_string like 'MYVAR_%'

Then execute the same PL/SQL block with my_var = 'MYVAR_VAL2' and check v$sql_bind_capture and you will see that the second value does not exist there.

Where can I find the bind var for all the queries even queries that were already hard parsed?

Anjul said...

Hi, Thanks!

Is there anyway in Oracle 9i where I can see bind variable values?

- Anjul

Nirmal Ari said...

Though I am replying to an old posting, but thought it may help someone. Try this:

select SQL_ID,LAST_CAPTURED,VALUE_STRING from DBA_HIST_SQLBIND where SQL_ID='0uf7cy3tdwpmf' order by 2;


Good Luck

Nirmal Arri

Anonymous said...

I was having some problems when I was working on pharmacy reviews site, and those problems are very similar to the ones you are describing on this post

Invinci said...

Was Short and specific thanks

osval said...

Really a excellent article!
it is what i was looking for, because when you do a explain plan with bind variables the output is different if you do with the exact values!
Thanks a lot for this great solution.

Anand Tirthgirikar said...

Can you please tell how to get bind values which are not in WHERE clause ?