What is AWR( Automatic workload repository ) and How to generate the AWR report

(AWR) Automatic workload repository is a collection of persistent system performance statistics owned by SYS. It resides in SYSAUX tablespace. By default snapshot are generated once every 60 min and maintained for 7 days. Each snapshot has a unique ID know as "snap_id". Snapshot detail can be found in "dba_hist_snapshot" view.

So before generating the AWR Report let's find time interval for which we need AWR Report.

Suppose we need AWR Report for 05-MAY-11. To achieve this we need snapshot ID for this data.

How to find Snap Id: Select from dba_hist_snapshot view which have two fields "BEGIN_INTERVAL_TIME" and "END_INTERVAL_TIME". These fields gives exact value for time this snap shot belongs.

From above table select your snap_id in the meantime for 05-MAY-11. In this case starting snap_id is 2676 and it goes up to 2699.

For more detail about dba_hist_snapshot Please visit:

Steps to Generate AWR Report:

1. Log in to database:

 [oracle@test ~]$ export ORACLE_SID=test01
[oracle@test ~]$ sqlplus sys as sysdba

2. Execute command for Report generation:

 SQL> @ $ORACLE_HOME/rdbms/admin/awrrpt.sql

3. Choose Report Type: It will prompt for AWR report type. This report can be generated in two types "HTML" and "TEXT". Default is HTML

 Specify the Report Type
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: HTML

4. Snap details:You can give any value it will show you all snap id's and detail for given no of days.
Enter value for num_days: 2

5. Specify the Begin and End Snapshot Ids:
Enter value for begin_snap:2676

Enter value for end_snap: 2699

Here you need to give begin and end snap id.

6. Specify Report Name: Default is awrrpt_1_begin_span_id_end_snap_id.html. Press enter for default name or give new name.

Specify the Report Name
The default report file name is awrrpt_1_2676_2699.html.  To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:

7. Report generated:  your report is generated at present working directory.

Report written to awrrpt_1_2676_2699.html

8. To get the report: 

SQL> exit;

[oracle@test]$ pwd

9. Read AWR Report: Go to /home/oracle find file as "awrrpt_1_2676_2699.html"
This is your AWR report generated.

Now DBA has generated AWR Report, So next step is to analyze AWR Report


  1. This is a nice start. And it is very useful site for the resolving the issues.

    However we need more information on oracle issues. So please keep on updating this site.