What is Real Time Database Operation Monitoring ?

• Real Time Database Operation Monitoring will help you track the progress of a set of sql statements and let you create a report.
• Real Time Database Operation Monitoring acts as a superset of all monitoring components like : ASH, DBMS_MONITOR …
• You can generate Active Reports which are available offline and don’t need access to the production system once generated.
• Real Time Database Operation Monitoring is part of the tuning pack and subject to license.

What is a Database Operation ?

• A database opration is one or more sql statements running inside a single session.
• There are two types of database operations :
  • Simple : A simple database operation is one SQL or PL/SQL statement.
  • Composite : A Composite database operation consists in multiple SQL or PL/SQL statement running in a single session.

How Can I take advantage of Real-Time Database Operation Monitoring ?

• RTDOM helps you monitor batch jobs and send alerts when the time planned to execute the batch job is exceeded.
• It helps you find the expensive SQL Statements
• Diagnose changes by comparing current job execution with the previous executions
• Monitor and compare executions times after a database upgrade.

Identifying a Database OPeration

• A database operation is identified by :
  • An operation name
  • An operation id
• Inside the V$SQL_MONITOR view the following is used :
  • the SQL_ID (SQL Statement ID)
  • The SQL_EXEC_START (Time of the sql execution start)
  • The SQL_EXEC_ID (The unique execution id)

Starting Monitoring for a database operation

single statement

• You can start monitoring at the SQL statement level by usin the hint /*+ MONITOR */
• To disable monitoring for this specific satement use the hint /*+ NO_MONITOR */

Database operation

• Monitoring a database opration is done by using the
  • DBMS_SQL_MONITOR.BEGIN_OPERATION to start the database operation.
  • DBMS_SQL_MONITOR.END_OPERATION to stop the database operation.

DBMS_SQL_MONITOR.BEGIN_OPERATION (
   dbop_name       IN VARCHAR2,
   dbop_eid        IN NUMBER   := NULL,
   forced_tracking IN VARCHAR2 := NO_FORCE_TRACKING,
   attribute_list  IN VARCHAR2 := NULL)
  RETURN NUMBER;

DBMS_SQL_MONITOR.END_OPERATION  (
   dbop_name       IN VARCHAR2,
   dbop_eid        IN NUMBER   := NULL,
  RETURN NUMBER;

Generate a Database Operation Monitoring Report

• Once you finished the Database Operation Monitoring you can generate a report using the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR which builds a detailed report about a specific database operation.
• You can additionaly use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST which gives details about all subsets of a database operation.