ADDM

ADDM(Automatic Database Diagnostic Monitor)


For Oracle systems, the statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event, often saving time and resources reproducing a problem.

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The findings (problems) are listed in order of potential impact on database performance, along with recommendations to resolve the issue and the symptoms which lead to it's discovery.

An ADDM analysis is performed every time an AWR snapshot is taken and the results are saved in the database.The obvious place to start viewing ADDM reports is Enterprise Manager.

The value for the SET LONG command should be adjusted to allow the whole report to be displayed.
SET LONG 100000000

In most cases, ADDM output should be the first place that a DBA looks when notified of a performance problem. ADDM provides the following benefits:

• Automatic performance diagnostic report every hour by default
• Problem diagnosis based on decades of tuning expertise
• Time-based quantification of problem impacts and recommendation benefits
• Identification of root cause, not symptoms
• Recommendations for treating the root causes of problems
• Identification of non-problem areas of the system
• Minimal overhead to the system during the diagnostic process

The goal of the analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by the database server in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

The types of problems that ADDM considers include the following:

• CPU bottlenecks - Is the system CPU bound by Oracle or some other application?
• Undersized Memory Structures - Are the Oracle memory structures, such as the SGA, PGA, and buffer cache, adequately sized?
• I/O capacity issues - Is the I/O subsystem performing as expected?
• High load SQL statements - Are there any SQL statements which are consuming excessive system resources?
• High load PL/SQL execution and compilation, as well as high load Java usage
• RAC specific issues - What are the global cache hot blocks and objects; are there any interconnect latency issues?
• Sub-optimal use of Oracle by the application - Are there problems with poor connection management, excessive parsing, or application level lock contention?
• Database configuration issues - Is there evidence of incorrect sizing of log files, archiving issues, excessive checkpoints, or sub-optimal parameter settings?
• Concurrency issues - Are there buffer busy problems? Hot objects and top SQL for various problem areas

A problem finding can be associated with a list of Recommendation's for reducing the impact of the performance problem. Each recommendation has a benefit which is an estimate of the portion of DB time that can be saved if the recommendation is implemented. A list of recommendations can contain various alternatives for solving the same problem; you not have to apply all the recommendations to solve a specific problem.

Automatic database diagnostic monitoring is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable the automatic database diagnostic monitoring.


To diagnose database performance issues, ADDM analysis can be performed across any two AWR snapshots as long as the following requirements are met:

• Both the snapshots did not encounter any errors during creation and both have not yet been purged. 
• There were NO SHUTDOWN AND STARTUP actions between the two snapshots.

Typically, you would view output and information from the automatic database diagnostic monitor through Oracle Enterprise Manager or ADDM reports. However, you can display ADDM information through the DBA_ADVISOR views. This group of views includes:

DBA_ADVISOR_TASKS

This view provides basic information about existing tasks, such as the task Id, task name, and when created.

DBA_ADVISOR_LOG

This view contains the current task information, such as status, progress, error messages, and execution times.

DBA_ADVISOR_RECOMMENDATIONS

This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.

DBA_ADVISOR_FINDINGS

This view displays all the findings and symptoms that the diagnostic monitor encountered along with the specific recommendation.

ACTIVE SESSION HISTORY


With Oracle Database 10g, there is a new view introduced called V$ACTIVE_SESSION_HISTORY, which maintains data from active sessions, capturing the wait events and their wait times, the SQL_ID, and session information for the waiting session.In addition, the view V$SESSION_WAIT_HISTORY will provide the last 10 wait events for an active session.

With Oracle Database 10g, there are now over 700 wait events, primarily due to the fact that many previous wait events have been broken down into more granular events to make diagnostics more precise. To make these wait events easier to interpret, they are categorized into wait classes, with each wait class pointing to a high-level category of problem for a particular wait event


ASH stores the history of a recent session's activity and facilitates the analysis of the system performance at the current time. ASH is designed as a rolling buffer in memory, and earlier information is overwritten when needed. ASH uses the memory of the SGA.