Generate AWR, ASH, ADDM Reports from SQL Prompt

In Oracle databases, performance reports like AWR (Automatic Workload Repository), AWRDD (AWR Daily), ASH (Active Session History), and ADDM (Automatic Database Diagnostic Monitor) are essential for monitoring and troubleshooting database performance. This guide provides a step-by-step process to generate these reports from the SQL prompt.

AWR Report:

The AWR (Automatic Workload Repository) report provides insights into database performance over a specific period. It helps in identifying issues related to resource consumption and performance bottlenecks.

Steps to Generate AWR Report:

SQL> @?/rdbms/admin/awrrpt.sql

You will be prompted to enter the following:

• Start and End Snapshot IDs: You can specify the snapshot IDs for the report period.
• Report Format: Choose between HTML or text formats.

AWRDD Report:

The AWRDD (AWR Daily) report focuses on daily performance snapshots. It is helpful for daily monitoring and identifying trends in the database.

Steps to Generate AWRDD Report:

SQL> @?/rdbms/admin/awrddrpt.sql

This command generates a daily performance report in the specified format.

ASH Report:

ASH (Active Session History) provides real-time data on active sessions in the database. This report is valuable for identifying sessions that are consuming resources at any given time.

Steps to Generate ASH Report:

SQL> @?/rdbms/admin/ashrpt.sql

You will be prompted to enter:

• Begin Time: Specify the start time of the report.
• Duration: Define how long you want the report to cover in minutes.
• Report Name: Specify the report file name.

Example of input prompts:

Enter value for begin_time: 28/12/24 00:00:00
Enter value for duration: 60
Enter value for report_name: ORCL_ashrpt_1_0417_1100.html

ADDM Report:

The ADDM (Automatic Database Diagnostic Monitor) report analyzes the database’s performance and provides recommendations for improvement based on the historical data.

Steps to Generate ADDM Report:

SQL> @?/rdbms/admin/addmrpt.sql

This command will prompt you for details like the time window of the analysis and the format (HTML or text) of the report.

Conclusion: These reports—AWR, AWRDD, ASH, and ADDM—offer comprehensive insights into database performance and help in identifying and troubleshooting issues effectively. By following the above steps, you can easily generate these reports from the SQL prompt to monitor and optimize your Oracle database.