Latest posts by Cyrille Modiano (see all)
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
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
- 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 */
- 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.
6 thoughts on “OCP 12C – Real-Time Database Operation Monitoring”
Thank you Cyrille!
You’re welcome ! Hope it helps.
Hi, I generated the report, but in status column shows “DONE (ALL ROWS), DONE” what is the difference between this two?
Here is what the documentation says :
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 ?
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.