Monitoring Database Operations

We can monitor any long running jobs or batch operations.

Database operations are either simple or composite.

Simple Database Operation

A simple database operation is a single SQL statement or PL/SQL subprogram.
When the SQL Monitor feature is enabled, the database monitors simple database operations automatically when any of the following conditions is true:

A SQL statement or PL/SQL subprogram has consumed at least 5 seconds of CPU or I/O time in a single execution.
A SQL statement executes in parallel.
A SQL statement specifies the /*+ MONITOR */ hint.
The event sql_monitor specifies a list of SQL IDs for the statements to be monitored. 
For example, the following statement forces instance-level monitoring for SQL IDs 5hc07qvt8v737 and 9ht3ba3arrzt3:
ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true'

Composite Database Operation

A composite database operation is defined by the user. 
It includes the beginning and end points defined using the procedures DBMS_SQL_MONITOR.BEGIN_OPERATION and DBMS_SQL_MONITOR.END_OPERATION.

Oracle Database automatically monitors a composite operation when either of the following conditions is true:

The operation has consumed at least 5 seconds of CPU or I/O time.
Tracking for the operations is forced by setting FORCED_TRACKING to Y in DBMS_SQL_MONITOR.BEGIN_OPERATION.

After monitoring is initiated, the database stores metadata about the database operations in AWR, and the data in AWR and ASH.
 The database stores the operational data (the statements and metadata) in the SGA. 
 After an operation completes, the database writes the SQL Monitor report to disk,
 where it can be queried using the DBA_HIST_REPORTS view.

Every monitored database operation has an entry in the V$SQL_MONITOR view.
The V$SQL_PLAN_MONITOR view includes monitoring statistics for each operation in the execution plan of the SQL statement being monitored.


User Interfaces for Database Operations Monitoring(DBMS_SQL_MONITOR)

Real-Time SQL Monitoring is a feature of the Oracle Database Tuning Pack. 
Database operations are enabled when the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter is set to DIAGNOSTIC+TUNING (default).

DBMS_SQL_MONITOR Package
The DBMS_SQL_MONITOR package defines the beginning and ending of a composite database operation, and generates a report of the database operations.
We can monitor Operations with proper Intearctive Report using below function of DBMS_SQL_MONITOR package.

REPORT_SQL_MONITOR Function

MONITOR and NO_MONITOR Hints

You can use the MONITOR and NO_MONITOR hints to control tracking for individual statements.

The MONITOR hint forces real-time SQL monitoring for the query, even if the statement is not long-running.
This hint is valid only when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING. The following query forces SQL Monitor to enable tracking:

SELECT /*+ MONITOR */  prod_id, AVG(amount_sold), AVG(quantity_sold)
FROM   sales GROUP BY prod_id ORDER BY prod_id;

The NO_MONITOR hint disables real-time SQL monitoring for the query, even if the query is long running. The following query forces SQL Monitor to disable tracking:

SELECT /*+ NO_MONITOR */  prod_id, AVG(amount_sold), AVG(quantity_sold)
FROM   sales GROUP BY prod_id ORDER BY prod_id;


DEMO - To Monitor Long running operations using V$ Views

Let we Monitor execution operation of QUERY which takes more than 5 seconds as mentioned above.

We will be monitoring the same using V$SQL_PLAN_MONITOR view.

Connect to sh schema and execute Long running session as below,

[oracle@ace2oracledb ~]$ sqlplus sh/sh

SQL> SELECT /* demmo */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id;

Now let we have another session with privileged user and execute below Query for Monitoring .
We need to wait for 5 seconds

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;

       SID SQL_ID	 STATUS      LINE OPERATION							      ROWS
---------- ------------- ---------- ----- ------------------------------------------------------------- ----------
	41 9spj2a53qaf5y EXECUTING	0 SELECT STATEMENT							 0
					1 SORT ORDER BY 							 0
					2 MERGE JOIN CARTESIAN						  11580525
					3 TABLE ACCESS FULL							13
					4 BUFFER SORT							  11580525
					5 PARTITION RANGE ALL						    918843
					6 BITMAP CONVERSION TO ROWIDS					    918843
					7 BITMAP INDEX FAST FULL SCAN					      1074

8 rows selected.

SQL> /

       SID SQL_ID	 STATUS      LINE OPERATION							      ROWS
---------- ------------- ---------- ----- ------------------------------------------------------------- ----------
	41 7xh657juuggjy EXECUTING	0 SELECT STATEMENT							 0
					1 SORT ORDER BY 							 0
					2 MERGE JOIN CARTESIAN						   7808181
					3 TABLE ACCESS FULL							 9
					4 BUFFER SORT							   7808181
					5 PARTITION RANGE ALL						    918843
					6 BITMAP CONVERSION TO ROWIDS					    918843
					7 BITMAP INDEX FAST FULL SCAN					      1074
8 rows selected.

DEMO - Reporting on a Simple Database Operation: 

We also can generate TEXT / HTML report for above operations.

For that we need to use DBMS_SQL_MONITOR.REPORT_SQL_MONITOR as below,

Login from SH and execute long running session as below.

[oracle@ace2oracledb ~]$ sqlplus sh/sh

SQL> SELECT /* demmo */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id;

Let this run.

Now login to another Duplicate session and execute below task and there we will get details,

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
VARIABLE my_rept CLOB;
BEGIN
:my_rept := DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(report_level  => 'ALL', TYPE => 'text');
END;
/
PRINT :my_rept

SQL Monitoring Report

SQL Text
------------------------------
SELECT /* demmo */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id

Global Information
------------------------------
 Status 	     :	EXECUTING
 Instance ID	     :	1
 Session	     :	SH (80:35749)
 SQL ID 	     :	9spj2a53qaf5y
 SQL Execution ID    :	16777217
 Execution Started   :	02/19/2022 22:38:06
 First Refresh Time  :	02/19/2022 22:38:32
 Last Refresh Time   :	02/19/2022 22:38:44
 Duration	     :	52s
 Module/Action	     :	SQL*Plus/-
 Service	     :	SYS$USERS
 Program	     :	sqlplus@ace2oracledb.vbox.lab (TNS V1-V3)

Global Stats
====================================================================
| Elapsed |   Cpu   |	 IO    |  Other   | Buffer | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs  | Bytes |
====================================================================
|      37 |    4.77 |	    32 |     0.23 |    101 |   876 | 185MB |
====================================================================

SQL Plan Monitoring Details (Plan Hash Value=2036849021)
====================================================================================================================================================================================
| Id   |	     Operation		    |	   Name      |	Rows   | Cost |   Time	  | Start  | Execs |   Rows   | Write | Write |  Mem  | Temp  | Activity | Activity Detail |
|      |				    |		     | (Estim) |      | Active(s) | Active |	   | (Actual) | Reqs  | Bytes |       |       |   (%)	 |   (# samples)   |
====================================================================================================================================================================================
| -> 0 | SELECT STATEMENT		    |		     |	       |      |        13 |    +26 |	 1 |	    0 |       |       |     . |     . | 	 |		   |
| -> 1 |   SORT ORDER BY		    |		     |	   51G | 316M |        50 |	+2 |	 1 |	    0 |   876 | 185MB | 249KB | 186MB | 	 |		   |
|    2 |    MERGE JOIN CARTESIAN	    |		     |	   51G |   2M |        24 |    +15 |	 1 |	  12M |       |       |     . |     . | 	 |		   |
| -> 3 |     TABLE ACCESS FULL		    | CUSTOMERS      |	 55500 |  423 |        13 |    +26 |	 1 |	   13 |       |       |     . |     . | 	 |		   |
|    4 |     BUFFER SORT		    |		     |	  919K | 316M |        38 |	+1 |	13 |	  12M |       |       |  28MB |     . | 	 |		   |
|    5 |      PARTITION RANGE ALL	    |		     |	  919K |   29 | 	1 |    +26 |	 1 |	 919K |       |       |     . |     . | 	 |		   |
|    6 |       BITMAP CONVERSION TO ROWIDS  |		     |	  919K |   29 | 	1 |    +26 |	28 |	 919K |       |       |     . |     . | 	 |		   |
|    7 |	BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX |	       |      | 	1 |    +26 |	28 |	 1074 |       |       |     . |     . | 	 |		   |
====================================================================================================================================================================================


SQL> 
We also can generate same report in HTML format as below.
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SPOOL /tmp/reo.html
VARIABLE my_rept CLOB;
BEGIN
  :my_rept := DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
                report_level => 'ALL',
                TYPE         => 'HTML');
END;
/
PRINT :my_rept


Let we open /tmp/reo.html file.

Views for Monitoring sessions

DBA_HIST_REPORTS
DBA_HIST_REPORTS_DETAILS
V$SQL_MONITOR
V$SQL_MONITOR_SESSTAT
V$SQL_PLAN_MONITOR
V$ACTIVE_SESSION_HISTORY
V$SESSION
V$SESSION_LONGOPS
V$SQL
V$SQL_PLAN