Monitoring Performance Using Enterprise Manager Database Express

Overview

    Purpose

    This tutorial covers how to monitor database performance using Enterprise Manager Database Express (also referred to in this tutorial as EM Express).

    Time to Complete

    Approximately 15 minutes

    Introduction

    Enterprise Manager Database Express is a new feature in Oracle Database 12c. It ships with the database and replaces the former Enterprise Manager Database Control product. It does not require a separate installation, and provides a lightweight way to perform common analysis and actions in an intuitive graphical environment instead of using command lines.

    Prerequisites

    Before starting this tutorial, you should:

    • Have Oracle Database 12c installed.
    • Have workload generation software installed.

Generate Workload on the Database

    Edit /u01/software/frame/bin/start.sh. Change /home/oracle to /home/oracle/emexp. Change ./db12c.env to ./noncdb.env.

    Edit $ORACLE_HOME/network/admin/tnsnames.ora. Remove the database domain name (example.com) from all of the SERVICE_NAME parameters.

    Start the workload generator.

    ./home/oracle/emexp/start_load.sh

    Verify the workload generator is running.

    ps -eaf | grep frame

    You should see output similar to the following:

    To have more interesting statistics to examine, the workload generator should run for at least 5 to 10 minutes before you begin the next topic in this tutorial. Note that the graphs you see in the screenshots may not match exactly what you see on your screen.

Access EM Express

    Open the browser, and click the noncdb EM Express bookmark. You can also type in the URL http://localhost:5555/em.

    Login with username sys, password oracle, and check the "as sysdba" checkbox.

    Select the Performance menu > Performance Hub option.

Change the Time Window of Interest

    On the Performance Hub page, you should see a time window slider at the top right. Use the handles on the left and right edges of the window to pull the window over to an interesting time period of activity. Notice the grayed areas change in the charts to match the window slider.

    Use the handles on the left and right edges of the window to pull the window over to an interesting time period of activity.

    Notice the grayed areas change in the charts to match the window slider.

Explore the Activity Tab for a Time Window

    Click the Activity tab to see details about the activity that occured during the selected time window.

    Click Wait Class > Session Identifiers > User ID to see the user IDs responsible for the workload.

    Place the cursor on the largest area in the graph. It will turn the larget User ID workload into a highlighted yellow. Click to make this User ID a filter condition.

    You should now see that User ID's workload during the time window period. Other workloads will not be shown and will appear as blank space in the graphs. That allows you to focus on that single user. In this case the entire wait class for that user is CPU.

    Click the Wait Class drop-down list, and select Top Dimentions > Module to isolate the moduels the user was running.

    The modules the user was running are listed on the right, in the graph's legend. Click on the module with the largest area represented in the graph.

    Module now becomes a second filter condition, and it shows that module waited mostly on CPU.

    Remove the filter conditions. Click on the X on the right of each condition.


Explore the Workload Tab for a Time Window

    Click the Workload tab to see details about the workloads that occurred during the selected time window. The Workload tab has Workload Profile, Sessions, and Top SQL sections.

    In the Workload Profile section, click Parse Calls to see graphically how parse calls behaved during the time window.

    You should see Hard Parses and overall Parse Counts.

    Click Redo Size.

    You can see redo activity during, before, and after the time window.

    Click Open Cursors to see cursor activity.

    In the Top SQL section we see the SQL IDs ranked from most to least activity.

    Click the SQL ID with the most activity.

    You can examine particulars about that SQL, including Database Time, IO Bytes, and Buffer Gets.

Explore the Activity Tab for a Specific SQL ID Within a Time Window

    Click the Activity tab to see details for the specific SQL ID in the selected time window.

    The context of the tabs is now centered on the specific SQL ID. You see that the SQL ID is now a filter condition, but this filter cannot be deleted because all the tabs now describe activity for that specific SQL ID.

    Wait Class is the default activity displayed for this SQL ID. To see the user, click on the Wait Class drop-down list, then select Session Identifiers > User ID.

    You can see details for this user's SQL ID in the Activity and the time window graph. Activity for this specific SQL ID is highlighted.

    Activity not related to this SQL ID is the blank area under the curves.

Explore the Tablespace Storage Information Page

    Click the Storage tab, and then select Tablespaces.

    You can check storage statistics for tablespaces such as Temp and Undo. These statistics are important for workload analysis.

    Click View > Expand All to see all the tablespaces plus all of the datafiles in each tablespace.

    All tablespaces and their datafiles are now visible.

    Click View > Flatten.

    You should now see only the datafiles and index files.

Stop Workload Generation on the Database

    Edit /home/oracle/emexp. Change $HOME to $HOME/emexp. Change ./db12c.env to ./noncdb.env.

    Stop the workload generator.

    ./home/oracle/emexp/stop_load.sh

    Verify the workload generator is no longer running.

    ps -eaf | grep frame

    You should see no other result other than the "ps -eaf".

Summary

    In this tutorial, you have learned how to:

    • Navigate the Performance Hub pages on EM Database Express.
    • Navigate the Tablespace Storage page on EM Database Express.

    Resources

    • To learn more about Database 12c refer to additional OBEs in the Oracle Learning Library <can link to a specific set of OBEs on the subject or product(s)>

    Credits

    • Author: Deba Chatterjee

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.