Generate AWR, ASH, ADDM Reports from SQL Prompt

Oracle provides built-in tools to generate performance reports like AWR, AWRDD, ASH, and ADDM. These reports help identify resource issues, long-running queries, and system bottlenecks.

This guide shows how to run each report directly from the SQL prompt using Oracle-provided scripts.

1. AWR Report (Automatic Workload Repository)

The AWR report gives a snapshot of performance statistics between two time intervals. Use it to find I/O, CPU, or wait bottlenecks.

Generate AWR Report

-- Run AWR report script
@?/rdbms/admin/awrrpt.sql

📌 You’ll be prompted for:

• DBID or default
• Snapshot begin/end IDs
• Report format (HTML or TEXT)
• Report file name

2. AWRDD Report (AWR Daily)

The AWRDD report summarizes a full day’s performance using AWR snapshots.

Generate AWRDD Report

-- Run AWR daily report script
@?/rdbms/admin/awrddrpt.sql

📌 You’ll enter:

• Snapshot dates
• Report format
• Output file name

3. ASH Report (Active Session History)

The ASH report captures active session details within a specific time range. It’s ideal for spotting spikes or unusual activity.

Generate ASH Report

-- Run ASH report script
@?/rdbms/admin/ashrpt.sql

📌 You’ll be asked to enter:

• Begin time (e.g., 10/06/25 14:00:00)
• Duration in minutes (e.g., 30)
• Report file name (e.g., ORCL_ashrpt_30min.html)

4. ADDM Report (Automatic Diagnostic Monitor)

The ADDM report provides root cause analysis and suggestions to improve performance using past AWR data.

Generate ADDM Report

-- Run ADDM report script
@?/rdbms/admin/addmrpt.sql

📌 Prompts include:

• Begin and End snapshot IDs
• Report format
• Output file name

Summary Table

📌 Conclusion

These reports provide critical insights into database health and workload trends. By running them from the SQL prompt, you can analyze system behavior without needing any extra tools.