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.

[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;

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

Leave a Comment:

25 comments

vijay says November 25, 2008

this is very good explanation and is clear

Narinder says December 29, 2008

good one

shailupatil says March 5, 2009

good one

Paul says March 11, 2009

Good Article. Thanks Rajat.

Mushtaq Momen says March 30, 2009

Short by Clear and to the point. Quick starter.

Sanjeev Nanda says September 4, 2009

hi,
Can you tell me what is the basic difference between AWR and STACKPACK.
What are the advantage in AWR which is not possible in stackpack.

regards
sanjeev

Preetam says September 8, 2009

Fantastic…..

venkat says October 7, 2009

you have given a clear picture on how to generate awr report.I am a beginer in performance tuning.Please can u tell me how to analyze the awr report and how to to give the solution for that.

himansu says January 16, 2010

hiiiiiiiiiiiiiii
gd sortest explanation to awr report
thnxxxxxxxxx

MRK says January 20, 2010

Good article to run AWR report….thanks

but would you please guide me how to analyse the AWR report ? or any good document on it.

Bidya says March 3, 2010

Good Artical to get the AWR report.
But i need analyze the report to resolve the perdormance.Please give some idea about this or send some documents on this.

Elango says March 22, 2010

Ya really good article, its really good way to get awr report using manually, thanks its useful for me.
And i need how to analysis table,user,schema level and how can query for that , plz help me

Sanjeev Nanda says March 30, 2010

hi,
how to purge AWR statistics.

Regards
Sanjeev

Rajat says March 31, 2010

Check this metalink note ID 287679.1, hope will slove all ur probelm .

regards
Rajat Dey

Rajat says March 31, 2010

Bidya , MRK ,ELANGI and ALL

Generate AWR report , you need to analyse in diff level after generating , now it depends on what type of problem you are facing . Easy way i am telling you . Generate report …Register urself in the below site
http://www.txmemsys.com/statspack-reg.htm

They will provide u a link … After generating this report …COPY paste report
in the below URL immediately u will get output

Sanjeev Nanda says April 1, 2010

Thanks Rajat.Rajat one more query
Can you tell me what is the basic difference between AWR and STACKPACK.
What are the advantage in AWR which is not possible in stackpack.

Rajat says April 5, 2010

Hi Sanjeev ,

1. AWR is 20 to 30 mins faster than statspack

2. No need to install it installed by default

3. No need to schedule .

4. STATSPACK does not store the Active Session History (ASH) statistics which are available in the AWR dba_hist_active_sess_history view.

Sanjeev Nanda says April 6, 2010

Hi Rajat,
Thanks Rajat , good explanation .Your article really good.

Rajat says April 7, 2010

Please ignore

1st point .. i made mistake while writting .

Satish D Amalkar says May 4, 2010

Hi Rajat,

Good one, can be referred readily.

Thanks,

Satish

Oleg says November 27, 2010

AWR – Automatic Workload Repository

Mallu says October 31, 2011

hi sir,
Will you send your e-mail ID,if you dont mind.Its my request.

Regards,
Mallu.

Mallu says November 2, 2011

while we wil select the begin snap ID and end snap ID the following error wil come.Why this error wil come.what is the region.
“The instance was shutdown between snapshots … and …”

Asim Mansuri says June 11, 2014

Good One. Thanks…

kalayn says May 21, 2015

Hello sir,
I am using WLS 11g in our Worksapce .. I heard that AWR Report is a good report tool we can take for DB (Oracle In myn case) ..Now what i want is is The AWR report is Avilable for WLS ?? Please let me know . and reply me to my mail id “nlkalyan.434@gmail.com”

Thnakyou!!!

Add Your Reply