(10393) Database Performance Tuning Hands-On Lab - Oracle

[Pages:24](10393) Database Performance Tuning Hands-On Lab

ASH Analytics Real-time ADDM SQL Performance Analyzer

Copyright ? 2012 Oracle Corporation. All Rights Reserved.

ORACLE ENTERPRISE MANAGER Cloud Control 12c

Database Management Lab

Database Performance Hands-on Lab

Objective: The objective of this lab to provide exercises designed to showcase new database performance management and data management capabilities in Oracle Enterprise Manager Cloud Control 12c.

Functional Coverage: In this lab you will go through important features in the following functional areas: A. Database Performance management

A1. ASH Analytics A2. Real Time ADDM A3. SQL Performance Analyzer

Lab environment setup

This lab will have a workload started for you. If you don't see a workload running, please ask the system administrator to make sure to start the workload.

NOTE: The graphs you see in this workbook may not match those on your screen.

Copyright ? 2012 Oracle Corporation. All Rights Reserved.

Page 2 of 24

ORACLE ENTERPRISE MANAGER Cloud Control 12c

Database Management Lab

A1. ASH Analytics

Estimated Time to Complete Use Case: 15 minutes

Business Case Oracle Enterprise Manager 12c introduces ASH Analytics, a new tool to explore the ASH (Active Session History) data that allows the administrator to rollup, drilldown, and slice or dice performance data across various performance dimensions. With the ability to create filters on various dimensions, identifying performance issues has never been easier. The built-in Loadmap view allows administrators to explore performance data using predefined performance dimension hierarchies.

2. Understanding the ASH Analytics page

1.1

Log on to Enterprise Manager using login -> dbuser / oracle12.

1.2

Navigate to prod database Ash Analytics page via menu Targets Databases

Choose -> prod. as your database. Copyright ? 2012 Oracle Corporation. All Rights Reserved.

Page 3 of 24

ORACLE ENTERPRISE MANAGER Cloud Control 12c

Database Management Lab Lab

Navigate to `ASH Analytics' page from the specific database page via `Performance' `ASH Analytics'

1.3

On the Database login page, for Credential select `Named', wait for login credentials to auto-populate with

Credential Name being DB-SYS-GLOBAL and click Login button.

Tip: If you have already logged to this database earlier, you will not be shown this page.

Copyright ? 2012 Oracle Corporation. All Rights Reserved.

Page 4 of 24

ORACLE ENTERPRISE MANAGER Cloud Control 12c

Database Management Lab

1.4

Shown below is the ASH Analytics Home Page. Notice the followLainbg:

1.4.1 Select the Hour Time Period for investigation: Use the slider at the top of the chart to select a time period which has a high workload. The graph below the time window is a close-up detail of the time window's time period. Click on Day or Week period to view different levels across days then choose Hour.

1.6 The detail graph's drop-down menu defaults to Wait Class. The graph's legend on the right always matches the drop-down menu on the upper left.

Copyright ? 2012 Oracle Corporation. All Rights Reserved.

Page 5 of 24

ORACLE ENTERPRISE MANAGER Cloud Control 12c

1.7 Review the different Wait Classes:

Database Management Lab Lab

1.7.1 1.7.2

In the 2nd chart from the top, observe the detailed view of the time window that has been selected. By default the wait class dimension is selected. Observe the different wait classes for the window selected (blue for USER_IO, green for CPU etc.)

1.8

Become familiar with the different dimensions: For example, change the dimension from Wait Class to SQL

ID.

From the drop down, choose Top Dimensions -> SQL ID. Observe the change in the chart. The use of different performance dimensions allows the user to get a perspective of what is currently running and how the system resources are being utilized.

Copyright ? 2012 Oracle Corporation. All Rights Reserved.

Page 6 of 24

ORACLE ENTERPRISE MANAGER Cloud Control 12c

Database Management Lab Lab

1.9

Let's examine the different filtering capabilities of ASH Analytics:

1.9.1 Select Service as the Top Dimension. From the drop down, select Top Dimensions -> Service

1.9.2 Click on the chart for the service `prod.' and set it as a filter.

Note: Depending on the workload characteristics and time period chosen you might not see the exactly the same services. If you don't find prod. then choose the service with biggest block of color in the graph.

Copyright ? 2012 Oracle Corporation. All Rights Reserved.

Page 7 of 24

ORACLE ENTERPRISE MANAGER Cloud Control 12c

Database Management Lab Lab

1.9.3 Once the filter is set, it will be indicated next to the Filters label. This filtered view shows the wait classes view for the specified service.

1.9.4 Observe how view changes with filter. Let`s see how other dimensions are affected by the same filter. With the filter enabled, change the dimension from "Wait Class" to "Module".

From the drop-down select Top Dimension Module.

Copyright ? 2012 Oracle Corporation. All Rights Reserved.

Page 8 of 24

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download