AWR Reports -Create Manual snapshots and Genarate AWR Reports

AWR Reports ,Snap shots Overview

 Automatic workload repository(AWR)  is a collection  of system performance statistics.This stats are owned by SYS user contained in SYSAUX tablespace.Snapshot is a set of performance    statistics  captured  at a certain time and stored in AWR.Snapshots are used to compute the  rate      of  change of a statistics.Each snapshot is identified by a snapshot sequence number (snap_id).By Default snapshot interval is 1 hour.You can change the  frequency by changing  snapshot INTERVAL parameter.

 

You can take manual snapshots by  using  Database Control (EM) and DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT  stored procedure.

• Log in to database using SYS user

• Create a manual snapshot  by executing  the dbms_workload_repository.create_snapshot() procedure.

                                EXEC  dbms_workload_repository.create_snapshot()

• Wait for required time(may be 15 mins,30mins,45 mins )  and then create another manual snap shot by executing dbms_workload_repository.create_snapshot() procedure.

Now you can generate AWR Report using these snapshots.

To generate AWR Reports there is  a script awrrpt.sql located in the  $ORACLE_HOME\RDBMS\ADMIN  folder

• In the SQL command prompt  execute the awrrpt.sql

                     SQL>@C:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpt.sql
 

• This will ask for the  out put file should be a text file or a HTML file.

•  This will ask for number of days  to list down snap_id's.

 

• Then ask for beginning snapshot id and ending snapshot id's.

•  Then it will as for a name for the file name. 

All the step are as follows.