Real -Time Database Monitoring which is a new feature in Oracle 12c extends Real-Time SQL Monitoring which was a feature introduced in Oracle 11g. The main difference is related to the fact that SQL Monitoring only applies to a single SQL statement .
Very often we run batch jobs and those batch jobs in turn invoke many SQL statements. When batch jobs run slowly all of a sudden it becomes very difficult to identify which of those individual SQL statements which are part of the batch job are now contributing to the performance issue – or maybe batch jobs have started running slowly only after a database upgrade has been performed and we need to identify which particular SQL statement or statements have suffered from performance regressions after the upgrade.
The API used for Real-Time Database Monitoring is the DBMS_SQL_MONITOR package with the BEGIN_OPERATION and END_OPERATION calls.
So what is a Database Operation?
A database operation is single or multiple SQL statements and/or PL/SQL blocks between two points in time.
Basically to monitor a database operation it needs to be given a name along with a begin and end point.
The database operation name along with its execution ID will help us identify the operation and we can use several views for this purpose like V$SQL_MONITOR as well as V$ACTIVE_SESSION_HISTORY via the DBOP_NAME and DBOP_EXEC_ID columns.
Let us look an example of monitoring database operations using Oracle 12c Database Express.
We create a file called mon.sql and will run it in the SH schema while using Database Express to monitor the operation.
The name of the database operation is DBOPS and we are running a number of SQL statements as part of the same database operation.
n := dbms_sql_monitor.begin_operation(‘DBOPS’);
drop table sales_copy;
CREATE TABLE SALES_COPY AS SELECT * FROM SALES;
INSERT INTO SALES_COPY SELECT * FROM SALES;
SELECT * FROM SALES ;
select * from sales where cust_id=1234;
select dbop_exec_id into m from v$sql_monitor
From the Database Express 12c Performance menu > Performance Hub > Monitored SQL
In this figure we can see that the DBOPS database operation is still running.
Click the DBOPS link in the ID column
We can see the various SQL statements which are running as part of the operation and we can also see that one particular SQL is taking much more database time as compared to the other 3 SQL ID’s.
The DELETE SALES_COPY SQL statement is taking over 30 seconds of database time as compared to other statements which are taking around just a second of database time in comparison. It is consuming close to 2 million buffer gets as well.
So we know that for this particular database operation, which is the most costly single SQL statement.
We can now see that the database operation is finally complete and it has taken 42 seconds of database time.