Real-Time Database Operation Monitoring in Oracle Database 12c Release 1 and 2

Real-Time Database Operation Monitoring in Oracle Database 12c Release 1 and 2 (12.1 and 12.2)

Oracle 11g introduced Real-Time SQL Monitoring, allowing you to watch the progress of long running SQL, or SQL you explicitly asked to be monitored using the MONITOR hint. Oracle 12c extends this functionality, allowing you to explicitly monitor composite operations made up of a variety of SQL statements and PL/SQL calls containing SQL. The DBMS_SQL_MONITOR package provides the API for this functionality, as well as rehousing some of the subroutines previously found in the DBMS_SQLTUNE package.

 This functionality requires Enterprise Edition and the Diagnostics and Tuning option. Make sure you have the correct licensing before using this functionality.

• Real-Time SQL Monitoring Updates
• Real-Time SQL Database Operation Monitoring
• REPORT_SQL_MONITOR
• REPORT_SQL_MONITOR_LIST
• REPORT_SQL_DETAIL
• Views
• 12cR2 Updates

Related articles.

• Real-Time SQL Monitoring using DBMS_SQLTUNE

Real-Time SQL Monitoring Updates

The basics of the real-time SQL monitoring functionality are unchanged from Oracle 11g, except the REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST routines have been moved from the DBMS_SQLTUNE package to the DBMS_SQL_MONITOR package.

Real-Time SQL Database Operation Monitoring

Oracle 12c allows you to monitor a series of SQL and PL/SQL calls containing SQL and group them together as a composite database operation. The start and end of the operation are signalled by the BEGIN_OPERATION and END_OPERATION routines from the DBMS_SQL_MONITOR package.

The BEGIN_OPERATION function accepts the following parameters.

• DBOP_NAME : A user-defined name for the composite database operation.
• DBOP_EID : An optional unique identifier for the current execution of the composite database operation. If it is NULL, a unique execution identifier is created.
• FORCED_TRACKING : When set to the constant FORCE_TRACKING (Y), the operation is tracked. When set to NO_FORCE_TRACKING (N), the default, the operation is only tracked if it has consumed 5 seconds of CPU or I/O time.
• ATTRIBUTE_LIST : An optional comma-separated list of name-value pairs of attributes to restrict the calls that are monitored during the operating period.

The END_OPERATION uses just the first two of those parameters, but both are mandatory.

The examples below use the following objects.

DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 1000000;

CREATE TABLE t2 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 1000000;

Begin an operation with forced tracking.

VARIABLE l_dbop_eid NUMBER;

BEGIN
  :l_dbop_eid := DBMS_SQL_MONITOR.begin_operation (
                   dbop_name       => 'db_op_1',
                   dbop_eid        => :l_dbop_eid,
                   forced_tracking => DBMS_SQL_MONITOR.force_tracking
                 );
END;
/

Perform an operation that combines references to T1 and T2.

BEGIN
  FOR cur_rec IN (SELECT * FROM t1) LOOP
    NULL;
  END LOOP;

  FOR cur_rec IN (SELECT * FROM t2) LOOP
    NULL;
  END LOOP;
END;
/

End the operation.

BEGIN
  DBMS_SQL_MONITOR.end_operation (
    dbop_name       => 'db_op_1',
    dbop_eid        => :l_dbop_eid
  );
END;
/

We can see the operation was monitored using the V$SQL_MONITOR view.

SET LINESIZE 200

SELECT dbop_name, dbop_exec_id, status
FROM   v$sql_monitor
WHERE  username = 'TEST';

DBOP_NAME                      DBOP_EXEC_ID STATUS
------------------------------ ------------ -------------------
db_op_1                                   3 DONE
db_op_1                                   2 DONE
db_op_1                                   1 DONE

SQL>

REPORT_SQL_MONITOR

The usage of the REPORT_SQL_MONITOR function for real-time SQL monitoring is unchanged compared to 11g (see here), but is has been moved to the DBMS_SQL_MONITOR package. For monitoring DB operations, all we need to do is specify the DBOP_NAME parameter and we will get a report on the latest execution of the specified database operation. Alternatively, we can specify the DBOP_EXEC_ID parameter if we don't want the latest execution.

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_SQL_MONITOR.report_sql_monitor(
  dbop_name    => 'db_op_1',
  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

REPORT_SQL_MONITOR_LIST

The REPORT_SQL_MONITOR_LIST function works in the same way it did in 11gR2, but is has been moved to the DBMS_SQL_MONITOR package and now supports active reports.

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_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

REPORT_SQL_DETAIL

The REPORT_SQL_DETAIL function is still located in the DBMS_SQLTUNE package. You can see an example of its use here.

Views

There are some minor changes to the views compared to 11g, but they are still used in the same way.

• V$SQL_MONITOR
• V$SQL_PLAN_MONITOR

12cR2 Updates

The main change in Oracle Database 12.2 is the addition of two new parameters to BEGIN_OPERATION function.

• SESSION_ID : The ID of the session to be monitored. If NULL the current session is monitored.
• SESSION_SERIAL : The serial number of the session to be monitored. If NULL the serial number derived based on the session ID.

These allow an operation to be started in a separate session.