Since Oracle 11g, the database offers real-time SQL Monitoring, a great feature for insights of currently and previously running queries.
In most cases this works great without effort, but sometimes your statements won’t show.
This can be caused by statements with very large execution plans or statements with short execution times.
Monitor statements with very large execution plans
To include statements with very large execution plans in SQL Monitoring, we need to increase two thresholds. The following values served me well, but your millage may vary:
ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;
Remember to execute these commands in the same session and before the statements you want to monitor!
Monitor short running statements
To include short running statements into SQL Monitoring, we can use the
SELECT /*+ MONITOR*/ * FROM DUAL;
Remeber that this hint is only valid when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING.
Execlude statements from SQL Monitoring
To exclude statements from SQL Monitoring, we can use the
SELECT /*+ NO_MONITOR*/ count(*) FROM DBA_OBJECTS CROSS JOIN DBA_OBJECTS;
Access SQL Monitoring
The SQL Monitoring informations can be accessed through
- the tables V$SQL_MONITOR and V$SQL_PLAN_MONITOR
(RAC: GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR)
- a PL/SQL procedure:
variable l_rep CLOB;
exec :l_rep := DBMS_SQLTUNE.REPORT_SQL_MONITOR();
- or with Oracle Enterprise Manager