Skip to content

Using Real-Time SQL Monitoring Reports

    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.

    SQL Monitoring reports 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;

    If you have long running statements you don’t want to monitor, 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 reports 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, 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 SYS_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 scott/tiger
    
    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;

    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 / AS SYSDBA
    
    -- 11gR1
    SELECT sql_id, status
    FROM   v$sql_monitor;
    
    SQL_ID	      STATUS
    ------------- -------------------
    526mvccm5nfy4 DONE (ALL ROWS)
    
    SQL>
    
    -- 11gR2
    SET LINESIZE 200
    COLUMN sql_text FORMAT A80
    
    SELECT sql_id, status, sql_text
    FROM   v$sql_monitor
    WHERE  username = 'SCOTT';
    
    SQL_ID        STATUS              SQL_TEXT
    ------------- ------------------- --------------------------------------------------------------------------------
    526mvccm5nfy4 DONE (ALL ROWS)     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
    
    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
    
    SPOOL /host/report_sql_monitor.htm
    SELECT DBMS_SQLTUNE.report_sql_monitor(
      sql_id       => '526mvccm5nfy4',
      type         => 'HTML',
      report_level => 'ALL') AS report
    FROM dual;
    SPOOL OFF

    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, 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
    
    SPOOL /host/report_sql_monitor_list.htm
    SELECT DBMS_SQLTUNE.report_sql_monitor_list(
      type         => 'HTML',
      report_level => 'ALL') AS report
    FROM dual;
    SPOOL OFF

    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 , 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 /host/report_sql_detail.htm
    SELECT DBMS_SQLTUNE.report_sql_detail(
      sql_id       => '526mvccm5nfy4',
      type         => 'ACTIVE',
      report_level => 'ALL') AS report
    FROM dual;
    SPOOL OFF
    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 http://download.oracle.com/otn_software/emviewers/scripts/flashver.js
    wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js
    wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/document.js
    wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf

    When calling functions in the DBMS_SQLTUNE package, I 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_MONITORREPORT_SQL_MONITOR_LIST subroutines.

    Also See:

    ORAchk