AWR (Automatic workload Repository) Report in Oracle 10G

AWR (Automatic workload Repository)Report in Oracle 10G

AWR is an enhancement/upgradation of statspack, which help us to tune the database.

Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically when you create a new Oracle database under Oracle Database 10g.

GATHER_STATS_JOB, to collect AWR statistics every 1 hour

We can disable and enable the schedule job by following command:

 You can disable this job using the dbms_scheduler.disable procedure as seen in this example:
Exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

And you can enable the job using the dbms_scheduler.enable procedure as seen in this example:
Exec dbms_scheduler.enable(’GATHER_STATS_JOB’);

.
AWR Snapshot Reports
Oracle provides reports that you can run to analyze the data in the AWR. These reports are much like the statspack reports prior to Oracle Database 10g. There are two reports: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/ rdbms/ admin.
         The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on. The reports are much like the statspack reports of old, in that you define a beginning and ending snapshot ID, and the output filename of the report. Additionally, you can opt to produce the report in either text format or HTML format.

[oaPROD usa.com01 PROD] $ sqlplus ‘/as sysdba’
 SQL> @awrrpt.sql

Current Instance   DB Id    DB Name      Inst Num Instance

———– ———— ——– ————
 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 textDefaults to ‘html’
Enter value for report_type: text

Type Specified:  text Instances in this Workload Repository schema
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 DB Id     Inst Num DB Name      Instance     Host
 ———— ——– ———— ———— ————
 01  1141957891        1 PROD        PROD        usa.com    
 02 1141957891        1 GLD1        LGLD1        ind.com                                         

 Using  124707144 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent(n) days of snapshots being listed.  Pressing <return> withoutspecifying a number lists all completed snapshots.  Enter value for num_days:  1

Listing the last day’s Completed Snapshots                                                       

   SnapInstance     DB Name        Snap Id    Snap Started    Level
     ———— ———— ——— —————— —–
  PROD        PROD            
                                6400 14 Oct 2008 00:01      1

                               6401 14 Oct 2008 01:00      1

                               6402 14 Oct 2008 02:00      1

                               6403 14 Oct 2008 03:00      1           

                               6404 14 Oct 2008 04:00      1      

                               6405 14 Oct 2008 05:01      1
 Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 6412

Begin Snapshot Id specified: 6412

Enter value for end_snap: 6413

End   Snapshot Id specified: 6413

Specify the Report Name
The default report file name is awrrpt_1_6412_6413.txt.  To use this name,press <return> to continue, otherwise enter an alternative.

Enter value for report_name:End of ReportReport written to awrrpt_1_6412_6413.txt

————-

You can see what snapshots are currently in the AWR by using the DBA_HIST_SNAPSHOT view as seen in this example:
 SQL> SELECT snap_id, begin_interval_time, end_interval_time  FROM dba_hist_snapshot ORDER BY 1;