As I’ve 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, I’ve 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 it’s 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 instance’s performance (aka instance ADDM) and then immediately performs an ADDM database analysis.

Figure 1 shows the results in Oracle 11g’s 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, it’s also now possible to run an ADDM performance analysis for a subset of RAC instances in what’s 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.

DBMS_ADDM

So how does Oracle 11gR1 provide all these analysis capabilities? It uses a brand-new package, DBMS_ADDM that encapsulates all ADDM functionality. I’ve presented the list of DBMS_ADDM’s analysis procedures in Table 1.

I’ve 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

There’s no question that ADDM analyses are quite exhaustive; however, I’ve 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 I’ve 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 11g’s new DBMS_ADDM package now gives me just this capability: It’s 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 it’s 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.

I’ve 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. I’ve supplied a sample SQL*Plus formatted query against this view in Listing 3.

Removing ADDM Findings Filters and ADDM Tasks. Finally, I’ve demonstrated how to remove existing ADDM findings and tasks in Listing 4.

Conclusion

Oracle Database 11g’s expanded Automatic Database Diagnostic Monitor features significantly extend an Oracle DBA’s 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 I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s 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 I’ve 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

Back to DBAsupport.com