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 ADDM analysis includes:
Resource intensive SQL
Resource intensive PL/SQL and Java
Database configuration issues
The recommendations may include:
Database configuration changes
Using other advisors
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.
The addmrpt.sql script can be used to create an ADDM report from SQL*Plus.
The following views can be used to display the ADDM output without using Enterprise Manager or the GET_TASK_REPORT function:
DBA_ADVISOR_TASKS - Basic information about existing tasks.
DBA_ADVISOR_LOG - Status information about existing tasks.
DBA_ADVISOR_FINDINGS - Findings identified for an existing task.
DBA_ADVISOR_RECOMMENDATIONS - Recommendations for the problems identified by an existing task.