Automatic Database Diagnostic Monitor

Automatic Database Diagnostic Monitor

When problems occur with a system, it is important to perform accurate and timely diagnosis of the problem before making any changes to a system. Oftentimes, a database administrator (DBA) simply looks at the symptoms and immediately starts changing the system to fix those symptoms. However, an accurate diagnosis of the actual problem in the initial stage significantly increases the probability of success in resolving the problem.

With Oracle Database, the statistical data needed for accurate diagnosis of a problem is stored in the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM):

• Analyzes the AWR data on a regular basis
• Diagnoses the root causes of performance problems
• Provides recommendations for correcting any problems
• Identifies non-problem areas of the system

Because AWR is a repository of historical performance data, ADDM can analyze performance issues after the event, often saving time and resources in reproducing a problem.

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

It is important to realize that tuning is an iterative process, and fixing one problem can cause the bottleneck to shift to another part of the system. Even with the benefit of ADDM analysis, it can take multiple tuning cycles to reach acceptable system performance. ADDM benefits apply beyond production systems; on development and test systems, ADDM can provide an early warning of performance issues.

Topics

1. ADDM Performance Monitoring
2. ADDM and Database Time
3. DBTime-Graph and ADDM Methodology
4. ADDM with Oracle Real Application Clusters

Refer the links below:
Automatic Performance Diagnostics
Diagnosing and Resolving Performance Problems Using ADDM

ADDM with Oracle Real Application Clusters

If you are using Oracle RAC, you can run ADDM in Database analysis mode to analyze the throughput performance of all instances of the database. In Database mode, ADDM considers DB time as the sum of the database time for all database instances. Using the Database analysis mode enables you to view all findings that are significant to the entire database in a single report, instead of reviewing a separate report for each instance.

The Database mode report includes findings about database resources (such as I/O and interconnect). The report also aggregates findings from the various instances if they are significant to the entire database. For example, if the CPU load on a single instance is high enough to affect the entire database, the finding will appear in the Database mode analysis, which will point to the particular instance responsible for the problem.

Automatic Database Diagnostic Monitor and Oracle RAC Performance

The Automatic Database Diagnostic Monitor (ADDM) is a self-diagnostic engine built into the Oracle Database. ADDM examines and analyzes data captured in the Automatic Workload Repository (AWR) to determine possible performance problems in Oracle Database. ADDM then locates the root causes of the performance problems, provides recommendations for correcting them, and quantifies the expected benefits. ADDM analyzes the AWR data for performance problems at both the database and the instance level.

An ADDM analysis is performed as each AWR snapshot is generated, which is every hour by default. The results are saved in the database and can be viewed by using Enterprise Manager. Any time you have a performance problem, you should first review the results of the ADDM analysis. An ADDM analysis is performed from the top down, first identifying symptoms, then refining the analysis to reach the root causes, and finally providing remedies for the problems.

For the clusterwide analysis, Enterprise Manager reports two types of findings:

Database findings: An issue that concerns a resource that is shared by all instances in the cluster database, or an issue that affects multiple instances. An example of a database finding is I/O contention on the disk system used for shared storage.

Instance findings: An issue that concerns the hardware or software that is available for only one instance, or an issue that typically affects just a single instance. Examples of instance findings are high CPU load or sub-optimal memory allocation.

ADDM reports only the findings that are significant, or findings that take up a significant amount of instance or database time. Instance time is the amount of time spent using a resource due to a performance issue for a single instance and database time is the sum of time spent using a resource due to a performance issue for all instances of the database, excluding any Oracle Automatic Storage Management (Oracle ASM) instances.

An instance finding can be reported as a database finding if it relates to a significant amount of database time. For example, if one instance spends 900 minutes using the CPU, and the sum of all time spent using the CPU for the cluster database is 1040 minutes, then this finding would be reported as a database finding because it takes up a significant amount of database time.

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

Recommendations are composed of actions and rationales. You must apply all the actions of a recommendation to gain the estimated benefit of that recommendation. The rationales explain why the actions were recommended, and provide additional information to implement the suggested recommendation.

Viewing ADDM for Oracle RAC Findings

DBTime-Graph and ADDM Methodology

Identifying the component contributing the most database time is equivalent to finding the single database component that provides the greatest benefit when tuned. ADDM uses database time to identify database components that require investigation and also to quantify performance bottlenecks. 

The first step in automatic performance tuning is to correctly identify the causes of performance problems. Only when the root cause of the performance problem is correctly identified, is it possible to explore effective tuning recommendations to solve or alleviate the issue.

ADDM looks at the database time spent in two independent dimensions:

• The first dimension looks at the database time spent in various phases of processing user requests. This dimension includes categories such as “connecting to the database,” “optimizing SQL statements,” and “executing SQL statements.”

• The second dimension looks at the database time spent using or waiting for various database resources used in processing user requests. The database resources considered in this dimension include both hardware resources, such as CPU and I/O devices, and software resources, such as database locks and application locks.

To perform automatic diagnosis, ADDM looks at the database time spent in each category under both these dimensions and drills down to the categories that had consumed significant database time. This two-dimensional drilldown process can be represented using the DBTime-graph.

Performance problems often distribute database time across many categories in one dimension but not in the other. For example, a database with insufficient CPU capacity slows down all phases involved in processing user requests, which is in the first dimension of the ADDM analysis. 

However, it would be evident from the second dimension that the top performance problem affecting the database is insufficient CPU capacity. This two-dimensional view of determining where the database time is consumed gives ADDM very good judgment in zooming in to the more significant performance issues.

ADDM and Database Time

Database time is defined as the sum of the time spent inside the database processing user requests. 

The user’s response time is the time interval between the instant the request is sent and the instant the response is received. The database time involved in that user request is only a portion of that user’s response time that is spent inside the database.

The upper graphic in the slide above illustrates a simple scenario of a single user submitting a request. The user’s response time is the time interval between the instant the request is sent and the instant the response is received. The database time involved in that user request is only a portion of that user’s response time that is spent inside the database. 

 

The lower graphic in the slide illustrates database time as it is summed over multiple users, and each user is performing a series of operations resulting in a series of requests to the database. You can see that the database time is directly proportional to the number and duration of user requests, and can be higher or lower than the corresponding wall-clock time (elapsed time).

Using the database time as a measure, you can gauge the performance impact of any entity of the database. 

For example, the performance impact of an undersized buffer cache would be measured as the total database time spent in performing additional I/O requests that could have been avoided if the buffer cache were larger.

Database time is simply a measurement of the total amount of work done by the database server. The rate at which the database time is consumed is the database load average, measured as database time per second. 

The objective of ADDM is to reduce the amount of database time spent on a given workload, which is analogous to consuming less energy to perform the same task.

ADDM Performance Monitoring

ADDM is scheduled to run automatically by the MMON process on every database instance to detect problems proactively. Each time a snapshot is taken, ADDM is triggered to perform an analysis of the period corresponding to the last two snapshots. This approach proactively monitors the instance and detects bottlenecks before they become a significant problem.

The results of each ADDM analysis are stored in Automatic Workload Repository and are also accessible through Database Control.

By default, the Oracle database server automatically captures statistical information from the SGA every 60 minutes and stores it in Automatic Workload Repository (AWR) in the form of snapshots. These snapshots are stored on disk and are similar to Statspack snapshots. However, they contain more precise information than the Statspack snapshots.

Additionally, ADDM is scheduled to run automatically by the MMON process on every database instance to detect problems proactively. Each time a snapshot is taken, ADDM is triggered to perform an analysis of the period corresponding to the last two snapshots. This approach proactively monitors the instance and detects bottlenecks before they become a significant problem.

The results of each ADDM analysis are stored in Automatic Workload Repository and are also accessible through Database Control.

Note: Although ADDM analyzes Oracle database performance over the period defined by the last two snapshots, it is possible to manually invoke an ADDM analysis across any two snapshots.

Types of Problems Analyzed by ADDM:

• CPU load 
• Memory usage 
• I/O usage 
• Resource intensive SQL 
• Resource intensive PL/SQL and Java 
• RAC issues 
• Application issues 
• Database configuration issues 
• Concurrency issues 
• Object contention 

ADDM is enabled by default and controlled by two parameters

1. CONTROL_MANAGEMENT_PACK_ACCESS, if set to DIAGNOSTIC or DIAGNOSTIC+TUNNING 
2. STATISTICS_LEVEL if set to either TYPICAL or ALL 

ADDM can be executed in three modes

1. Database Mode
2. Instance Mode
3. Partial Mode

Below example creates an ADDM task in database analysis mode for the entire database during the time period defined by snapshots 11050 and 11056

VAR tname VARCHAR2(30);
BEGIN
  :tname := 'ADDM for 11PM to 5AM';
  DBMS_ADDM.ANALYZE_DB(:tname,  11050, 11056);
END;

Snap ids can be taken from below SQL




SQL> select * from DBA_HIST_SNAPSHOT







To analyze a particular instance of the database



VAR tname VARCHAR2(30);
BEGIN
  :tname := 'my ADDM for 11PM to 5AM';
  DBMS_ADDM.ANALYZE_INST(:tname,11050,11056, 1);

END;





Where 1 = Instance number




Running the ADDM in Partial Mode:

VAR tname VARCHAR2(30);
BEGIN
  :tname := 'my ADDM for 7PM to 9PM';
  DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,4', 11050, 11056);
END;


Where 1, 2, and 4 are the instance numbers during the time period defined by snapshots 11050 and 11056 snapshots




Running the ADDM Reports:






SQL> SELECT DBMS_ADDM.GET_REPORT( :tname) FROM DUAL;







-- UNIX
@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql








DBMS_ADVISOR


The DBMS_ADVISOR package can be used to create and execute any advisor tasks, including ADDM tasks. The following example shows how it is used to create, execute and display a typical ADDM report.




BEGIN
  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'ADDM',
    task_name         => '970_1032_AWR_SNAPSHOT',
    task_desc         => 'Advisor for snapshots 970 to 1032.');

  -- Set the start and end snapshots.
  DBMS_ADVISOR.set_task_parameter (
    task_name => '970_1032_AWR_SNAPSHOT',
    parameter => 'START_SNAPSHOT',
    value     => 970);

  DBMS_ADVISOR.set_task_parameter (
    task_name => '970_1032_AWR_SNAPSHOT',
    parameter => 'END_SNAPSHOT',
    value     => 1032);

  -- Execute the task.
  DBMS_ADVISOR.execute_task(task_name => '970_1032_AWR_SNAPSHOT');
END;
/

-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM   dual;
SET PAGESIZE 24