Before You Begin

This 15-minute tutorial shows you how to monitor a database operation using Oracle Enterprise Manager Cloud Control (Cloud Control).

Background

Oracle Database operations are either simple or composite. A simple database operation is a single SQL statement or PL/SQL statement. Cloud Control automatically monitors a simple database operation when it runs in parallel, or when it has consumed at least 5 seconds of CPU time or I/O time in a single execution.

A composite database operation typically consists of a combination of multiple SQL and PL/SQL statements. You begin and end a composite database operation using the PL/SQL procedures DBMS_SQL_MONITOR.BEGIN_OPERATION and DBMS_SQL_MONITOR.END_OPERATION respectively. Only one composite database operation can run at a time in a database session.

Using Cloud Control, you can monitor database operations while they are executing, and view the details related to the time and resources consumed by them.

What Do You Need?

You must have the following software before you run this tutorial:

• Oracle Database 18c Enterprise Edition
• Oracle Enterprise Manager Cloud Control (Cloud Control) 13c Release 3
• An Oracle Database 18c instance configured in Cloud Control
• The following initialization parameters configured for the Oracle Database 18c instance:
  • STATISTICS_LEVEL initialization parameter set to TYPICAL (the default value) or ALL
  • CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter set to DIAGNOSTIC+TUNING (the default value)

Set Up the Tutorial Environment

1. Using SQL*Plus, connect to the Oracle Database 18c instance as the SYS user. This database instance should be already configured in Cloud Control.
2. Run the script setup.sql to set up the tutorial environment. The script takes a few seconds to complete and displays the following message on completion:
   
   ***** Environment Setup Completed *****
   
   Note:  Do not close the SQL*Plus window.

Open the Oracle Database 18c Instance Home Page in Cloud Control

1. Log in to Cloud Control.
2. Navigate to the home page of the Oracle Database 18c instance in which you had executed the setup.sql script.
   
   Note:  Do not close the Cloud Control window.

Start a Database Operation

Go to the SQL*Plus window and run the script db_operation.sql.

The db_operation.sql script starts a database operation having the name DBOP_EXAMPLE. The DBOP_EXAMPLE operation is a composite database operation, that is, it contains many SQL statements.

Note:  Do not wait for the db_operation.sql script to complete and go to the next step of the tutorial. Do not close the SQL*Plus window.

Monitor the Database Operation Using Cloud Control

1. Go to the Cloud Control window and from the Performance menu, select Performance Hub and then SQL Monitoring.

   

   Note: If the database login page is displayed, then log in as the SYS user.
   
   The Monitored SQL Execution page is displayed.
   
   The Monitored SQL Execution page lists all the SQL statements, PL/SQL statements, and composite database operations that are monitored by Cloud Control. You should also see the DBOP_EXAMPLE operation listed on top.
   

   

2. Click the DBOP_EXAMPLE link.
   
   The Monitored SQL Execution Details page for the DBOP_EXAMPLE operation is displayed. You may see a different information in Cloud Control than what is shown in the following image.
   

   

   The Overview section shows the general information, time and wait statistics, and IO statistics for the DBOP_EXAMPLE operation.
   
   The Details section shows two tabs - Activity tab and Metrics tab. The Activity tab is selected by default.
3. Click the Metrics tab.
   
   The Metrics tab shows various metrics for the DBOP_EXAMPLE operation, such as CPU used, Memory used, IO throughput, and IO requests.

   

4. Click the Activity tab.
   
   The Activity tab shows a chart containing the CPU time used by the various SQL statements of the DBOP_EXAMPLE operation.

   

5. In the Activity tab, click the SQL ID that is consuming the maximum CPU time, that is, click the largest rectangular area in the chart or click the corresponding SQL ID in the legend.
   
   The SQL Details page for the selected SQL ID is displayed.
   

   

   The Text section shows the SQL statement for the selected SQL ID.
   
   The Details section shows many tabs. The Statistics tab is selected by default, and it shows various statistics of the SQL statement, such as wait statistics, execution statistics, and shared cursors statistics as shown in the preceding image.
   
   The Activity tab shows the CPU usage by the SQL statement.

   

   The Plan tab shows the execution plan of the SQL statement.

   

Clean Up the Tutorial Environment

Go to the SQL*Plus window and run the script cleanup.sql.

The cleanup.sql script cleans up the tutorial environment and terminates the SQL*Plus session. The script takes a few seconds to complete and displays the following message on completion:

***** Environment Cleanup Completed *****

Want to Learn More?

Oracle Database 2 Day + Performance Tuning Guide