Automatic Database Diagnostic Monitor (ADDM) | Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)

Automatic Database Diagnostic Monitor (ADDM, pronounced ‚“Adam ‚½) is not just another piece of software that you purchase from Oracle Corporation and install on your database server and workstation.

ADDM is a holistic self-diagnostic mechanism built into the Oracle Database 10 g . It is an integral part of the kernel. It automatically examines and analyzes the snapshot data captured into AWR to proactively determine any major issues with the system, and in many cases it recommends corrective actions with quantified expected benefits. It is constantly monitoring and diagnosing your system.

The goal of ADDM is to identify the areas of the system that are consuming the most DB time ‚ time spent in the database calls. It uses the wait model and time model statistics to find where time is being spent in the database. It drills down to the root cause of the problem using a tree-structured set of rules. These rules have a proven track record and have been used successfully over the past several years by Oracle Corporation in performance tuning engagements.

ADDM can detect and report many problems including the following types of problems:

CPU bottlenecks due to Oracle and non-Oracle applications
I/O subsystem capacity issues
High-load SQL statements that may be consuming excessive system resources
High-load PL/SQL compilation and execution consuming excessive system resources
High-load Java applications consuming excessive system resources
Undersized memory structures, such as SGA, buffer cache, and log buffer
Poor connection management
RAC- related issues with global cache management and interconnect latency
Concurrent data access issues resulting in buffer busy waits
Database configuration issues such as log file sizing, archiving or suboptimal parameter settings

In addition to reporting the problematic areas of the system, ADDM also reports the areas it treats as nonproblematic, so you don ‚ t spend time analyzing items that don ‚ t impact overall system performance. ADDM also recommends possible solutions to the common problems it detects. Again, ADDM recommended solutions are targeted towards achieving lower DB time .

Note ‚

Because the recommendations from ADDM are generated by a set of predefined rules, those may or may not be applicable to all the situations. You may find some of these recommendations unsuitable to your environments. According to Oracle, ADDM does not target the tuning of individual user response times. Use tracing techniques to tune for individual user response times.

ADDM Setup

Although the Automatic Database Diagnostic Monitoring is enabled by default, there are a couple of parameters that you need to be aware of.

The initialization parameter STATISTICS_LEVEL must be set either to TYPICAL or ALL to enable ADDM functionality. It defaults to TYPICAL; setting it to BASIC will disable ADDM and many other features.

The other parameter is not an initialization parameter. It is a special ADDM-related task parameter, DBIO_EXPECTED, which ADDM uses to analyze the performance of the I/O subsystem. The value for the DBIO_EXPECTED parameter defines the average time it takes to read a single database block in microseconds. The default value for this parameter is 10 milliseconds (10,000 microseconds). If your think you I/O subsystem response time is significantly different, you may want to change this default value.

First, you must find out the average read time for random reads of a single database block for your hardware. Convert that to microseconds. For this example, let ‚ s say it comes out to be 30,000 microseconds (somewhat slow disks).

Second, use the following procedure to set the DBIO_EXPECTED value to 30,000:

 REM  Run this as SYS user 
 begin 
 dbms_advisor.set_default_task_parameter ( 
 ADDM,DBIO_EXPECTED,30000); 
 end; 
 / 
 PL/SQL procedure successfully completed.

The value for the DBIO_EXPECTED parameter is saved in an internal table. You need to execute the preceding procedure to change it. The following SQL script shows how you can interrogate the current value of this parameter:

 col parameter_name for a20 
 col parameter_value for a20 
 select advisor_name, 
 parameter_name, 
 parameter_value, 
 from   dba_advisor_def_parameters 
 where  parameter_name like 'DBIO%'; 
 
 ADVISOR_NAME         PARAMETER_NAME       PARAMETER_VALUE 
 -------------------- -------------------- -------------------- 
 ADDM                 DBIO_EXPECTED        30000

Using EM to Access ADDM

The Oracle EM Database Control is the primary interface to the ADDM. In the following series of steps you can see the results of ADDM analysis.

Figure 9-4 and Figure 9-5 show the top and bottom portion of the Database home page. Information pertaining to the database instance, host CPU usage, active session, space usage, diagnostic summary, alerts, and performance analysis is shown on the Database home page. It also contains links to access other components that perform several other tasks .

Figure 9-4: Database home page top portion

Figure 9-5: Database home page bottom portion

In Figure 9-4, the number of ADDM performance findings for the last ADDM analysis period is shown under Diagnostic Summary toward the bottom right corner.

In Figure 9-5, those performance findings are listed under the Performance Analysis. For each finding ADDM also provided a few recommendations that are shown on the right.

The first performance finding states that there was CPU contention . By clicking the link associated with this finding, you can drill down to the details, as shown in Figure 9-6.

Figure 9-6: Performance Finding Details and Recommendations

For the CPU contention issue, in this case, ADDM wants you to consider adding more CPUs to the server or adding more database instances, preferably on other servers, to service the load. It also identified two SQL statements that may need investigating.

Back on the Database home page, as shown in Figure 9-5, if you click the Advisor Central link under the heading Related Links at the bottom of the page, you are presented with the Advisor Central home page, as shown in Figure 9-7.

Figure 9-7: Advisor Central home page

The Advisor Central home page offers a variety of advisories on almost any component of the database, from SQL tuning to undo management. It readily shows the details of the automatic advisory tasks that ADDM performed when the last AWR snapshot was taken. The latest advisor task is shown on this page. You can review reports of the previous tasks using the pull-down Advisor Runs menu. You can select from the last run, the last 24 hours, or the last 7 days.

Clicking the name of the advisor task takes you to the Automatic Database Diagnostic Monitor (ADDM) page, as shown in Figure 9-8.

Figure 9-8: Automatic Database Diagnostic Management

This page shows the database activity over the past several hours and additional information on the advisor task. To view the ADDM report for this particular advisor task, click the View Report button. The detailed ADDM report for the selected task will be displayed, as shown in Figure 9-9.

Figure 9-9: Viewing the ADDM report

All this information was readily captured and analyzed by Oracle Database 10 g . It was easily accessible, and most importantly, ADDM analyzed the system load and offered recommendations before its findings became a real problem.

Even if you do not look at the ADDM findings immediately, you can access them later because they are stored in the database. ADDM data (and all ADDM advisor framework data) is stored for 30 days by default.

However, many a times DBAs will have to perform real-time problem diagnosis. How many times have you received calls from users stating the database is slow? With the Performance page of EM Database Control, you can now easily find out what ‚ s going on in your system. From the Database Control home page (Figure 9-4), click the Performance link to access the Performance home page, as shown in Figure 9-10.

Figure 9-10: Performance home page

On the Performance page, you can see how the CPU and memory resources are being used to make sure those are not the bottlenecks. You can assess the database health from the Sessions: Waiting and Working graph that shows how the CPU is being used by the sessions and if there are any sessions waiting for the resources. This graph provides quite a bit of information. It shows the average number of active sessions on the Y axis broken down by the Wait class and CPU. The X axis shows the time. The data is refreshed every 15 seconds by default. The graph uses various colors to indicate different wait classes. The larger the block of color , the worse the problem. Clicking the legend of the color scheme on the right, which is broken down by wait class, will take you to the drill-down page showing the active sessions waiting for that wait class. In the example in Figure 9-10, the Application wait class was the prominent color block. Clicking the Application legend takes you to the Active Sessions Waiting: Application page, as shown in Figure 9-11. You see that the wait event is enq: TX row lock contention , so there is a locking problem in this particular application.

Figure 9-11: Active Sessions Waiting: Application (wait class)

You can drill down by clicking the link under the Top Waiting SQL get the to SQL statement details, as shown in Figure 9-12. There you have it. This is the SQL that is waiting for the lock to be released.

Figure 9-12: SQL Details showing SQL text and explain plan

You can also drill down by clicking the link under the Top Waiting Sessions to find the details of the waiting session, as shown in Figure 9-13. The session has been waiting on the enq: TX row lock contention wait event.

Figure 9-13: Session Details showing general information

By clicking the Wait Events link on the Session Details page, you can see the historical wait event the session encountered . In the example shown in Figure 9-14, the session has been waiting on the enq: TX row lock contention for quite some time.

Figure 9-14: Session Details showing session waits

Manually Running ADDM Report

Running the ADDM report from within the Oracle EM is the preferred and the simplest method. However, you can use Oracle-supplied scripts and package procedures to generate the ADDM diagnosis report. You need to know any two AWR snapshots to produce such a report. The snapshots must be available in AWR and there must not be any database restarts between those snapshots.

There are two scripts in the $ORACLE_HOME/rdbms/admin directory that can generate the ADDM diagnosis report: addmrpt.sql and addmrpti.sql . The former generates the report for the local database instance, while the latter can generate the report for other instances (in the RAC environment, for example). In addition, the Oracle-supplied package DBMS_ADVISOR has procedures (API) to generate the ADDM diagnosis report.

We will briefly discuss how to use these methods to generate the ADDM diagnosis report.

To run the scripts or use the API scripts you must have the ADVISOR privilege.

When running addmrpt.sql , you will be prompted to provide the beginning and ending snapshot ID from a list of available snapshots and a report name of your choice. Oracle will generate the ADDM diagnosis report for the specified range of snapshot IDs. The report can also be run in a noninteractive mode. The script has instructions in it to show you how to do that.

To generate the ADDM diagnosis report using the DBMS_ADVISOR package directly needs a bit more setup, as follows :

Create an advisor task of ADDM type, using the CREATE_TASK procedure.
Set the START_SNAPSHOT and END_SNAPSHOT parameters to run the just-created task using the SET_TASK_PARAMETER procedure. The ADDM diagnosis report will be generated for the range of these snapshots.
Execute the task using the EXECUTE_TASK procedure to generate the diagnosis report.

View the generated ADDM diagnosis report using GET_TASK_REPORT procedure.

Note ‚

The Oracle Database Performance Guide 10 g Release 1 has an excellent example in Chapter 6 in the section ‚“Running ADDM Using DBMS_ADVISOR APIs ‚½ that demonstrates the use of the ADMS_ADVISOR package to generate and view the ADDM diagnosis report.

ADDM Views

Oracle EM is the preferred interface to view all ADDM-related information. However, the DBMS_ADVISOR views can be used to view this information. Table 9-4 lists a few of these views and their description.

Table 9-4: ADDM Views (Not a Complete List)
DBA_ADVISOR_FINDINGS	Displays all the findings discovered by ADDM.
DBA_ADVISOR_LOG	Displays current state of all tasks in the database such as task progress, error messages, and execution times. There is one row for each task.
DBA_ADVISOR_RATIONALE	Displays the rationales for all recommendations.
DBA_ADVISOR_RECOMMENDATIONS	Displays the results of all completed diagnostic tasks with recommendations for the detected problems. The recommendations are ranked in the RANK column. The BENEFIT column shows the expected benefit after carrying out recommended actions.
DBA_ADVISOR_TASKS	Displays information about all existing tasks in the database.