ADDM Enhancements in Oracle Database 11g
October 31, 2008
Synopsis. Oracle Database 10g introduced the Automatic Database Diagnostic Monitor (ADDM) feature set as part of the Diagnostic Pack, and its proved to be a flexible scalpel for pruning away nonrelated issues during both reactive and proactive instance performance tuning. This article illustrates how Oracle Database 11gR1 has expanded ADDM to encompass Real Application Clusters (RAC) database tuning at the global level and provides the ability to filter out previously identified performance issues from any ADDM analysis report.
As Ive travelled around the US and talked to more and more DBAs that have transitioned at least a few of the Oracle databases to Release 10gR2, Ive found that ADDM has become an essential tool on their DBA tool belts for quickly identifying and isolating performance issues with any Oracle 10g database instance, no matter if its part of a single-instance database or a Real Application Cluster (RAC) database. However, many of the most stalwart ADDM converts have complained that what they really wished for was the ability for ADDM to analyze the complete performance spectrum of a RAC clustered database.
Database ADDM: The View from The (RAC) Mountaintop
The good news is that Oracle Database 11g now offers what it calls database ADDM, or the ability to run ADDM reports at the overall clustered database level as well as at the individual database instance level. Whenever a new Automatic Workload Repository (AWR) snapshot is requested, or whenever an ADDM analysis has been executed against a set of existing AWR snapshots, ADDM first analyzes each individual RAC instances performance (aka instance ADDM) and then immediately performs an ADDM database analysis.
Figure 1 shows the results in Oracle 11gs Enterprise Manager Database Control Advisor Central panel from a recent ADDM analysis performed against a two-node RAC clustered database (RACDB). Note that a separate analysis is also shown for each of the two database instances (RACDB1 and RACDB2).
Figure 2 shows the results of drilling down into the database ADDM report itself. Note that Oracle 11g now provides a much more succinct breakdown of each finding; in addition, the total number of instances that are affected by each performance finding is also summarized in RAC database analysis mode:
Finally, Figure 3 shows how Oracle 11g presents a set of findings in database ADDM mode. From this point, the DBA can navigate to instance-specific findings:
Even better, its also now possible to run an ADDM performance analysis for a subset of RAC instances in whats called partial analysis ADDM mode. This is extremely useful, especially when, say, only two or three instances of a RAC clustered database are exhibiting performance issues because a particular RAC database service is designated as the preferred instance for those instances.
So how does Oracle 11gR1 provide all these analysis capabilities? It uses a brand-new package, DBMS_ADDM that encapsulates all ADDM functionality. Ive presented the list of DBMS_ADDMs analysis procedures in Table 1.
Ive provided samples of how to execute these three types of analyses in Listing 1 as well as how to use GET_REPORT to generate a report. Report 1 shows an example of a database ADDM analysis for ADDM task ADDM_100, while Report 2 shows a sample of the report from an instance-specific report for ADDM task ADDM_110.
Filtering ADDM Findings
Theres no question that ADDM analyses are quite exhaustive; however, Ive sometimes wished I could repress certain findings that I certainly expected to find within an analysis. For example, I know that there will be intensive DML generated against the AP.INVOICES and AP.INVOICE_ITEMS tables as part of the sample workload Ive analyzed. It would make my review of the resulting ADDM database and instance analyses that much simpler if I could filter out any findings for SQL statements that access these segments so that I can concentrate on unexpected DML or queries.
Oracle 11gs new DBMS_ADDM package now gives me just this capability: Its now possible to add a finding directive to one or more ADDM tasks. Whenever those task(s) are re-executed, those findings will be simply filtered from the resulting ADDM report(s). ADDM finding filters can be generated within Oracle 11g Enterprise Manager Database Control, as shown in Figure 4 and Figure 5 below:
Of course, behind the scenes its really the DBMS_ADDM packaged procedures that are handling the majority of the work. Table 2 lists the various procedures that Oracle 11g Enterprise Manager Database Control calls to create, edit, and remove ADDM findings.
Ive demonstrated how these procedures can be used to effectively filter out any set of previously-identified performance issues for the same instance-level ADDM analysis task (ADDM_110) in Listing 2. The resulting edited report output in Report 3 shows how ADDM applied the selected filters against any matching findings, initialization parameters, segments, and/or SQL statements during the re-execution of ADDM task ADDM_110.
ADDM Findings Metadata. A new column, FILTERED, has been added to several DBA_ADVISOR_* views. This indicator will display (Y)es when a finding has been filtered out of an ADDM report through a specific Advisor finding directive. In addition, Oracle 11g adds the new DBA_ADVISOR_FINDINGS data dictionary view that shows all currently active finding directives. Ive supplied a sample SQL*Plus formatted query against this view in Listing 3.
Removing ADDM Findings Filters and ADDM Tasks. Finally, Ive demonstrated how to remove existing ADDM findings and tasks in Listing 4.
Oracle Database 11gs expanded Automatic Database Diagnostic Monitor features significantly extend an Oracle DBAs reach into much more detailed performance analyses, including the ability to filter out sets of previously-identified issues from a later ADDM run. The addition of ADDM reporting at an overall cluster database level for RAC clustered databases is a welcome and overdue feature that provides almost instantaneous insight into exactly what are the root causes of unexpectedly poor performance in a clustered database.
References and Additional Reading
While Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that Ive drawn upon the following Oracle Database 11gR1 documentation for the deeper technical details of this article:
B28254-06 Oracle Database 11gR1 Real Application Clusters Administration and Deployment Guide
B28274-01 Oracle Database 11gR1 Performance Tuning Guide
B28279-02 Oracle Database 11gR1 New Features Guide
B28320-01 Oracle Database 11gR1 Reference Guide
B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference