Monitoring Real-Time Database Operations Using EM Database Express

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.

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.

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

    . oraenv

    sqlplus system/oracle@noncdb

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

    grant select any dictionary to hr, sh;

Starting the Database Operations

    You will start 3 sets of database operations.

    Starting the ORA.HR.select database operation

      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

      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

      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;

Monitoring the Database Operations

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

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

    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.

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.

    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)

    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)

    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)

Resetting Your Environment

    Revoke the SELECT ANY DICTIONARY privilege from HR and SH.

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

Summary

    In this tutorial, you have learned how to:

    • Start composite database operations
    • Monitor database operations in real-time using Enterprise Manager Database Express
    • End composite database operations

    Resources

    Credits

    • Lead Curriculum Developer: Dominique Jeunot

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.