Tuesday, 13 December 2016

Real-Time Database Operation Monitoring in Oracle Database 12c

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.
  • 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
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 /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

Examples of the output for each available TYPE are displayed below.

- XML
- ACTIVE - Active HTML is default type.
  • 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