Oracle SQL Monitoring Reports — The One Feature Every DBA Should Know Better

May 26, 2026

If you've been working with Oracle databases long enough, you've almost certainly pulled up a SQL Monitoring Report at some point. They're one of the most powerful tools in a DBA's arsenal — giving you real-time visibility into exactly what a query is doing, how much CPU it's burning, which execution plan it picked, and where it's spending most of its time.

But here's the thing — most people only scratch the surface with SQL Monitoring. They pull a report from OEM when something is on fire, glance at it, and move on. There's a lot more to understand about how these reports work, when they're generated, and how to extract them when you need them most.

Let me walk you through what I think every DBA should know.

What Are SQL Monitoring Reports?

SQL Monitoring Reports give you critical runtime statistics for a query — things like elapsed time, CPU consumption, I/O reads, parallel execution details, and a line-by-line breakdown of the execution plan with actual vs estimated row counts. What makes them special is that you can view them while the query is still running, not just after it finishes. That real-time visibility is what sets SQL Monitoring apart from most other diagnostic tools in Oracle.

Two Ways to Extract Them

1. Real-Time Reports

These are extracted while the query is actively executing. You have two options:

OEM (Oracle Enterprise Manager) — the GUI route, great for visual drill-down
SQL*Plus commands — what we'll focus on here, because it works everywhere without needing OEM access

2. Historical Reports

These are reports for queries that have already completed. Historically, SQL Monitoring data was only kept in memory buffers and was lost once the query finished or the buffer was recycled. From later versions of Oracle onwards, these reports are persisted to disk and tied to your AWR retention policy, meaning you can go back in time and investigate a query that ran hours or even days ago.

Why Isn't Every Query Getting a SQL Monitoring Report?

This is one of the most common questions DBAs run into. You look for a monitoring report for a query and it simply doesn't exist. That's not a bug — it's by design. Three hidden parameters control this behaviour:

_sqlmon_threshold (default: 5) Any query must have a combined CPU and I/O execution time exceeding 5 seconds to qualify for SQL Monitoring. Fast queries simply don't generate reports.

_sqlmon_max_planlines (default: 300) If a query's execution plan exceeds 300 lines, it won't be monitored. Complex queries with massive plans can fall into this bucket.

_sqlmon_max_plan (default: 20 × CPU_COUNT) This limits the total number of queries being monitored concurrently by SQL Monitor at any point in time.

Important: I'd strongly advise against changing any of these at the system level in production. However, they can be adjusted at the session level for testing and diagnostic purposes, which is perfectly reasonable.

Extracting Historical SQL Monitoring Reports

Since later Oracle versions persist SQL Monitoring data to disk via AWR, you can query DBA_HIST_REPORTS to find historical reports. Here's how to do it:

Step 1 — Find the Report ID for your SQL ID

SELECT report_id 
FROM dba_hist_reports
WHERE key1 = 'your_sql_id';

Step 2 — Extract the full report using the Report ID

SET echo ON
SET trimspool ON
SET TRIM ON
SET pages 0
SET linesize 4000
SET LONG 10000000
SET longchunksize 1000000

SPOOL sqlmon_report.html

SELECT dbms_auto_report.report_repository_detail(
         rid  => &report_id,
         TYPE => 'active'
       )
FROM dual;

SPOOL OFF

Use TYPE => 'active' for an interactive HTML report, or TYPE => 'text' if you just need plain text output.

A Few Things Worth Knowing

SQL Monitoring is available from Oracle 11g R1 onwards and requires the Diagnostics + Tuning Pack license.
The MONITOR hint can force SQL Monitoring on a specific query even if it doesn't meet the default threshold — useful when you want to observe a fast query for testing.
The NO_MONITOR hint does the opposite — suppresses monitoring for a query you don't want tracked.

Wrapping Up

SQL Monitoring Reports are one of those features that look simple on the surface but have a surprising amount of depth once you start digging into them. Understanding why a report wasn't generated, knowing how to pull historical reports from AWR, and being aware of the parameters that govern monitoring behaviour will make you significantly more effective when you're under pressure diagnosing a production issue.

There are plenty of resources covering how to pull real-time reports through OEM and SQL*Plus — so I deliberately kept this focused on the lesser-discussed aspects. Hope it adds something useful to your toolkit.

As always, drop your questions or experiences in the comments — would love to hear how others are using SQL Monitoring in their environments 👇

#Oracle #OracleDatabase #DBA #DatabasePerformance #SQLMonitoring #OracleCloud #DatabaseTuning #TechBlog

Search This Blog

Oracle Internals