V$SQL_MONITOR displays bind values.

What are the bind values of a long running query?

When you’re monitoring a long running query, you have to know the bind values of that query. Because, depending on the bind values, the performance of query is very different. For example:

1. between :b1 and :b2

-> If the range of :b1 and :b2 is wide, in some cases, Full Table Scan is better than Index Range Scan.

2. col1 LIKE :b1||’%’

-> If that query is slow, we have to check the contents of :b1. Maybe :b1 is NULL or a very short character (e.g ‘A’).

3. col1 = :b1

-> If col1 column has an index and that query is slow, we have to check column suppressing.

and so on.

Until 10g, to find the bind values, we dump the process state by oradebug, as follows:

$ sqlplus "/ as sysdba"
SQL> oradebug setospid
SQL> oradebug dump processstate 10
SQL> oradebug tracefile_name

And then, search the “Bind#” in the trace file for that cursor. But, sometimes the bind values doesn’t exist in the tracefile. Morever, if the bind values are exist, It’s a little difficult to find the bind values.
Of course, from oracle 10g, oracle provides V$SQL_BIND_CAPTURE view. This view displays information on bind variables used by cursors. But, bind values aren’t always captured for this view.

To overcome this limiation, oracle 11g provide bind values by V$SQL_MONITOR view. This view displays SQL cursors which execution have been (or are being) monitored. SQL monitoring is automatically started when a SQL cursor runs parallel or when it has consumed at least 5 seconds of CPU or I/O time ( This is controlled by “_sqlmon_threshold”). Also, the view provide bind values in “BIND_XML” column. So, you can find the bind values as follows:

SELECT bv.name  name ,
       bv.pos   position,
       bv.type  type,
       bv.value value
FROM   v$sql_monitor s ,
       xmltable( '/binds/bind'
                  passing xmltype( s.binds_xml )
                  COLUMNS name  VARCHAR2( 30 )   path '@name' ,
                          pos   NUMBER           path '@pos',
                          type  VARCHAR2( 15 )   path '@dtystr' ,
                          value VARCHAR2( 4000 ) path '.'
               ) bv
WHERE  s.sql_id=&sql_id 
AND    s.sid=&sid
ORDER  BY bv.pos;
 
 
 
NAME POSITION TYPE VALUE
:B1 1 VARCHAR2(4000) 1
:B2 2 VARCHAR2(4000) 10000001
:B3 3 VARCHAR2(4000) 20000001