Improving RAC Database Tuning with ADDM for RAC

Purpose

This tutorial describes how to use ADDM in a RAC environment to identify database level issues, and drill down to affected database instances.

Time to Complete

Approximately 40 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Using ADDM for RAC
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Oracle Database 11g offers an extension to the set of functionality that increases the database’s manageability by offering clusterwide analysis of performance. A special mode of Automatic Database Diagnostic Monitor (ADDM) analyzes an Oracle Real Application Clusters (RAC) database cluster and reports on issues that are affecting the entire cluster as well as on those that are affecting individual instances. This mode is called database ADDM as opposed to instance ADDM, which already existed with Oracle Database 10g. Database ADDM for RAC is not just a report of reports but has independent analysis that is appropriate for RAC.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Clusterware 11g, Oracle Database 11g, and create a two-node RAC database.

2.

Download and unzip the rac_addm.zip file into your working directory (that is, wkdir) and navigate into your working directory.

Back to Topic List

Using ADDM for RAC

Database ADDM has access to AWR data generated by all instances, thereby making the analysis of global resources more accurate. Both database and instance ADDM run on continuous time periods that can contain instance startup and shutdown. In the case of database ADDM, there may be several instances that are shut down or started during the analysis period. You must maintain the same database version throughout the entire time period, however. Database ADDM runs automatically after each snapshot is taken. The automatic instance ADDM runs are the same as in Oracle Database 10g. You can also perform analysis on a subset of instances in the cluster. This is called partial analysis ADDM. The following scenario illustrates the drill-down capability of ADDM in a RAC environment.

Perform the following steps to understand the use of ADDM for RAC:

 

1.

Open your browser window, and log on to Enterprise Manager as user SYS. Once on the Cluster Database Home page, click the Performance tab.

 

2.

Open a terminal window as user oracle and execute the addm_rac_setup.sh script. This script creates a user a tablespace and a table that are used during this tutorial.

./addm_rac_setup.sh

 

3.

From the same terminal session, execute the run_workload.sh script. This script starts inserting rows into a previously created table. Wait until the script finishes its execution. You have to press "return" after you see the second "PL/SQL procedure successfully completed" message.

                               
                                 
./run_workload.sh
                              
                            

 

4.

Go back to your Enterprise Manager session and observe the Average Active Sessions graph. You should see high waits on the Concurrency and Cluster wait classes.

 

5.

After your workload finished its execution, execute the create_snapshot.sh script. This script generates a new AWR snapshot to force an ADDM analysis.

./create_snapshot.sh

 

6.

Back to your Enterprise Manager session, click the Home tab to return to the Cluster Database Home page.

 

7.

Wait few minutes on the Home page until the ADDM Findings link appears in the Diagnostic Summary section. Once there, click the number in front of the ADDM Findings field.

 

8.

Once on Automatic Database Diagnostic Monitor page, click the Top SQL by DB Time link at the top of the report analysis. This finding is a global finding.

On the Top SQL by DB Time page, click the Show All Details link.

You should see INSERT statements using a common sequence object used from both instances.

Once done, click the ADDM breadcrumb link at the top of the page.

 

9.

Back to the Automatic Database Diagnostic Monitor page, click the Sequence Usage link at the top of the report analysis. It shows that both instances are affected by this finding. ADDM tells you to look at top SQL statements to investigate hot sequences. Once done, click the ADDM breadcrumb link at the top of the page. This information shows that the previously seen sequence is probably the issue.

 

10.

Drill down to the first instance by clicking the racdb_racdb1 link in the Affected Instances table.

Click the Top SQL by DB Time link in the Finding column.

Click the Show All Details link in the Recommendations section.

You should corroborate the global information seen previously with the ones you can see at the instance level.

Once done, click the ADDM breadcrumb link to go back at the top level of the ADDM report.

 

11.

Go back to the Performance page by clicking the Database tab and then Performance tab.

 

 

12.

Back to your terminal session, execute the addm_rac_fix.sh script. This script recreate the culprit sequence using a much bigger CACHE value.

 

13.

Execute the create_snapshot.sh script. This script generates a new AWR snapshot.

./create_snapshot.sh

 

14.

Once done, execute again the run_workload.sh script, and wait for it to finish. You have to press "return" after you see the second "PL/SQL procedure successfully completed" message.

 

15.

Go back to your Enterprise Manager session and observe the Average Active Sessions graph. You should no longer see high waits on the Concurrency and Cluster wait classes.

 

16.

After your workload finished its execution, execute the create_snapshot.sh script. This script generates a new AWR snapshot to force a new ADDM analysis.

./create_snapshot.sh

 

17.

Back to your Enterprise Manager session, click the latest Snapshot icon underneath the Average Active Sessions graph. This directs you directly to the latest ADDM report.

 

18.

On the ADDM report page, you should no longer see any issue with your sequence.

 

Back to Topic Lis t

Summary

In this tutorial, you learned how to use ADDM for RAC.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document