Oracle SQL Monitor : Real Time Execution Plan Monitoring :

Introduction :

SQL Monitor is a diagnostics tool that tracks long-running SQL statements while they are still executing. It does not require you to enable a trace, does not generate trace files, and adds almost no overhead. When a SQL statement crosses the threshold of 5 seconds of CPU or I/O time in a single execution, SQL Monitor begins collecting data automatically .

The key difference from everything we have covered so far is timing. ADDM tells you what already happened. SPA predicts what might happen. AWR Compare Periods shows you the difference between two past windows. SQL Monitor shows you what is happening right now, which row source is producing rows, how many rows it has actually produced versus what the optimizer estimated, and where the execution is currently stuck.

Theoretical Foundation :

How SQL Monitor Collects Data :

SQL Monitor does not trace every execution. It monitors statements that meet the threshold, and it stores the monitoring data in memory in the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views . The data includes:

• Actual rows produced by each step in the plan

• CPU time and I/O time consumed by each step

• Memory and temporary space usage

• The current status of the execution

Because the data is in memory and sampled rather than traced, the overhead is negligible. This is why SQL Monitor is safe to leave enabled in production, unlike 10046 or 10053 traces .

What SQL Monitor Reveals That Other Tools Cannot :

The execution plan you see from EXPLAIN PLAN or DBMS_XPLAN is an estimate. The optimizer predicts that a step will return 10 rows, but at runtime it might return 10 million. SQL Monitor shows both numbers side by side. This gap between estimated rows and actual rows is usually the root cause of a bad plan, and SQL Monitor is the fastest way to see it .

Licensing and Prerequisites :

SQL Monitor is part of the Oracle Tuning Pack. The CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING (the default in most installations) for monitoring to work . The STATISTICS_LEVEL parameter must be TYPICAL or ALL .

Step 1 – Verify SQL Monitor is Enabled :

Check the Parameters :

SHOW PARAMETER statistics_level;
SHOW PARAMETER control_management_pack_access;

Expected values :

If statistics_level is BASIC, SQL Monitor will not collect data. Change it:

ALTER SYSTEM SET statistics_level = TYPICAL;

No restart is required.

Step 2 – Find Monitored SQL Statements :

Query V$SQL_MONITOR :

SELECT sql_id, sql_text, status, elapsed_time / 1000000 AS elapsed_sec, cpu_time / 1000000 AS cpu_sec, buffer_gets, disk_reads, executions, username
FROM v$sql_monitor WHERE status <> 'DONE' ORDER BY elapsed_time DESC;

What to Look For :

If a statement has been running for 30 minutes with high buffer gets and low CPU, it is likely stuck on I/O. If CPU is high, it is doing heavy computation. If elapsed time is high but both CPU and I/O are low, it may be waiting on a lock or a resource.

Query V$SQL_PLAN_MONITOR for Step-Level Details :

SELECT sql_id, sql_exec_id, plan_line_id, plan_operation, plan_options, plan_object_name, output_rows, physical_read_bytes, workarea_mem, 
workarea_tempseg FROM v$sql_plan_monitor WHERE sql_id = '&sql_id' ORDER BY plan_line_id;

This gives you the per-step statistics that feed the graphical report.

Step 3 – Generate the SQL Monitor Report :

Active HTML Report (Recommended) :

The active report is the most useful format because it is interactive and visual. It shows the execution plan as a tree with color-coded statistics.

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET PAGESIZE 0
SET LINESIZE 1000
SET TRIMSPOOL ON
SET TRIM ON
SET FEEDBACK OFF
SET TERMOUT OFF

SPOOL /tmp/sql_monitor_report.html

SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(sql_id => '&sql_id', type => 'ACTIVE', report_level => 'ALL') FROM dual;

SPOOL OFF

Open the resulting HTML file in a browser. The active report shows the plan tree with the following for each step :

• Operation name (e.g., TABLE ACCESS FULL, HASH JOIN)

• Estimated rows from the optimizer

• Actual rows produced at runtime

• Activity percentage (what fraction of total DB time is spent in this step)

• I/O and CPU details

Text Report (for Quick Analysis) :

SET LONG 1000000
SET PAGESIZE 50000
SET LINESIZE 200

SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(sql_id => '&sql_id', type => 'TEXT', report_level => 'ALL') FROM dual;

The text report is harder to read but useful for pasting into an email or a ticket.

Step 4 – Read the Report: Find the Problem Step :

The Key Diagnostic Signal :

Look at the step with the highest Activity %. That is where the time is going. Then compare the estimated rows with the actual rows for that step.

A real example from Oracle’s documentation: a query with a missing WHERE clause caused a Cartesian join. The MERGE JOIN CARTESIAN step estimated 19 rows but actually produced 50 million rows. The activity percentage showed that this step consumed most of the DB time .

The SORT ORDER BY step has 99% activity, but the root cause is the MERGE JOIN CARTESIAN below it that produced 50 million rows instead of 19. The sort had to work with an enormous dataset because the join above it went wrong.

Common Patterns :

Step 5 – Force Monitoring for Short Statements :

SQL Monitor only starts automatically for statements that consume 5 seconds or more. If you want to monitor a shorter statement, use the MONITOR hint:

SELECT /*+ MONITOR */ * FROM orders WHERE customer_id = 100;

To prevent monitoring of a statement, use NO_MONITOR:

SELECT /*+ NO_MONITOR */ * FROM small_table;

The hint only works when CONTROL_MANAGEMENT_PACK_ACCESS is DIAGNOSTIC+TUNING .

SQL Monitor vs 10053 Trace :

Both tools help you understand why a plan is bad, but they answer different questions.

The 10053 trace is the deeper tool. It shows you the optimizer’s arithmetic: which access paths it considered, what cost it assigned to each, and why it discarded the better plan . SQL Monitor shows you the consequence of that decision. If SQL Monitor reveals a cardinality mis-estimate but the statistics are accurate, the 10053 trace is the next step.

Important: 10053 only produces output during a hard parse. If the SQL is already in the shared pool, you must add a comment to force a new parse, or flush the shared pool .

Practical Considerations :

SQL Monitor Memory :

SQL Monitor keeps data in memory for recently executed statements. The amount of memory is controlled by _sqlmon_max_plan and related hidden parameters. In most systems, this is not a concern, but if you monitor many concurrent long-running statements, older data may be aged out.

Active Data Guard :

SQL Monitor works on Active Data Guard standby databases. This is useful when you need to diagnose reporting queries running on a standby without affecting the primary.

Composite Database Operations :

If you need to monitor a group of related SQL statements as a single unit, use DBMS_SQL_MONITOR.BEGIN_OPERATION and END_OPERATION. The report groups all statements under one operation name, which is useful for ETL jobs or batch processes .

Summary :

SQL Monitor is the tool you reach for when a statement is running and you cannot wait. It gives you the actual runtime behavior of the execution plan, which is almost always more revealing than the estimate. Once you see where the rows exploded, you can decide whether the fix is a statistic refresh, an index, a plan baseline, or a SQL rewrite.