Performance Self-Diagnostics: Automatic Database Diagnostic Monitor

Oracle Database includes a self-diagnostic engine called Automatic Database Diagnostic Monitor (ADDM). ADDM makes it possible for Oracle Database to diagnose its own performance and determine how identified problems can be resolved.

To facilitate automatic performance diagnosis using ADDM, Oracle Database periodically collects snapshots of the database state and workload. Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM. Snapshots provide a statistical summary of the state of the system at a point in time. These snapshots are stored in Automatic Workload Repository (AWR), residing in the SYSAUX tablespace. The snapshots are stored in this repository for a set time (8 days by default) before they are purged to make room for new snapshots.

ADDM analyzes data to determine the major problems in the system, and may recommend solutions and quantify expected benefits. ADDM analysis results are represented as a set of findings.

EM Express provides three types of ADDM findings.

ADDM

ADDM performs its analysis on data that has been captured and stored in AWR. For ADDM, the default collection interval for a snapshot is one hour.

Generally, ADDM is used for identifying systemwide systemic problems. It calls attention to performance problems that include:

Resource contention (bottlenecks), such as when your database is using large amounts of CPU time or memory due to high load SQL statements
Poor connection management, such as when your application is making too many logins to the database
Lock contention in a multiuser environment, such as when one user process acquires a lock to safely update data in a table, causing other user processes that must acquire locks against the same table to wait, resulting in a slower database performance

Real-Time ADDM

Real-Time ADDM automatically monitors the database in real time.

Real-Time ADDM proactively detects and diagnoses transient high impact problems such as these before they threaten application performance:

High CPU
I/O spikes
Memory
Interconnect issues
Hangs and deadlocks

When Real-Time ADDM detects a possible performance problem, it triggers data collection. The data is saved in the report repository (part of AWR). When you view a Real-Time ADDM report from EM Express, an analysis is performed, and findings and recommendations are made. Because Real-Time ADDM reports are stored in AWR, they can help you identify recurrences of a problem over time.

Current ADDM Findings

If there are Real-Time ADDM findings for the past 5 minutes, these are listed on the Current ADDM Findings tab in the Performance Hub when both of the following are true:

Real-Time Data - Last Hour is selected in the Select Time Period field in the Performance Hub
The selected period in the time picker in the Performance Hub is "now" (the shaded blocks area in the time picker is at the far right in the Performance Hub)

Table 10-1 provides a summary of the ADDM features available in EM Express.

Table 10-1 ADDM Features in EM Express

Feature	New?	Description	Analysis Period	To View Analysis Findings
ADDM	No	This is the traditional ADDM that has existed in previous database releases. ADDM Tasks are presented on the ADDM tab in the Performance Hub.	The AWR interval, which is 1 hour, by default	In the ADDM Tasks table on the ADDM tab, click a Task Name.
Real-Time ADDM	Yes	Proactively detects and diagnoses transient high impact problems in real time. Real-Time ADDM Reports are presented on the ADDM tab in the Performance Hub.	Real time	In the Real-Time ADDM Reports table on the ADDM tab, select a report and click View Performance Report.
Current ADDM Findings	Yes	Triggers Real-Time ADDM analysis and findings for the past 5 minutes. Current ADDM Findings are presented on the Current ADDM Findings tab in the Performance Hub.	Last 5 minutes	In the Current ADDM Findings table, select a finding and click View Finding.