OCP 12C – Real-Time Database Operation Monitoring

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

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.

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.

6 thoughts on “OCP 12C – Real-Time Database Operation Monitoring

  1. Hi, I generated the report, but in status column shows “DONE (ALL ROWS), DONE” what is the difference between this two?

      1. Thank you Cyrille,

        I am preparing for 1z0-060 and there is one thing that i don’t understand about RTDOM. I think about composite operation.

        How can I tag in 2 differents sessions that the orders they send belong to the same execution of an operation ? All sample I can see make the start and stop in the same session…

        I have made test by myself and I never foudn how to do that. If a run “begin” in another session with the same name, it is considered like another operation.

        Can you help me understand ?

        regards

        Sylvain

        1. Hi Sylvain,

          Thanks for your feedback.

          I had a look at the documentation and it says it’s always in the same session, that might be an error in the book, here is the extract from documentation:

          “A composite database operation consists of the activity of one session between two points in time.”

          The only difference with a database operation it’s it contains multiple statements.

          Cyrille

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.