ADDM

Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g

Overview
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:

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

The recommendations may include:

Hardware changes
Database configuration changes
Schema changes
Application changes
Using other advisors
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.
addmrpt.sql Script
The addmrpt.sql script can be used to create an ADDM report from SQL*Plus.

Related Views
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.