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 is performed every time an AWR snapshot is taken and the results are saved in the database provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.
The ADDM analysis includes:
•CPU bottlenecks
•Undersized Memory Structures
•I/O capacity issues
•High load SQL statements
•High load PL/SQL execution and compilation, as well as high load Java usage
•RAC specific issues
•Sub-optimal use of Oracle by the application
•Database configuration issues
•Concurrency issues
•Hot objects and top SQL for various problem areas
ADDM analysis results are represented as a set of FINDINGs
Example ADDM Report
FINDING 1: 31% impact (7798 seconds)
————————————
SQL statements were not shared due to the usage of literals. This resulted in additional hard parses which were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 31% benefit (7798 seconds)
ACTION: Investigate application logic for possible use of bind variables
instead of literals. Alternatively, you may set the parameter “cursor_sharing” to “force”.
RATIONALE: SQL statements with PLAN_HASH_VALUE 3106087033 were found to be using literals. Look in V$SQL for examples of such SQL statements.
In this example, the finding points to a particular root cause, the usage of literals in SQL statements, which is estimated to have an impact of about 31% of total DB time in the analysis period.
In addition to problem diagnostics, ADDM recommends possible solutions. When appropriate, ADDM recommends multiple solutions for the DBA to choose from. ADDM considers a variety of changes to a system while generating its recommendations.
Recommendations include:
•Hardware changes
•Database configuration
•Schema changes
•Application changes
•Using other advisors
ADDM Settings
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.
The default setting is TYPICAL.
Setting STATISTICS_LEVEL to BASIC disables many Oracle features, including ADDM, and is strongly discouraged.
ADDM analysis of I/O performance partially depends on a single argument, DBIO_EXPECTED
The value of DBIO_EXPECTED is the average time it takes to read a single database block in microseconds. Oracle uses the default value of 10 milliseconds
Set the value using
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(‘ADDM’, ‘DBIO_EXPECTED’, 8000);
Diagnosing Database Performance Issues with ADDM
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.
Using Enterprise Manager
The obvious place to start viewing ADDM reports is Enterprise Manager. The “Performance Analysis” section on the “Home” page is a list of the top five findings from the last ADDM analysis task.
Specific reports can be produced by clicking on the “Advisor Central” link, then the “ADDM” link. The resulting page allows you to select a start and end snapshot, create an ADDM task and display the resulting report by clicking on a few links.
Executing addmrpt.sql Script
The addmrpt.sql script can be used to create an ADDM report from SQL*Plus. The script is called as follows:
@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql
It then lists all available snapshots and prompts you to enter the start and end snapshot along with the report name.
Using DBMS_ADVISOR Package
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 24The value for the SET LONG command should be adjusted to allow the whole report to be displayed.
The relevant AWR snapshots can be identified using the DBA_HIST_SNAPSHOT view.
ADDM Views
•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
