6.1 Monitoring Performance Using EM Express

«« Previous
Next »»

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

2. Generate Workload on the Database


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

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

3. Start the workload generator.

./home/oracle/emexp/start_load.sh

4. Verify the workload generator is running.

ps -eaf | grep frame

You should see output similar to the following:

Monitoring Performance Using EM Express

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.

3. Access EM Express 


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

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

Monitoring Performance Using EM Express

3. Select the Performance menu > Performance Hub option.

Monitoring Performance Using EM Express

4. Change the Time Window of Interest


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

Monitoring Performance Using EM Express

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 Performance Using EM Express

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

Monitoring Performance Using EM Express

5. Explore the Activity Tab for a Time Window


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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express


Monitoring Performance Using EM Express

6. Explore the Workload Tab for a Time Window


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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

3. You should see Hard Parses and overall Parse Counts.

Monitoring Performance Using EM Express

4. Click Redo Size.

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

6. Click Open Cursors to see cursor activity.

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

8. Click the SQL ID with the most activity.

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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


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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

8. Explore the Tablespace Storage Information Page


1. Click the Storage tab, and then select Tablespaces.

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

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

Monitoring Performance Using EM Express

4. All tablespaces and their datafiles are now visible.

Monitoring Performance Using EM Express

5. Click View > Flatten.

Monitoring Performance Using EM Express

6. You should now see only the datafiles and index files.

Monitoring Performance Using EM Express

9. Stop Workload Generation on the Database


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

2. Stop the workload generator.

./home/oracle/emexp/stop_load.sh

3. Verify the workload generator is no longer running.

ps -eaf | grep frame

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

«« Previous
Next »»