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 mins

    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 software requirements:

    • Oracle Database 12c

    Prerequisites

    Before starting this tutorial, you should:

    • Install the sample schemas
    • Complete the other tutorials in this series

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.

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

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

      Run the setup script by using the following command:

      ./setup_workload.sh <sys_passwd> <service_name>

      Start the workload by using the following command:

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

      The script is executed successfully.

    Using the Performance Hub

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

      Select Performance Hub in the Performance menu.

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

      Click the drop-down menu to view the options.  

      Select Real Time - Last Hour and click OK.

      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. Note: While capturing screenshots for this tutorial, an error was encountered. Hence, you see a ADDM finding. You may or may not see this tab.

      Click the Activity tab.

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

      Click the Wait Class drop-down menu.

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

      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.

      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.

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

      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.

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

      Click both X’s to remove the filter conditions.

      Click the Workload tab.

      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 see Hard Parses and overall Parse Counts. Click Redo Size.

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

      Click Open Cursors to see cursor activity. 


      In the Top SQL section, you 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. Now click the Activity tab again.

      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.

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

      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.

      Click ORCL to return to the database home page.

Summary

    In this tutorial, you learned how to:

    • Use the performance hub

    Resources

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

    Credits

    • Lead Curriculum Developer: Salome Clement
    • Other Contributors: Bert Rich

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.