Oracle 19c Diagnosing Performance Issues using ADDM

In Oracle, diagnosing performance issues using the Automatic Database Diagnostic Monitor (ADDM) is essential for maintaining database efficiency. ADDM Diagnosis provides valuable insights into performance bottlenecks and helps optimize the database. This article will explore ADDM Performance Diagnosis, Database Performance Tuning, and the steps to effectively use ADDM for performance monitoring.

Understanding ADDM Performance Diagnosis

ADDM Performance Diagnosis involves analyzing database performance metrics to identify issues affecting efficiency. ADDM collects data from the Automatic Workload Repository (AWR) and provides recommendations to address performance problems. This process is crucial for maintaining optimal database performance and ensuring smooth operations.

By examining snapshots taken at regular intervals, ADDM can detect trends and anomalies. This proactive approach helps database administrators (DBAs) to anticipate potential issues and take corrective actions before they impact the system.

ADDM Diagnosis – Creating an ADDM Baseline

To start using ADDM Diagnosis, you need to create a baseline. This baseline represents a period of normal database performance, serving as a reference point for future comparisons. Follow these steps to create an ADDM baseline:

1. Identify the Baseline Period: Choose a period that reflects typical database performance under normal workload conditions.
2. Capture AWR Snapshots: Use the DBMS_WORKLOAD_REPOSITORY package to capture snapshots of the database performance during the identified period.
3. Create the Baseline: Define the baseline using the captured snapshots.

For example:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
    start_snap_id => 100, 
    end_snap_id   => 110, 
    baseline_name => 'Normal_Performance'
  );
END;

In this example, the baseline ‘Normal_Performance’ is created using snapshots 100 to 110. This baseline will help in comparing current performance with past optimal performance.

📢 You might also like: Oracle 19c Diagnosing Performance Issues using ASH Reports (Category: Performance Management and Tuning)

ADDM Diagnosis – Managing ADDM Baselines

Managing ADDM baselines involves updating, maintaining, and purging baselines as needed. Regular updates ensure that baselines remain relevant and reflective of current workloads. DBAs can update baselines by creating new snapshots and incorporating them into existing baselines.

To remove an obsolete baseline, use the following command:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
    baseline_name => 'Old_Performance',
    cascade       => FALSE
  );
END;

This command deletes the ‘Old_Performance’ baseline, helping maintain an organized repository.

Using ADDM for Performance Diagnosis

Once ADDM baselines are created and managed, they can be used for performance diagnosis. ADDM analyzes the AWR data and generates reports highlighting performance issues and providing recommendations for improvement. These reports are accessible through Oracle Enterprise Manager (OEM), offering a graphical interface for easy interpretation.

Generating an ADDM Report

1. Access the Performance Page in OEM: Navigate to the Performance page and select the desired baseline.
2. Generate the ADDM Report: This report will highlight deviations from the baseline and suggest corrective actions.

To generate the ADDM report manually, you can use the following script:

@$ORACLE_HOME/rdbms/admin/addmrpt.sql

ADDM Analysis Options

An ADDM analysis can be performed on a pair of AWR snapshots and a set of instances from the same database. The pair of AWR snapshots define the time period for analysis, and the set of instances define the target for analysis.

• Database Mode: Analyzes all instances of the database.
• Instance Mode: Analyzes a particular instance of the database.
• Partial Mode: Analyzes a subset of all database instances.

For non-Oracle RAC or single instance environments, the analysis performed in Instance mode is the same as a database-wide analysis. If using Oracle RAC, ADDM also analyzes the entire database in Database mode.

ADDM Tuning Session

An ADDM tuning session follows the same steps as a manual tuning session, but with automation to streamline the process:

1. Generate ADDM report: ADDM runs automatically each time an AWR snapshot is taken.
2. Review the recommendations: Examine the findings displayed on the Database Home page in OEM.
3. Implement the recommendations: Apply the suggested changes to optimize performance.
4. Review the next ADDM report: Ensure the implemented changes have resolved the issues.

ADDM Diagnosis – Manual Tuning Session

A manual tuning session involves more steps compared to ADDM tuning:

1. Collect current statistics: Gather the latest performance data.
2. Compare current statistics with a previous set: Identify deviations.
3. Look up in a performance-issues knowledge base: Research known issues and solutions.
4. Define the problem and make recommendations: Propose a solution.
5. Build a trial solution: Develop a test fix.
6. Implement and measure the change: Apply and evaluate the fix.
7. Decide if the solution met the goal: Assess the effectiveness of the fix.

Benefits of ADDM Diagnosis

ADDM Diagnosis offers several benefits for database performance management:

1. Proactive Monitoring: ADDM helps identify performance issues before they impact the system, allowing for timely intervention.
2. Trend Analysis: By comparing current performance with historical baselines, DBAs can identify trends and plan for future capacity needs.
3. Performance Optimization: Recommendations from ADDM reports guide DBAs in optimizing database performance.
4. Efficient Resource Allocation: Understanding performance trends helps in better allocation of resources, ensuring optimal usage.

Conclusion

In conclusion, ADDM Diagnosis is a powerful tool for diagnosing performance issues in Oracle databases. By creating and managing ADDM baselines, and using the insights provided by ADDM reports, DBAs can ensure their databases run efficiently and effectively. Implementing ADDM Diagnosis not only helps in maintaining optimal performance but also provides valuable insights into performance trends and potential issues.

Start using ADDM in your Oracle environment today to enhance your performance monitoring and ensure your database operates at peak efficiency.

See more on Oracle’s website!

Be Oracle Performance Management and Tuning Certified Professional, this world is full of opportunities for qualified DBAs!