AWR (Advance 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.
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;
About the Author Rajat
I am Rajat Dey and I have more than decade of Oracle experience from all major regions throughout the globe. I am well versed in most facets of Oracle, supporting database, fusion middleware and the eBusiness suite across many operating system platforms.Currently I am located in Sydney Oracle Financials 11i and Release 12,12.2 Oracle RDBMS 7x,8i , 9i,10g and 11g Amazon cloud . Oracle RAC 10g and 11g Oracle Applications Server Oracle Web logic Server OBIEE Hyperion SSO Oracle Portal PL/SQL UNIX SQL MS-SQL 2008. OEM 12c . Data Guard . Essbase . Database backup and recovery. Performance Tunning. TimeSten