ADDM Enhancements in Oracle Database 11g

Synopsis. Oracle Database 10g introduced the Automatic Database Diagnostic Monitor (ADDM) feature set as part of the Diagnostic Pack, and it’s 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 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).



Advisor Central: Database and Instance ADDM
Figure 1. Advisor Central: Database and Instance ADDM


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:



Database ADDM: Summary Page
Figure 2. Database ADDM: Summary Page


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:



Viewing a Database ADDM Finding Results
Figure 3. Viewing a Database ADDM Finding Results


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.

























Table 1. DBMS_ADDM Analysis Procedures


Function


Description


ANALYZE_DB


Performs database and instance level ADDM analyses for a specified set of snapshots


ANALYZE_INSTANCE


Performs instance level ADDM analysis for a specified set of snapshots


ANALYZE_PARTIAL


For a specified set of snapshots, performs instance level ADDM analyses for a selected set of instances


GET_REPORT


Generate a report for a specific ADDM analysis


DELETE


Remove an existing ADDM analysis task


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:



Viewing Active ADDM Filters
Figure 4. Viewing Active ADDM Filters



Editing a Performance Filter
Figure 5. Editing a Performance Filter


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.


































Table 2. DBMS_ADDM Results Filtering Procedures


Procedure


Description


INSERT_FINDING_DIRECTIVE


Applies a filter for a specific ADDM finding


INSERT_PARAMETER_DIRECTIVE


Applies a filter against an ADDM recommendation to modify a specific initialization parameter


INSERT_SEGMENT_DIRECTIVE


Applies a filter to limit findings about a specific segment


INSERT_SQL_DIRECTIVE


Applies a filter against a specific SQL statement


DELETE_FINDING_DIRECTIVE


Removes an existing filter for a specific ADDM finding


DELETE_PARAMETER_DIRECTIVE


Removes an existing filter against an ADDM recommendation to modify a specific initialization parameter


DELETE_SEGMENT_DIRECTIVE


Removes an existing filter that limits findings about a specific segment


DELETE_SQL_DIRECTIVE


Removes an existing filter against a specific SQL statement


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


» See All Articles by Columnist Jim Czuprynski

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles