Mastering Oracle Real‑Time SQL Monitoring for Faster Performance Diagnosis

Before diving into Real‑Time SQL Monitoring, the article defines key terms such as Table Queue, wall time vs. elapsed time, build and probe sides of a hash join, and row source.

Overview

SQL Monitoring was introduced in Oracle 11gR1 and enhanced in later releases. It aggregates all critical performance statistics for a SQL statement on a single page, automatically enabling for parallel queries. The feature is still little known in China.

Which SQL Statements Are Monitored

Serial SQL whose CPU or I/O time exceeds 5 seconds (controlled by _sqlmon_threshold).

All parallel SQL statements.

SQL with the MONITOR hint.

System parameters statistics_level must be TYPICAL or ALL, and control_management_pack_access must be DIAGNOSTIC+TUNING. The default plan line limit is 300; it can be changed via _sqlmon_max_planlines.

Finding the Real‑Time SQL Monitoring UI

The feature can be accessed through Enterprise Manager, Enterprise Manager Cloud Control (EMCC), SQL Developer, or the DBMS_SQLTUNE package. The article focuses on EMCC, showing step‑by‑step screenshots of navigating to the database, selecting the SQL Monitoring page, and interpreting the status column (Running, Completed, Queued, Error).

Detailed Walkthrough of the Monitoring Page

General Information

Shows SQL text, parallel degree, start time, last refresh, user, and fetch calls. Clicking the ellipsis after the SQL text reveals the full statement and bind variables (available from 11.2.0.2).

Time and Wait Statistics

Displays wall time, DB Time, and wait‑activity percentages. Hovering over DB Time bars reveals the distribution across wait events and CPU.

Wall Time vs. DB Time

Wall time is the elapsed time visible to the user; DB Time is the total CPU time consumed across all parallel processes. For parallel queries, DB Time can exceed wall time.

I/O Statistics

Shows logical I/O (buffer gets), physical I/O request counts, and total I/O bytes, broken down by operation type.

Details Tab

Contains sub‑tabs for Plan Statistics, Plan, Parallel, Activity, and Metrics. Row‑source icons indicate parallelism (multiple small‑person icons) and execution direction (right‑arrow). Estimated vs. actual row counts help spot stale statistics.

Timeline Column

Visualizes the active time span of each row source using ASH sampling. Clicking shows exact durations.

Activity Percentage

Derived from v$active_session_history, it highlights the row sources that consumed the most DB Time.

Parallel Tab

Shows each parallel server’s work, confirming the producer‑consumer model.

Metrics Tab Displays CPU, memory, and I/O utilization percentages for the query. Monitoring Index Creation By running an index rebuild with parallelism (e.g., alter index tt rebuild online parallel 6; ), the monitoring page shows the progress of the full‑table scan and the subsequent sort/create phase, including estimated vs. actual rows and percentage completed. Practical Example A hash‑join query on a 4.4 GB table is examined. Initial parallel broadcast caused high DB Time (12.6 min) and large temporary tablespace usage (4 GB). Switching to hash distribution reduced wall time to 1.3 min, DB Time to 8.3 min, and temporary space to 966 MB, with activity percentage on the hash join dropping from 51 % to 22 %. Relevant Views GV$SQL_MONITOR – real‑time monitoring data per execution. GV$SQL_PLAN_MONITOR – per‑plan‑line performance metrics. GV$ACTIVE_SESSION_HISTORY – ASH data with SQL_PLAN_LINE_ID , SQL_PLAN_OPERATION , etc. Controlling Monitoring Parallel queries are monitored automatically. Serial queries are monitored only if CPU or I/O exceeds 5 seconds. Hints MONITOR and NO_MONITOR can force or suppress monitoring. NO_MONITORING is unrelated and disables column‑usage collection. Retrieving Monitoring Output via SQL Use dbms_sqltune.report_sql_monitor(sql_id=>'4vnz8232nugv9', type=>'TEXT') to get a text report. Other formats include HTML, ACTIVE (11gR2), and XML. The SQL_EXEC_ID parameter can fetch historical executions. Overall, Real‑Time SQL Monitoring provides a powerful, visual way for DBAs to pinpoint resource‑intensive row sources, understand wait events, and guide optimization decisions for long‑running or parallel workloads.