AWR (Automatic Workload Repository) is one of the most important Oracle DBA tools for performance analysis. It captures database workload statistics and helps identify bottlenecks such as slow SQL, high CPU usage, I/O issues, waits, and memory problems.

Types of AWR Reports

1. Single Instance AWR Report

Used for standalone databases.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

2. RAC AWR Report (Global AWR)

Generates a report across all RAC nodes.

@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

3. RAC Instance-Specific AWR

Generates AWR for a specific RAC instance.

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

4. Global RAC Compare Report

Compare two RAC periods.

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

5. Compare AWR Reports

Compare two snapshots or periods.

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Check Available Snapshots

SELECT snap_id,
       begin_interval_time,
       end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id;

Verify AWR is Enabled

“The statistics_level parameter should be TYPICAL or ALL. If it is BASIC, AWR data collection will not work properly.”

show parameter statistics_level;

Check Existing Snapshots

Oracle automatically collects snapshots every hour by default. These snapshots store database performance statistics. An AWR report compares two snapshots and shows what happened between them.

SELECT snap_id,
       begin_interval_time,
       end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id;

Generate AWR Report

-- Connect with SYS user
@?/rdbms/admin/awrrpt.sql
Oracle ask:
Enter value for report_type: html
Enter value for begin_snap:
Enter value for end_snap:
Enter value for report_name:

For more information and execution watch this video: