top of page
  • Writer's picturekyle Hailey

What Bind Variable values were used in my Query

Check out this query by Kerry Osborne via Oracle-L (archives at Oracle-l Archives) for getting the peeked bind variable values from v$sql_plan. These values are in a raw format so the following query requires a function to decode the values. See the function code listing at the end

col bind_name for a20 col bind_type for a20 col value for a40 select bind_name, decode(bind_type,1,’VARCHAR2′,2,’NUMBER’,bind_type) bind_type, decode(bind_type,1,display_raw(bind_data,’VARCHAR2′),2,display_raw(bind_data,’NUMBER’),bind_data) value from ( select extractvalue(value(d), ‘/bind/@nam’) as bind_name, extractvalue(value(d), ‘/bind/@dty’) as bind_type, extractvalue(value(d), ‘/bind’) as bind_data from xmltable(‘/*/*/bind’ passing ( select xmltype(other_xml) as xmlval from v$sql_plan where sql_id like nvl(‘&sql_id’,sql_id) and child_number = ‘&child_no’ and other_xml is not null ) ) d ) ;

create or replace function display_raw (rawval raw, type varchar2)

return varchar2


cn number;

cv varchar2(32);

cd date;

cnv nvarchar2(32);

cr rowid;

cc char(32);


if (type = ‘NUMBER’) then

dbms_stats.convert_raw_value(rawval, cn);

return to_char(cn);

elsif (type = ‘VARCHAR2’) then

dbms_stats.convert_raw_value(rawval, cv);

return to_char(cv);

elsif (type = ‘DATE’) then

dbms_stats.convert_raw_value(rawval, cd);

return to_char(cd,’dd-mon-yyyy’);

elsif (type = ‘NVARCHAR2’) then

dbms_stats.convert_raw_value(rawval, cnv);

return to_char(cnv);

elsif (type = ‘ROWID’) then

dbms_stats.convert_raw_value(rawval, cr);

return to_char(cnv);

elsif (type = ‘CHAR’) then

dbms_stats.convert_raw_value(rawval, cc);

return to_char(cc);



end if;



Interesting to note that the v$sql_bind_capture has captured bind variables but not necessarily the ones that were peeked at optimization and these captured values can be recaptured without being used in the optimization of the query. From

by using an ‘alter system’ call to change the “_cursor_bind_capture_interval” to a (performance-threatening) 10 seconds on a small test system, I found that the values in v$sql_bind_capture would change fairly regularly as I re-executed a given query with constantly changing input bind values.

further reading about bind vars from the errorstack by Tanel:

2 views0 comments

Recent Posts

See All


bottom of page