The Automatic Database Diagnostic Monitor (ADDM) is a built-in feature of Oracle Database that helps to diagnose and resolve database performance issues. The ADDM provides automatic performance analysis of the database showing a detailed report that identifies performance problems, recommends solutions, and even provides specific command-line scripts to implement suggested changes. The ADDM analyzes performance data in the database’s Automatic Workload Repository (AWR) to provide diagnostic information about the database. The AWR collects and aggregates performance-related data in the database on an ongoing basis, essentially acting as a time-series database of performance metrics, ensuring accurate tracking of performance problems over time.
When a performance issue arises, the ADDM automatically analyzes the data in AWR, identifying any area of bottlenecks, poor SQL performance or configuration issues that are impacting the performance of the system. The ADDM uses Artificial Intelligence to analyze the workload and the configuration of the database looking for patterns of poor performance, unusual spikes or any situation that can cause problems.
Once the ADDM has identified the performance issues, it presents a detailed report that provides suggestions for resolving the problem. In the report, there is an analysis of the issue, the root cause of the problem, its impact on the database, and remedial actions to be taken.
Here is an example of an ADDM report:
Analysis Period: 10:00 - 11:00
Analysis Type: ADDM High Load SQL Analysis
Finding 1: SQL ID 12345678 consumed significant database time
Impact: High: consumes significant database time (30%)
Recommendation: Tune SQL, add an index or change instance configuration
Script: Run SQL Tuning Advisor on SQL ID, Implement index
Finding 2: Instance configuration has suboptimal settings
Impact: High
Recommendation: Change instance parameter, SGA size or database block size
Script: Run DBMS_ADVISOR Tuning Task on Instance configurationThe database administrator then takes the recommendation and implements the suggested remedies, which may include tuning SQL statements, adding indexes, modifying database configuration, or using additional hardware resources.
In summary, the ADDM is a powerful feature of Oracle Database that provides automated analysis and tuning of database performance. Its strength lies in the ability to analyze the database workload, identify performance bottlenecks, and provide recommendations for their remediation, making it a valuable tool for database administrators in tuning Oracle database performance.