6.2 Monitoring Real-Time Database Operations Using EM Express

«« Previous
Next »»

1. Overview


Purpose

This tutorial covers how to monitor certain database operations in real-time using Oracle Database Enterprise Manager Database Express.

Time to Complete

Approximately 30 minutes

Introduction

A database operation is a set of database tasks defined by end users or application code. You can define, monitor, and report on database operations. Database operations are either simple or composite. A simple database operation is a single SQL statement or PL/SQL procedure or function. A composite database operation is activity between two defined points in time in a database session.

In general, monitoring database operations in real-time may be helpful for managing batch jobs, and for identifying expensive SQL statements when tuning the database. Real-time database operations monitoring is enabled by default when the STATISTICS_LEVEL initialization parameter is either set to TYPICAL (the default value) or ALL. Because database operations monitoring is a feature of the Oracle Database Tuning Pack, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING (the default value). Database operation monitoring automatically starts when a simple database operation runs in parallel or when it has consumed at least 5 seconds of CPU or I/O time in a single execution. You create a composite database operation by using procedures in the PL/SQL package DBMS_SQL_MONITOR.

Prerequisites

Before starting this tutorial, you should:
  • Have Oracle Database 12c installed. 
  • Have EM Database Express, and an Oracle non-CDB instance started.

2. Granting Users the Select Any Dictionary  Privilege


So that the HR and SH accounts can be used to query the data dictionary views and generate load, they need to be granted the SELECT ANY DICTIONARY system privilege.

1. Launch SQL*Plus and connect to the non-CDB database as SYSTEM.

. oraenv

sqlplus system/oracle@noncdb

2. Grant HR and SH the SELECT ANY DICTIONARY privilege. Unlock the accounts first if they are locked.

grant select any dictionary to hr, sh;

3. Starting the Database Operations


You will start 3 sets of database operations.
  • Starting the ORA.HR.select database operation
1. Launch a new terminal window, and start a database operation. Name the database operation ORA.HR.select. The database operation performs several SELECT statements as HR user. The database operation is started with the DBMS_MONITOR.BEGIN_OPERATION procedure.
. 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;
  • Starting the ORA.SYSTEM.select database operation
1. Launch a new terminal window, and start a database operation. Name the database operation ORA.SYSTEM.select. The database operation performs several SELECT statements as SYSTEM user. The database operation is started with the DBMS_MONITOR.BEGIN_OPERATION procedure.
. oraenv

sqlplus system/oracle@noncdb

VAR dbop_eid NUMBER; 

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

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

SELECT c.cust_id, c.cust_last_name, c.cust_first_name, s.prod_id, p.prod_name, s.time_id FROM sh.sales s, sh.customers c, sh.products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id ORDER BY c.cust_id, s.time_id;
  • Starting the ORA.SH.select2 database operation
1. Launch a new terminal window, and start a database operation. Name the database operation ORA.SH.select2. The database operation performs several SELECT statements as SH user. The database operation is started with the DBMS_MONITOR.BEGIN_OPERATION procedure.
. oraenv

sqlplus sh/sh@noncdb

VAR dbop_eid NUMBER; 

EXEC :dbop_eid := DBMS_SQL_MONITOR.BEGIN_OPERATION ('ORA.SH.select2', forced_tracking => 'Y')

SELECT c.cust_id, c.cust_last_name, c.cust_first_name, s.prod_id, p.prod_name, s.time_id FROM sh.sales s, sh.customers c, sh.products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id ORDER BY c.cust_id, s.time_id;


4. Monitoring the Database Operations


You will use Oracle Enterprise Manager Database Express to view the database operations started in the previous section.

1. Open a web browser, and launch Oracle Enterprise Manager Database Express. Connect as SYS (don't forget to check the AS SYSDBA box)

2. View the database operation currently executing using Enterprise Manager Database Express. From Enterprise Manager Database Express, click the “Performance” menu, then the “Performance Hub” option, then the “Monitored SQL” tab. The list of database operations appear.

Monitoring Real-Time Database Operations Using EM Express

5. Stopping the Database Operations


Stop the 3 database operations you started by returning to each of the terminal windows and use the DBMS_SQL_MONITOR.END_OPERATION procedure to end the operations.

1. Go to the terminal window where you launched the ORA.HR.select database operation, and stop it.

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

2. Go to the terminal window where you launched the ORA.SYSTEM.select database operation, and stop it.

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

3. Go to the terminal window where you launched the ORA.SH.select2 database operation, and stop it.

EXEC DBMS_SQL_MONITOR.END_OPERATION('ORA.SH.select2', :dbop_eid)


6. Resetting Your Environment


1. Revoke the SELECT ANY DICTIONARY privilege from HR and SH.

connect / as sysdba
revoke select any dictionary from hr, sh;

«« Previous
Next »»