1.9 Monitoring and Optimizing Database Performance


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

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

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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

./setup_workload.sh <sys_passwd> <service_name>

Monitoring and Optimizing Database Performance

4. Start the workload by using the following command:

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

Monitoring and Optimizing Database Performance

5. The script is executed successfully.

Monitoring and Optimizing Database Performance

  • Using the Performance Hub
1. Launch Enterprise Manager Database Express 12c. Login as the SYSTEM user.

Monitoring and Optimizing Database Performance

2. Select Performance Hub in the Performance menu.

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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.

Monitoring and Optimizing Database Performance

7. Click the Activity tab.

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

9. Click the Wait Class drop-down menu.

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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.

Monitoring and Optimizing Database Performance

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.

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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.

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

17. Click the Workload tab.

Monitoring and Optimizing Database Performance

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.

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

21. Click Open Cursors to see cursor activity. 

Monitoring and Optimizing Database Performance

Monitoring and Optimizing Database Performance

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.

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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. 

Monitoring and Optimizing Database Performance

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

Monitoring and Optimizing Database Performance

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.

Monitoring and Optimizing Database Performance

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