Real-Time Database Operation Monitoring

Implement real-time database operation monitoring

Real-time monitoring can be activated automatically when more than five (5) seconds of CPU or I/O time is used with the default database settings. It also be activated explicitly through a SQL hint or through PL/SQL procedures and functions.

There appears to be a bug with retrieval of reports for monitored DB operations.

References

• Database 2 Day + Performance Tuning Guide
  • 5 Monitoring Real-Time Database Operations
• PL/SQL Packages and Types Reference
  • 151 DBMS_SQL_MONITOR
    • BEGIN_OPERATION Function
    • END_OPERATION Procedure
    • REPORT_SQL_MONITOR Function

Procedure

All commands are run as the SYS user in the JAM0 PDB within the JAR CDB on PADSTOW.

Monitoring at the Database Level

SQL statements are monitored automatically if:

• The system parameter, STATISTICS_LEVEL, is set to TYPICAL or ALL.
• The system parameter, CONTROL_MANAGEMENT_ACCESS, is set to DIAGNOSTIC+TUNING.
• CPU time or I/O time exceeds five (5) seconds for the SQL statement.

Monitoring at the Statement Level

The MONITOR hint is added to the SQL statement as shown in the example below to enable Real Time monitoring:

SELECT /*+ MONITOR */ SUM(dbms_random.value()) FROM dual CONNECT BY level < 1000000;

The NOMONITOR hint is added to the SQL statement disable Real Time monitoring.

Reporting on a Monitored SQL Statement

To report on the last monitored SQL statement, run the follow SQL statement using REPORT_SQL_MONITOR:

SET LINESIZE 180 SET PAGESIZE 1000 COLUMN report FORMAT A180 select dbms_sql_monitor.report_sql_monitor() as report from dual;

The sample output is:

REPORT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL Monitoring Report SQL Text ------------------------------ SELECT /*+ MONITOR */ SUM(dbms_random.value()) FROM dual CONNECT BY level < 100000 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (73:40065) SQL ID : 61f4rabuwbcd6 SQL Execution ID : 16777216 Execution Started : 01/27/2020 12:17:03 First Refresh Time : 01/27/2020 12:17:03 Last Refresh Time : 01/27/2020 12:17:03 Duration : .675846s Module/Action : sqlplus@padstow.yaocm.id.au (TNS V1-V3)/- Service : jam0.yaocm.id.au Program : sqlplus@padstow.yaocm.id.au (TNS V1-V3) Fetch Calls : 1 Global Stats =========================================================== | Elapsed | Cpu | PL/SQL | Other | Fetch | Buffer | | Time(s) | Time(s) | Time(s) | Waits(s) | Calls | Gets | =========================================================== | 0.68 | 0.64 | 0.45 | 0.04 | 1 | 6 | =========================================================== SQL Plan Monitoring Details (Plan Hash Value=968211682) ===================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ===================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | | 2 | +0 | 1 | 1 | | | | 2 | CONNECT BY WITHOUT FILTERING | | | | 1 | +0 | 1 | 99999 | | | | 3 | FAST DUAL | | 1 | 2 | 1 | +0 | 1 | 1 | | | =====================================================================================================================================

Monitoring a DB Operation

The following anonymous PL/SQL block demonstrates the use of BEGIN_OPERATION and END_OPERATION to monitor several SQL statements as a single operation:

VARIABLE l_op_name VARCHAR2(50) VARIABLE l_dbop_eid NUMBER BEGIN :l_op_name := 'CPU Load Test'; :l_dbop_eid := dbms_sql_monitor.begin_operation(:l_op_name, forced_tracking => 'Y'); END; / DECLARE l_result NUMBER := 0; BEGIN FOR i IN 1..5 LOOP SELECT SUM(dbms_random.value()) INTO l_result FROM dual CONNECT BY level < 1000000; END LOOP; END; / EXEC dbms_sql_monitor.end_operation(:l_op_name, :l_dbop_eid);

The EM Express Performance HUB shows:

Display Statistics for Monitored Operation

For a run of the DB operation, 'CPU Load Test', the following SQL is used to capture a snapshot of various statistics:

SELECT n.display_name, s."VALUE" FROM v$sql_monitor_sesstat s INNER JOIN v$statname n USING ( statistic# ) INNER JOIN v$sql_monitor m USING ( "KEY" ) WHERE m.dbop_name = 'CPU Load Test' AND s."VALUE" > 0;

Sample output is:

Display Status of a Monitored DB Operation

For a run of the DB operation, 'CPU Load Test', the following SQL is used to capture a snapshot of various statistics:

SELECT dbop_name, dbop_exec_id, status FROM v$sql_monitor WHERE dbop_name = 'CPU Load Test';

Sample output is:

Reporting on Monitored DB Operations

For a run of the DB operation, 'CPU Load Test', I attempted to produce a report using REPORT_SQL_MONITOR, but it failed as follows:

SQL> select dbms_sql_monitor.report_sql_monitor(dbop_name => 2 'CPU Load Test') from dual; Error starting at line : 1 in command - select dbms_sql_monitor.report_sql_monitor(dbop_name => 'CPU Load Test') from dual Error report - ORA-31011: XML parsing failed ORA-19213: error occurred in XML processing at lines 1 LPX-00288: CDATA-section-close delimiter is prohibited in element content ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at "SYS.DBMS_SQLTUNE", line 10320 ORA-06533: Subscript beyond count ORA-06512: at "SYS.DBMS_SQLTUNE", line 16211 ORA-06512: at "SYS.DBMS_SQLTUNE", line 16307 ORA-06512: at "SYS.DBMS_SQL_MONITOR", line 509 ORA-06512: at line 1