AWR, ADDM and ASH Reports Quick Reference | Oracle Database

Difference between AWR, ADDM and ASH Reports in Oracle Performance Tuning

What is AWR (Automatic Workload Repository) Report in Oracle Database and what kind of information does it provide? What is ADDM (Automatic Database Diagnostic Monitoring) Report in Oracle Database and what kind of Information does it provide? What is ASH (Active Session History) Report and what kind of Information does it provide in Oracle Database?

AWR (Automatic Workload repository) Report:

Automatic Workload Repository gathers, processes and maintains performance stats used for problem detection and self-tuning the databases. This collective information can be displayed as views and reports (HTML/text) to better show the current database performance status for further analysis.

AWR gathers the following data

• Object Statistics (access / usage stats of DB segments)
• Time Model Statistics (V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views)
• Some of the System and Session Statistics (V$SYSSTAT and V$SESSTAT views)
• ASH (Active Session History) Statistics
• High load generating SQL Statements

Different Components that uses AWR are:

• Automatic Database Diagnostic Monitor
• Undo Advisor
• SQL Tuning Advisor
• Segment Advisor

By default, Snapshots are generated every hour and retains the statistics in the repository for 8 days. Please find below if you intend to change the default settings:

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 14400,
interval => 30, topnsql => 100, dbid => 12345678);
END;
/

Here ,
Retention = 14400 minutes (10 days) Snapshot retention period
Interval = 30 minutes Snapshot interval period
topnsql = Number of Top SQL to flush
dbid = database identifier

Different types of AWR Reports for different purposes:

For Single Instance Environment:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

For Oracle RAC Environment :
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

For a particular SQL Statement :
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

For Comparing the reports :
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

ADDM (Automatic Database Diagnostic Monitoring) Report:

Automatic Database Diagnostic Monitoring Report analyzes the AWR data on a regular basis, to give you overview of the root cause of the problem which is affecting your database's performance. It also provides suggestions or recommendations for rectifying any problem identified and lists the areas which are having no issues. ADDM recommends multiple solutions for the DBA to choose from which includes,

• Database Configuration
• Hardware changes
• Schema changes
• Application changes & 
• Other advisories

ADDM refines the report based on the key factor DB time (cumulative time spent by database to process user requests). Below mentioned is the script to generate ADDM:

@$ORACLE_HOME/rdbms/admin/addmrpt.sql

ASH (Active Session History) Report:

Active Session History Reports shows you the below mentioned stats regarding your Oracle Database,

• Transient performace problems (usually not captured in ADDM reports)
• Time / session / action /module / SQL_ID based target performance analysis

Below mentioned is the script which you can run to generate your ASH report,

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

For Oracle RAC Environment :

@$ORACLE_HOME/rdbms/admin/ashrpti.sql