Yes, AWR and ADDM reports are essential for Oracle performance tuning. 1. AWR reports provide snapshots of database activity, showing top SQL, wait events, resource usage, and trends over time—useful for identifying inefficient queries and cache effectiveness. 2. ADDM analyzes AWR data to detect issues like poor SQL plans, missing indexes, contention, and memory misconfigurations, offering prioritized recommendations with root cause analysis. 3. Together, they enable a methodical approach: use ADDM for high-level insights and actionable advice, then validate findings in AWR with detailed metrics to address real bottlenecks effectively.

Yes, AWR and ADDM reports are two of the most powerful tools for Oracle database performance tuning. They provide deep insight into system behavior, help identify bottlenecks, and suggest actionable recommendations—all based on real data collected over time.

Here’s how they contribute:

1. AWR Reports: The Performance Snapshot

The Automatic Workload Repository (AWR) captures a detailed snapshot of database activity at regular intervals (default is every hour). These snapshots include metrics like SQL execution times, wait events, resource usage, and more.

What you can learn from an AWR report:

• Which SQL statements are consuming the most resources (CPU, I/O).
• Where the database is spending its time—through wait event analysis.
• Trends in performance over time when comparing multiple reports.
• How efficiently memory structures like buffer cache and shared pool are being used.

How to use it effectively:

• Focus on the "Top SQL" section to find problematic queries.
• Check "Load Profile" to understand transaction volume and system load.
• Compare reports from peak and off-peak hours to spot anomalies.
• Look at "Instance Efficiency Percentages" to see if key caches are sized correctly.

For example, if you notice high "SQL*Net message from client" waits, that might indicate inefficient application logic or long-running transactions—not necessarily a DBA issue alone.

2. ADDM Reports: Actionable Recommendations

ADDM automatically analyzes AWR data and provides expert-level tuning advice. It's like having a built-in Oracle performance consultant.

Common issues ADDM can detect:

• Inefficient SQL execution plans
• Lack of proper indexing
• Contention on shared resources (like latches or locks)
• Improper configuration of memory pools

Key benefits:

• It prioritizes findings by impact—so you know what to fix first.
• Each finding comes with specific recommendations, such as rewriting a query or increasing SGA size.
• It often identifies root causes, not just symptoms.

Say ADDM flags “high buffer busy waits.” It might suggest adding freelists, using ASSM, or even changing the application access pattern—depending on what's causing the contention.

3. Combining AWR and ADDM for Better Diagnostics

Using these two together gives you both the raw data (AWR) and intelligent interpretation (ADDM):

• Run ADDM first to get a summary of problems and suggested fixes.
• Then dive into the corresponding AWR report to validate those findings and dig deeper into the numbers.
• If ADDM suggests a particular SQL is problematic, pull that SQL from AWR and analyze its execution plan.

This workflow ensures you're not just reacting to symptoms but addressing real performance bottlenecks.

In short, AWR gives you the facts, and ADDM gives you the guidance. Used together, they make performance tuning much more efficient and data-driven.

That’s basically it—no magic, just methodical analysis.

The above is the detailed content of How can Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) reports aid in performance tuning?. For more information, please follow other related articles on the PHP Chinese website!