Oracle Database - 12c - Real-Time Database Operation Monitoring
In the latest release of Oracle - 12c, Oracle has come up with another brilliant feature for performance monitoring which is Real-Time Database Operation Monitoring.
Real time Sql monitoring was introduced in 11g and helped monitoring sql's in real time.
However, only individual sql's could be monitored. Suppose there is a batch operation or a pl/sql block having multiple calls inside it which might not trigger real-time monitoring by default though it takes a lot of DB and CPU time.
This is where real time database operation monitoring comes in.
The concept and the way to do is straight forward, let me demonstrate.
Execute Start of Operation
-- Do Action ----
End of Operation.
Below is my script which I will be using in this context. I have highlighted in green start of operation and end of operation.
I will be using the infamous scott schema and a cartersian join on dba_objects to have a long running query.
var db_opid number;
EXEC :db_opid := dbms_sql_monitor.begin_operation ('Scott.Batch.Select', forced_tracking => 'Y');
select * from emp a, dept d;
selet * from emp;
select /*+ MONITOR */ * from dba_objects a, dba_objects b;
EXEC dbms_sql_monitor.end_operation('Scott.Batch.Select', :db_opid );
Note -
I have purposefully inserted the monitor hint because I wanted to monitor the sql separately in real time as well.
If you skip the monitor hint then the db decides (on the 5 second and parallel rule) that if the sql has to be monitored or not.
I have used forced_tracking as well to force monitoring of the operation.
Scott.Batch.Select is the name given to the operation which we would see in the performance page.
I am using EM Express but you can find the same stuff in Cloud Control as well.
From the Image 1 below - you can see 2 elements being monitored
One is the sql because of the monitor hint and other is the database operation.
From the Image 2 you can see new options added for filtering the type of operation in the Performance Page
Real time Sql monitoring was introduced in 11g and helped monitoring sql's in real time.
However, only individual sql's could be monitored. Suppose there is a batch operation or a pl/sql block having multiple calls inside it which might not trigger real-time monitoring by default though it takes a lot of DB and CPU time.
This is where real time database operation monitoring comes in.
The concept and the way to do is straight forward, let me demonstrate.
Execute Start of Operation
-- Do Action ----
End of Operation.
Below is my script which I will be using in this context. I have highlighted in green start of operation and end of operation.
I will be using the infamous scott schema and a cartersian join on dba_objects to have a long running query.
var db_opid number;
EXEC :db_opid := dbms_sql_monitor.begin_operation ('Scott.Batch.Select', forced_tracking => 'Y');
select * from emp a, dept d;
selet * from emp;
select /*+ MONITOR */ * from dba_objects a, dba_objects b;
EXEC dbms_sql_monitor.end_operation('Scott.Batch.Select', :db_opid );
Note -
I have purposefully inserted the monitor hint because I wanted to monitor the sql separately in real time as well.
If you skip the monitor hint then the db decides (on the 5 second and parallel rule) that if the sql has to be monitored or not.
I have used forced_tracking as well to force monitoring of the operation.
Scott.Batch.Select is the name given to the operation which we would see in the performance page.
I am using EM Express but you can find the same stuff in Cloud Control as well.
From the Image 1 below - you can see 2 elements being monitored
One is the sql because of the monitor hint and other is the database operation.
From the Image 2 you can see new options added for filtering the type of operation in the Performance Page
