Improving RAC Database Tuning with ADDM for RAC
Improving RAC Database Tuning with ADDM for RAC
This tutorial describes how to use ADDM in a RAC environment
to identify database level issues, and drill down to affected database instances.
Approximately 40 minutes
This tutorial covers the following topics:
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.
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
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
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 List
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.
|
Bookmark
