12c Database : Monitor Database operations using EM Express

Previous to 12c, when you want to perform an monitoring the Session you will have to turn the trace etc or trace with application/module/program/sql_id etc.

But, what if you want to monitor specific operation for that session not whole or application. Kind of set of operations you want to peform not all with in that application/module/sql_id etc.

From 12c, You can do that using dbms_SQL_MONITOR.begin_operation.

Let's have a look,

Connect to the sample schema and begin the monitoring operation.

    . oraenv

     sqlplus hr/hr@noncdb

     VAR dbop_eid NUMBER;

     EXEC :dbop_eid := DBMS_SQL_MONITOR.BEGIN_OPERATION ('ORA.HR.select', forced_tracking => 'Y')

     select a.employee_id, b.employee_id from hr.employees a, hr.employees b;

     select * from hr.departments;

     select a.table_name , b.table_name FROM dict a, dict b;

Connect     to EM Express using https://dbhost:5502/em

Note: EM Express is a light weight performance monitoring replaced the db console.  Its in built with database engine and looks similar like 12c cloud control pages

Once connected, Go to Performance --> SQL Monitor --> And observe you operation

As I turned on the Begin operation this was showing me the status still running.

Once I stopped the operation using below, the console shows the operation completes.

This work has been determined as composite database operation.

EXEC DBMS_SQL_MONITOR.END_OPERATION('ORA.HR.select', :dbop_eid)

-Thanks

Geek DBA