Oracle 12c provides the ability to monitor PL/SQL operations within the database in real-time.  Database operations are either simple or composite.   Simple operations are single SQL or PL/SQL procedure/function and Composite operations are activity between two points in time.  A session doing multiple transactions, a given session can only be involved in one composite operation at a time.  This article outlines the basics involved in real-time database operation monitoring.

 

 

1. Real time SQL monitoring was introduced in 11g but only supported simple operations,12c supports composite operation monitoring, which allows you the ability to monitor a logical grouping of actions to support a particular business requirement.
2. Real-time Monitoring starts automatically when SQL statements run in parallel or consume more than 5 seconds of CPU or I/O time.
3. Real-Time monitoring can be viewed via Cloud Control on Monitor SQL Execution page which is the preferred method, allowing you to drill down. It can also be monitored via the data dictionary views, or DBMS_SQL_MONITOR package.
4. Data Dictionary views:
   1. V$SQL_MONITOR
   2. V$SQL_MONITOR_SESSTAT
   3. V$SQL_PLAN_MONITOR

 

5. DBMS_SQL_MONITOR package provides the following functions and procedure
   1. REPORT_SQL_MONITOR – gives detailed report.
   2. BEGIN_OPERATION – starts monitoring a session’s performance.
   3. END_OPERATION – ends monitoring a session’s performance

 

6. SQL Monitoring is automatic when STATISTICS_LEVEL is set to TYPICAL or ALL and Oracle will begin monitoring long running queries automatically.
7. The init parameter CONTROL_MANAGEMENT_PACK_ACCESS must be set to default of DIAGNOSTIC+TUNING to use this feature.
8. Hints of MONITOR and NO_MONITOR can be used to force or stop monitoring.

 

SELECT /*+MONITOR*/ first_name from emp;

 

9. Use the BEGIN_OPERATION and END_OPERATION function of DBMS_SQL_MONITOR package to monitor entire session.

 

 

Larry Catt

OCP