How to generate AWR report from 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:



interval => 30, topnsql => 100, dbid => 12345678);



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:


For Oracle RAC Environment :


For a particular SQL Statement :


For Comparing the reports :



1) Login to DB Server as oracle 

2) export ORACLE_SID=PrimeDG

3) cd $ORACLE_HOME/rdbms/admin

4) sqlplus / as sysdba

5) @awrrpt.sql


6) Enter value for report_type: html

Note: Since, we want to open the file in nicely formatted way, we pick html format.

7) Enter value for num_days: 7

Note: you can go back upto 7 days.

8) Enter value for begin_snap: 4697

9) Enter value for end_snap: 4704

10) Enter value for report_name: Test_AWR_Report.html

To pull the report, use WinSCP and pull the html file to you laptop. 

10) Start WinSCP and configure it connect to server with “oracle” username. 

11) Copy the file form UNIX (right side) to Windows (left side).

12) Double click the file to open the report in browser


Query to check the snapshot ID in last 24 hours. 

set linesize 200

set pagesize 200

col Begin_interval_time format a30

col end_interval_time format a30








begin_interval_time > sysdate-1

order by


Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.