Oracle 19c Use Automatic Database Diagnostic Monitor (ADDM) in RAC

Using the Automatic Database Diagnostic Monitor (ADDM) in Oracle 19c is essential for maintaining the health and performance of Real Application Clusters (RAC) environments. ADDM provides a comprehensive analysis of the database performance, helping DBAs identify and resolve issues promptly. In this article, we will discuss the usage of ADDM in RAC and delve into various aspects of RAC diagnostics.

Understanding ADDM in RAC

ADDM in RAC is designed to analyze performance data collected by the Automatic Workload Repository (AWR). By evaluating this data, ADDM identifies potential problems affecting the database and provides recommendations for improvement. This tool is invaluable for maintaining optimal performance and ensuring the efficient operation of RAC environments.

How ADDM Works in RAC

ADDM performs its analysis by examining the data stored in AWR snapshots. These snapshots capture a wide range of performance metrics, including wait events, system statistics, and SQL execution times. ADDM evaluates this data to identify performance bottlenecks and suggest actionable solutions. In a RAC environment, ADDM analyzes data across all instances, providing a comprehensive view of the cluster’s performance.

-- Example: Generating an ADDM report in Oracle RAC
BEGIN
  DBMS_ADDM.ANALYZE_DB(:TASK_NAME, :START_SNAPSHOT, :END_SNAPSHOT);
END;

Benefits of Using ADDM in RAC

Utilizing ADDM in RAC offers several benefits, including:

• Comprehensive Analysis: ADDM provides a detailed analysis of the entire RAC environment, helping DBAs understand performance issues across all nodes.
• Actionable Recommendations: The tool offers specific recommendations for addressing identified issues, enabling DBAs to take corrective actions quickly.
• Historical Performance Insights: By analyzing historical data, ADDM helps in understanding performance trends and preparing for future workloads.

RAC Diagnostics with ADDM

RAC diagnostics are critical for maintaining the stability and performance of clustered environments. ADDM assists in diagnosing several key areas:

Cluster Diagnostics

Cluster diagnostics involve examining the interactions between different nodes in a RAC environment. ADDM helps in identifying issues such as interconnect latency, data block contention, and load imbalances.

-- Example: Query to check interconnect performance
SELECT inst_id, name, value
FROM gv$sysstat
WHERE name LIKE 'gc%';

Database Diagnostics

Database diagnostics focus on identifying and resolving issues within individual database instances. ADDM evaluates SQL execution plans, identifies inefficient queries, and suggests optimizations to improve performance.

-- Example: Query to find top wait events
SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

Implementing ADDM Recommendations

Implementing ADDM recommendations involves executing the suggested actions to resolve identified issues. This may include optimizing SQL queries, adjusting database configurations, or redistributing workloads across nodes. Regularly reviewing and implementing ADDM recommendations is crucial for maintaining optimal performance in RAC environments.

Example of Implementing ADDM Recommendations

1. Optimize SQL Queries: Rewrite inefficient SQL queries based on ADDM’s recommendations.
2. Adjust Database Configurations: Modify initialization parameters to optimize resource utilization.
3. Redistribute Workloads: Balance the load across RAC nodes to prevent any single node from becoming a bottleneck.

Running ADDM in Database Mode

For RAC configurations, you can run ADDM in Database mode to analyze all instances of the databases. To run ADDM in Database mode, use the DBMS_ADDM.ANALYZE_DB procedure:

BEGIN
  DBMS_ADDM.ANALYZE_DB(
    task_name       => :TASK_NAME,
    begin_snapshot  => :BEGIN_SNAPSHOT,
    end_snapshot    => :END_SNAPSHOT,
    db_id           => :DB_ID
  );
END;
/

This example creates an ADDM task in database analysis mode and executes it to diagnose the performance of the entire database during the time period defined by snapshots 137 and 145:

VAR tname VARCHAR2(30);
BEGIN
  :tname := 'ADDM for 7PM to 9PM';
  DBMS_ADDM.ANALYZE_DB(:tname, 137, 145);
END;
/

Generating ADDM Reports

To generate ADDM reports in a RAC database, use the following SQL script:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpti.sql

This script will generate an ADDM report for the specified time range across all instances in the RAC database.

Conclusion

Using ADDM in Real Application Cluster diagnostics is an effective way to ensure the performance and stability of your database environment. By providing comprehensive analysis and actionable recommendations, ADDM helps DBAs maintain optimal performance and quickly address potential issues. Regular use of ADDM, along with proactive implementation of its suggestions, is essential for the smooth operation of RAC environments.

See more on Oracle’s website!

Be Oracle RAC and GRID Certified Professional, this world is full of opportunities for qualified DBAs!