Unlock Oracle SQL Monitor: Free Tool for Powerful SQL Optimization

Introduction

Oracle SQL Monitor, introduced in Oracle 11g and included for free in the Enterprise Edition, provides a visual, real‑time view of SQL execution and is a powerful yet often overlooked tool for SQL performance tuning.

How to Open the SQL Monitor Report

Method 1 – EM Console

Step 1: Open the Oracle Enterprise Manager (EM) home page, switch to the Performance tab, and click the SQL Monitor button in the lower‑right corner.

Step 2: Choose a time range and sort SQL statements by duration or database time.

Step 3: In the result list, click the check‑mark icon in the first column to display the detailed execution view.

SQL statements are captured in the v$sql_monitor view when any of the following conditions are met:

Parallel execution of the SQL.

Single‑process execution consuming more than 5 seconds of CPU or I/O.

Use of the /*+ monitor */ hint.

Method 2 – Top Sessions

In the Performance page, locate the Top Sessions panel, click the desired SQL ID, then click the status‑icon in the SQL Monitor view to open the detailed report.

Method 3 – Command‑Line Script

If EM is unavailable but you know the SQL ID, you can generate a monitor report via SQL*Plus:

set trimspool on
set arraysize 512
set trim on
set pagesize 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon.html
select /*+ noparallel */ dbms_sqltune.report_sql_monitor(sql_id=>'YOUR_SQL_ID', report_level=>'ALL', type=>'ACTIVE') from dual;
spool off
!cat sqlmon.html

Open the generated sqlmon.html to view the execution plan.

Using SQL Monitor for Optimization

After opening the execution plan, focus on the right‑most columns showing CPU and WAIT activity to identify the most resource‑intensive steps. Then compare Oracle’s estimated row count with the actual row count:

If the numbers differ significantly, statistics on the involved tables are likely stale and should be recollected.

If they are close, the optimizer’s choices are based on accurate statistics.

In the example case, a long‑running SQL ran for 5.4 hours, with a large gap between estimated and actual rows, indicating outdated statistics. After gathering stats on DMS_CONTAINERS and DMS_CONTAINER_JN, the optimizer chose the correct index on IYC_CNTRID, but still used a low‑selectivity index on TYPE. Creating a composite index on IYC_TYPE and YC_LSTUPDDT forced the optimizer to use the more selective path.

Summary of Steps

Use SQL Monitor to quickly spot abnormal or long‑running SQL statements; if you have the SQL ID, generate a script‑based report.

Inspect the execution plan, focusing on CPU and WAIT activity to locate costly operations.

Compare estimated versus actual row counts to decide whether statistics need to be refreshed.

Apply targeted tuning actions such as gathering statistics, creating or adjusting indexes, or adding optimizer hints based on the insights gathered.

By following these steps, you can leverage Oracle SQL Monitor to streamline SQL performance tuning without additional third‑party tools.