Real Time SQL Monitoring to Capture Bind Variables Values

Real Time SQL Monitoring allows the monitoring of SQL statements while they are being executed. By default Oracle will monitor any SQL statement that either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution.

As of 11gR2 Real Time SQL Monitoring also shows bind variable values used in the SQL statements.


Monitoring could be enabled for any statement by adding the /*+ MONITOR */ hint. Thereafter when the statement runs Oracle will monitor the statement.

However when there's no access to the SQL code this method of enabling monitoring is not possible. This is where dbms_sqltune.import_sql_profile comes in handy.

DBMS_SQLTUNE.IMPORT_SQL_PROFILE is an undocumented method which could be used to attache a profile to a SQL.

This is the test case to attache a monitoring profile to the sql to get the bind variables (11gR2) or/and enable monitoring (11gR1 and 11gR2).

SQL> variable var number
SQL> exec :var := 10; 

SQL> select * from x where b not in (select q from y where y.p = x.a) and x.a = :var;

A   B
--- ------
10  75282

SQL> select sql_id from v$sql where sql_text like 
'select * from x where b not in (select q from y where y.p = x.a) and x.a%';

SQL_ID
-------------
3nprf920bb7az  

---- create the profile for the sql
Declare
Sqltext Clob;
Begin
Select Sql_Text Into Sqltext From V$sqlarea Where Sql_Id='3nprf920bb7az';
Dbms_Sqltune.Import_Sql_Profile(
    Sql_Text => Sqltext,
    Profile => Sqlprof_Attr('MONITOR'),
    CATEGORY => 'DEFAULT',
    Name => 'monitor_profile',
    Force_Match => True);
End;
/

SQL> select * from x where b not in (select q from y where y.p = x.a) and x.a = :var;

When the above select statement is executed SQL will appear in the real time monitoring.

Once done drop the profile with

exec dbms_sqltune.DROP_SQL_PROFILE('monitor_profile');

Profile could be attached to update statements as well but monitoring window does not show the bind variables values.

SQL> variable a number;
SQL> variable b number;  
SQL> exec :a := 10;
SQL> exec :b := 20;
SQL> update vartable set a = :a * :b;
SQL> commit;
SQL> select sql_id from v$sqlarea where sql_text like 'update vartable set a = :a * :b';

SQL_ID
-------------
1g2mxvhws6a3x

Declare
Sqltext Clob;
Begin
Select Sql_Text Into Sqltext From V$sqlarea Where Sql_Id='1g2mxvhws6a3x';
Dbms_Sqltune.Import_Sql_Profile(
    Sql_Text => Sqltext,
    Profile => Sqlprof_Attr('MONITOR'),
    CATEGORY => 'DEFAULT',
    Name => 'monitor_profile',
    Force_Match => True);
End;
/

SQL> update vartable set a = :a * :b;

5 rows updated.

SQL Monitoring shows the following for the update statement
For insert statments attaching profile has no effect and they do not show up on the SQL Monitoring unless /*+ Monitor */ hint is used.

SQL> insert /*+ monitor */ into vartable values (:a,:b);

1 row created.

SQL> commit;