A bit of an history before I begin…
Oracle 10.2 had exposed bind variables values passed to a SQL statement via a new view named V$SQL_BIND_CAPTURE. Nevertheless, to limit the overhead, Oracle captured the bind at most every 15 minutes for a given cursor.
In other words, you were not able to see bind values for each SQL execution.
In some cases, especially when binds values have major influence on SQL execution, knowing the bind values passed to a given execution could be very important.
Oracle 11.2 has extended this ability.V$SQL_MONITOR was expanded and a new column BIND_XML was added to it.
V$SQL_MONITOR is one of my favourite additions to 11g. It displays very useful information on SQL statement executions and it is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time. (My only complaint about this view is that it is not kept in history. No dba_hist_sql_monitor yet. Maybe someone in Oracle will read this post and add it to their TODO list…).
Anyway, with the addition of BIND_XML new column, this means that in 11gR2 we will be able to see bind variables values for each execution of such SQL statements. Sure, it is still only partitial picture of bind variables world (since we will have bind information only for those statements who run in parallel or run longer that 5 seconds), but it is still more than we had before…
And as examples always make life much easier, here is a simple demonstration:
SQL> var a1 number;
SQL> var a2 varchar2(30);
SQL> var a3 varchar2(40);
SQL>
SQL> exec :a1 := 1000;
PL/SQL procedure successfully completed.
SQL> exec :a2 := ‘%A%’;
PL/SQL procedure successfully completed.
SQL> exec :a3 := ’01-JAN-2010′;
PL/SQL procedure successfully completed.
SQL> set timing on
SQL> select count(*)
2 from dba_tables t, dba_objects o
3 where o.object_id > :a1
4 and t.table_name like :a2
5 and o.created > to_date(:a3,’DD-MON-YYYY’)
6 and o.object_type=’TABLE’
7 and o.owner=t.owner
8 and o.object_name=t.table_name;
COUNT(*)
———-
2163
SQL> set long 1000
SQL> set lines 130
SQL> select xmltype(binds_xml) from v$sql_monitor
2 where sql_text like ‘select count(*)%from dba_tables t, dba_objects o%’;
XMLTYPE(BINDS_XML)
——————————————————————————–
<binds>
<bind name=”:A1″ pos=”1″ dty=”2″ dtystr=”NUMBER” maxlen=”22″ len=”2″>1000</bind>
<bind name=”:A2″ pos=”2″ dty=”1″ dtystr=”VARCHAR2(32)” maxlen=”32″ csid=”38″ len=”3″>%A%</bind>
<bind name=”:A3″ pos=”3″ dty=”1″ dtystr=”VARCHAR2(128)” maxlen=”128″ csid=”38″ len=”11″>01-JAN-2010</bind>
</binds>
As you can see, since running this SQL took a bit more than 5 seconds, its execution as well as all bind variable values passed for this execution can be instantly observed in v$sql_monitor.
Note #1: It is possible to change the 15 minutes bind capture interval by setting an undocumented parameter “_cursor_bind_capture_interval”, (though it can have a large impact on system performance), i.e
ALTER SYSTEM SET “_cursor_bind_capture_interval” = 30; — capture every 30 seconds
Note #2: v$sql_monitor requires a licenses for Oracle Diagnostics & Tuning Packs
Comments are most welcome..