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

is

cn number;

cv varchar2(32);

cd date;

cnv nvarchar2(32);

cr rowid;

cc char(32);

begin

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);

else

return 'UNKNOWN DATATYPE';

end if;

end;

/

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 http://jonathanlewis.wordpress.com/2008/07/24/bind-capture/

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:

http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output