Monitoring and Optimizing Database Performance

Overview

Purpose

In this tutorial, you monitor your database using Oracle Enterprise Manager Database Express.

Time to Complete

Approximately 30 Minutes

Introduction

This tutorial shows you how to perform some of the monitoring operations using Enterprise Manager Database Express.

Software Requirements

The following is a list of hardware and software requirements:

  • Oracle Database 12c

Prerequisites

Before starting this tutorial, you should:

  • Install the sample schemas

Monitoring Performance Using the Performance Hub

The Database Home page enables you to monitor the general state and workload of the database. In this section, you monitor the performance of a database using the Performance Hub.

Generating Some Database Activity

In this topic, you connect to the target database as the SYS user and run a script to generate some activity in your database. Unzip this file to a working directory.

  1. Open a terminal window. Execute the oraenv utility to set the environment variables for the orcl database. 

  2. Navigate to the location where you have unzipped the workload_12c.zip file.

  3. Run the setup script by using the following command:

    ./setup_workload.sh <sys_passwd> <service_name>

  4. Start the workload by using the following command:

    ./start_workload.sh <load_factor> <servicename> <sys_passwd>

  5. The script is executed successfully.

Using the Performance Hub

  1. Launch Enterprise Manager Database Express 12c. Login as the SYSTEM user.

  2. Select Performance Hub in the Performance menu.

  3. Real Time data collected in the last hour is displayed. Click Select Time Period to view the other options available.

  4. Click the drop-down menu to view the options.  

  5. Select Real Time - Last Hour and click OK.

  6. You will see a time window slider in the Time Picker area. Use the handles on the left and right edges of the window to pull the window over to an interesting time period of activity.

  7. Click the Activity tab.

  8. The Activity tab provides details about the activity that happened during the selected time window.

  9. Click the Wait Class drop-down menu.

  10. Click Session Identifiers and select User ID to see the user IDs responsible for the workload.

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

  12. You will see the selected User ID’s workload during the time window period. Other workloads will not be shown and will appear as blank space in the graphs. This allows you to focus on a single user. In this case, the entire wait class for the selected user is CPU.

  13. Click the Wait Class drop-down arrow and then select Top Dimensions -> Module to isolate the modules the user was running.

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

  15. Module now becomes a 2nd filter condition and it shows that the module waited mostly on CPU.

  16. Click both X’s to remove the filter conditions.

  17. Click the Workload tab.

  18. 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.

  19. You see Hard Parses and overall Parse Counts. Click Redo Size.

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

  21. Click Open Cursors to see cursor activity. 


  22. In the Top SQL section, you see the SQL IDs ranked from most to least activity. Click the SQL ID with the most activity.

  23. You can examine particulars about that SQL, including Database Time, IO Bytes and Buffer Gets. Now click the Activity tab again.

  24. The context of the tabs is now centered on that 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.

  25. To see the user, click on the Wait Class down-arrow and then select Session Identifiers -> User ID

  26. You 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.

  27. Click ORCL to return to the database home page.

Summary

In this tutorial, you learned to:

  • Use the performance hub

Resources

  • Oracle Database 2 Day DBA 12c Release 2 (12.1)

Credits

  • Lead Curriculum Developer: Veerabhadra Rao Putrevu
  • Original Curriculum Developer : Salome Clement
  • Other Contributors: Bert Rich

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

Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

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