Real-Time SQL Monitoring reports are available from three locations:
Enterprise Manager - Click the "Performance" tab, then the "SQL Monitoring" link at the bottom-right of the page to display the "Monitored SQL Executions" screen. Click the SQL_ID of interest to display the SQL monitoring report.SQL Developer - Available from the "Tools > Monitor SQL" menu.DBMS_SQLTUNE package.
In this article I will demonstrate the use of the DBMS_SQLTUNE package to display SQL monitoring reports without using Enterprise Manager or SQL Developer. This article has been updated to include additional functionality introduced in Oracle 11g Release 2.
IntroductionMONITOR HintREPORT_SQL_MONITORREPORT_SQL_MONITOR_LISTREPORT_SQL_DETAILActive HTML Reports OfflineViews
Related articles.
Explain Plan UsageDBMS_XPLAN : Display Oracle Execution PlansSQL trace, 10046, trcsess and tkprof in Oracle
Introduction
Oracle 11g automatically monitors SQL statements if they are run in parallel, or consume 5 or more seconds of CPU or I/O in a single execution. This allows resource intensive SQL to be monitored as it is executing, as well as giving access to detailed information about queries once they are complete.
SQL monitoring requires the STATISTICS_LEVEL parameter to be set to 'TYPICAL' or 'ALL', and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to 'DIAGNOSTIC+TUNING'.
SQL> CONN / AS SYSDBA
Connected.
SQL> SHOW PARAMETER statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> SHOW PARAMETER control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
MONITOR Hint
The MONITOR hint switches on SQL monitoring for statements that would not otherwise initiate it.
SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY d.dname;
REPORT_SQL_MONITOR
The REPORT_SQL_MONITOR function is used to return a SQL monitoring report for a specific SQL statement. The SQL statement can be identified using a variety of parameters, but it will typically be identified using the SQL_ID parameter.
The function can accept many optional parameters, shown here, but most of the time you will probably only use the following.
SQL_ID - The SQL_ID of the query of interest. When NULL (the default) the last monitored statement is targeted.SQL_EXEC_ID - When the SQL_ID is specified, the SQL_EXEC_ID indicates the individual execution of interest. When NULL (the default) the most recent execution of the statement targeted by the SQL_ID is assumed.REPORT_LEVEL - The amount of information displayed in the report. The basic allowed values are 'NONE', 'BASIC', 'TYPICAL' or 'ALL', but the information displayed can be modified further by adding (+) or subtracting (-) named report sections (eg. 'BASIC +PLAN +BINDS' or 'ALL -PLAN'). This is similar to the way DBMS_XPLAN output can be tailored in the later releases. I almost always use 'ALL'.TYPE - The format used to display the report ('TEXT', 'HTML', 'XML' or 'ACTIVE'). The 'ACTIVE' setting is new to Oracle 11g Release 2 and displays the output using HTML and Flash, similar to the way it is shown in Enterprise Manager.SESSION_ID - Targets a subset of queries based on the specified SID. Use S