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

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. Bydefault 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 Reportlet's find time interval for which we needAWR 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.

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
/home/oracle

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.