8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Real-Time SQL Monitoring (DBMS_SQLTUNE.REPORT_SQL_MONITOR)
Real-Time SQL Monitoring reports are available from three locations:
- Cloud Control - Navigate to the database, then do "Performance Hub > SQL Monitoring".
- SQL Developer - Available from the "Tools > Real-Time SQL Monitor" menu.
DBMS_SQLTUNE
package in 11g andDBMS_SQL_MONITOR
package in 12c onward.
In this article we 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.
This functionality requires Enterprise Edition and the Diagnostics and Tuning option. Make sure you have the correct licensing before using this functionality.
- Introduction
- MONITOR Hint
- REPORT_SQL_MONITOR
- REPORT_SQL_MONITOR_LIST
- REPORT_SQL_DETAIL
- Active HTML Reports Offline
- Views
- 12c Updates
Related articles.
- Explain Plan Usage
- DBMS_XPLAN : Display Oracle Execution Plans
- SQL 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.
-- 11gR1 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; -- 11gR2 Onward select /*+ monitor */ d.dname, listagg(e.ename, ',') as employees from emp e join dept d on e.deptno = d.deptno group by d.dname order by d.dname;
If we have long running statements we don't want to monitor, we can use the NO_MONITOR
hint to prevent them being monitored.
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
- TheSQL_ID
of the query of interest. When NULL (the default) the last monitored statement is targeted.SQL_EXEC_ID
- When theSQL_ID
is specified, theSQL_EXEC_ID
indicates the individual execution of interest. When NULL (the default) the most recent execution of the statement targeted by theSQL_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. UseSYS_CONTEXT('USERENV','SID')
for the current session.
The report accesses several dynamic performance views, so you will most likely access it from a privileged user, or a user granted the SELECT_CATALOG_ROLE
role.
To see it in action, first we make sure we have a monitored statement to work with.
conn testuser1/testuser1@//localhost:1521/pdb1 -- 11gR1 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; -- 11gR2 Onward select /*+ monitor */ d.dname, listagg(e.ename, ',') as employees from emp e join dept d on e.deptno = d.deptno group by d.dname order by d.dname;
Monitored statements can be identified using the V$SQL_MONITOR
view. This view was present in Oracle 11g Release 1, but has additional columns in Oracle 11g Release 2, making it much more useful. It contains an entry for each execution monitored, so it can contain multiple entries for individual SQL statements.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba -- 11gR1 select sql_id, status from v$sql_monitor; SQL_ID STATUS ------------- ------------------- 526mvccm5nfy4 DONE (ALL ROWS) SQL> -- 11gR2 Onward set linesize 200 column sql_text format a80 select sql_id, status, sql_text from v$sql_monitor where username = 'TESTUSER1'; SQL_ID STATUS SQL_TEXT ------------- ------------------- -------------------------------------------------------------------------------- 4w4wcds1t6vfh DONE (ALL ROWS) SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ',') AS employees FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname SQL>
Once the SQL_ID
is identified, we can generate a report using the REPORT_SQL_MONITOR
function.
set long 1000000 set longchunksize 1000000 set linesize 1000 set pagesize 0 set trim on set trimspool on set echo off set feedback off -- <= 11gR2 spool /tmp/report_sql_monitor.htm select dbms_sqltune.report_sql_monitor( sql_id => '4w4wcds1t6vfh', type => 'HTML', report_level => 'ALL') as report from dual; spool off -- >= 12c spool /tmp/report_sql_monitor.htm select dbms_sql_monitor.report_sql_monitor( sql_id => '4w4wcds1t6vfh', type => 'HTML', report_level => 'ALL') as report from dual; spool off
Examples of the output for each available TYPE
are displayed below.
- TEXT
- HTML
- XML
- ACTIVE - (save to your PC and run it) Active HTML available in 11gR2 requires a download of Javascript libraries and a Flash movie from an Oracle website, so must be used on a PC connected to the internet, unless you download the relevant libraries and use the
BASE_PATH
parameter in the function call to identify their location.
In Oracle 12c, the REPORT_SQL_MONITOR
function is now found in the DBMS_SQL_MONITOR
package.
REPORT_SQL_MONITOR_LIST
The REPORT_SQL_MONITOR_LIST
function was added in Oracle 11g Release 2 to generate a summary screen, similar to that on the "Monitored SQL Executions" page of Enterprise Manager. There are a number of parameters to filer the content of the report (shown here), but most of the time you will probably only use the TYPE
and REPORT_LEVEL
parameters, similar to those in the REPORT_SQL_MONITOR
function. The query below shows how the function can be used.
set long 1000000 set longchunksize 1000000 set linesize 1000 set pagesize 0 set trim on set trimspool on set echo off set feedback off -- <= 11gR2 spool /tmp/report_sql_monitor_list.htm select dbms_sqltune.report_sql_monitor_list( type => 'HTML', report_level => 'ALL') as report from dual; spool off -- >= 12c spool /tmp/report_sql_monitor_list.htm select dbms_sql_monitor.report_sql_monitor_list( type => 'HTML', report_level => 'ALL') as report from dual; spool off
Examples of the output for each available TYPE
are displayed below.
- TEXT
- HTML
- XML
- ACTIVE - Active HTML is not currently supported, but the parameter list, specifically the
BASE_PATH
, suggest it will be supported in future.
In Oracle 12c, the REPORT_SQL_MONITOR_LIST
function is now found in the DBMS_SQL_MONITOR
package.
REPORT_SQL_DETAIL
Although not documented as part of Real-Time SQL Monitoring, the REPORT_SQL_DETAIL
function added in Oracle 11g Release 2 returns a report containing SQL monitoring information. Once again, it has several parameters (shown here), but you will probably only use a subset of them to target specific SQL statements, as shown below.
set long 1000000 set longchunksize 1000000 set linesize 1000 set pagesize 0 set trim on set trimspool on set echo off set feedback off spool /tmp/report_sql_detail.htm select dbms_sqltune.report_sql_detail( sql_id => '526mvccm5nfy4', type => 'ACTIVE', report_level => 'ALL') as report from dual; spool off
Examples of the output for each available TYPE
are displayed below.
Active HTML Reports Offline
As mentioned previously, by default Active HTML available in 11gR2 require a download of Javascript libraries and a Flash movie from an Oracle website, so must be used on a PC connected to the internet. An alternative to this is to download the relevant files to a HTTP server on your network (or local machine) and use the BASE_PATH
parameter to reference those files rather than the Oracle website.
To show this I will create a new directory under a HTTP server on my network and download the relevant files to it.
mkdir -p /var/www/html/sqlmon cd /var/www/html/sqlmon wget --mirror --no-host-directories --cut-dirs=1 https://download.oracle.com/otn_software/emviewers/scripts/flashver.js wget --mirror --no-host-directories --cut-dirs=1 https://download.oracle.com/otn_software/emviewers/scripts/loadswf.js wget --mirror --no-host-directories --cut-dirs=1 https://download.oracle.com/otn_software/emviewers/scripts/document.js wget --mirror --no-host-directories --cut-dirs=1 https://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf
When calling functions in the DBMS_SQLTUNE
package, use the BASE_PATH
parameter with the value of "http://192.168.0.4/sqlmon" so the active report will use the local copies of the files, rather than accessing them from the internet.
set long 1000000 set longchunksize 1000000 set linesize 1000 set pagesize 0 set trim on set trimspool on set echo off set feedback off spool /host/report_sql_monitor.htm select dbms_sqltune.report_sql_monitor( sql_id => '526mvccm5nfy4', type => 'ACTIVE', report_level => 'ALL', base_path => 'http://192.168.0.4/sqlmon') as report from dual; spool off
Views
The SQL monitoring functionality accesses a number of existing views, but two new dynamic performance views have been added specifically as part of it.
12c Updates
Oracle 12c introduced the DBMS_SQL_MONITOR
package, which is now the location for the REPORT_SQL_MONITOR
, REPORT_SQL_MONITOR_LIST
subroutines.
For more information see:
- DBMS_SQL_MONITOR
- DBMS_SQLTUNE
- MONITOR Hint
- Explain Plan Usage
- DBMS_XPLAN : Display Oracle Execution Plans
- SQL trace, 10046, trcsess and tkprof in Oracle
Hope this helps. Regards Tim...