Real-Time Database Operation Monitoring in Oracle Database 12c Release 1 and 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. 

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 signaled 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 , but 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.

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

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.