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:
Comments