Oracle AWR (Automatic Workload Repository) and ADDM (Automatic Database Diagnostic Monitor) are two powerful performance tuning tools that can help DBAs (Database Administrators) to identify and resolve performance issues in their databases. In this blog post, we'll explore what AWR and ADDM are, when to use each tool, and their advantages and disadvantages.
What is Oracle AWR?
AWR is a built-in repository in Oracle databases that stores performance-related data, such as wait events, SQL statements, and system statistics, at regular intervals. AWR data is collected automatically by the database server and is used by various Oracle tools, including ADDM, to identify performance issues.
When to use AWR?
AWR is useful when you need to diagnose and troubleshoot performance issues in your database. You can use AWR to identify the root cause of a slow-running SQL statement or a high CPU usage problem. AWR data can also be used to monitor database performance over time and track changes in performance.
Advantages of AWR:
Automatic data collection: AWR collects performance-related data automatically, so you don't have to manually collect the data.
Historical data: AWR stores performance-related data for a long time, so you can track changes in performance over time.
Low overhead: AWR has a low overhead on the database server, so it can run continuously without affecting the performance of the database.
Disadvantages of AWR:
Limited scope: AWR only collects performance-related data, so it may not be helpful for diagnosing issues that are not related to performance.
Limited resolution: AWR data is collected at regular intervals, so it may not capture short-lived performance issues.
What is Oracle ADDM?
ADDM is a tool that analyzes AWR data to identify performance issues and provide recommendations for resolving them. ADDM analyzes the AWR data to identify the top SQL statements, wait events, and other performance-related issues.
When to use ADDM?
ADDM is useful when you need to quickly identify and resolve performance issues in your database. ADDM provides recommendations for resolving performance issues, making it easier for DBAs to take corrective actions.
Advantages of ADDM:
Quick analysis: ADDM analyzes AWR data quickly and provides recommendations for resolving performance issues.
Actionable recommendations: ADDM provides actionable recommendations for resolving performance issues, making it easier for DBAs to take corrective actions.
Automation: ADDM automates the performance tuning process, making it easier for DBAs to identify and resolve performance issues.
Disadvantages of ADDM:
Limited scope: ADDM only analyzes AWR data, so it may not be helpful for diagnosing issues that are not related to performance.
Limited resolution: ADDM provides high-level recommendations for resolving performance issues, so it may not be helpful for diagnosing complex performance issues.
Conclusion:
In summary, AWR and ADDM are two powerful performance tuning tools that can help DBAs to diagnose and resolve performance issues in their databases. AWR is useful for monitoring database performance over time and diagnosing performance issues, while ADDM is useful for quickly identifying and resolving performance issues. DBAs should use both tools in conjunction to get a comprehensive view of database performance and to resolve performance issues quickly and efficiently.
