AWR Report

Automatic Workload Repository (AWR) in Oracle

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).The AWR is a repository of performance information collected by the database to aid in the tuning process for DBAs.

Oracle 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically when we create a new Oracle database. We can disable and enable the schedule job by following command:

we can disable this job by using the dbms_scheduler.disable procedure as below :

Exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

And we can enable the job using the dbms_scheduler.enable procedure as below :

Exec dbms_scheduler.enable(’GATHER_STATS_JOB’);

AWR consists of a collection of performance statistics including :

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the v$sess_time_model and v$sys_time_model views.
• Active Session History (ASH) statistics from the v$active_session_history view.
• Some system and session statistics from the v$sysstat and v$sesstat views.
• Object usage statistics.
• Resource intensive SQL and PL/SQL.

The resource intensive SQL and PL/SQL section of the report can be used to focus tuning efforts on those areas that will yield the greatest returns.  The statements are ordered by several criteria including :

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory

Several of the automatic database tuning features require information from the AWR to function correctly, including:

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

 How to generate AWR report ?  

There are two scripts that are provided by oracle to generate the AWR report. The scripts are available in the directory  $ORACLE_HOME\rdbms\admin. The two scripts are 

1.) awrrpt.sql   : If we have only One Oracle Database then run awrrpt.sql sql script.

2.) awrrpti.sql  : If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the below procedure :

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.

    interval  => 15);          -- Minutes. Current value retained if NULL.

END;

/

                  Here we have  alter the snapshot interval to 15min. It is recommended that 15 Minutes is enough in two snapshot for better performance bottleneck.

AWR using Enterprise Manager    :   The automated workload repository administration tasks have been included in Enterprise Manager. The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section. The page allows us to modify AWR settings or manage snapshots without using the PL/SQL APIs.

Here is the Demo of the AWR report .

C:\>sqlplus sys/xxxx@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 16 11:42:19 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @D:\app\Neerajs\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id                       DB Name        Inst Num          Instance

-----------                      ------------           --------          ------------

 1281052636                ORCL                1                    orcl

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

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id                        Inst Num       DB Name        Instance           Host

------------                      --------           ------------        ------------        ------------

* 1281052636                1                   ORCL              orcl                xxxx

Using 1281052636 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> without

specifying a number lists all completed snapshots.

Enter value for num_days:       (Press Enter to see all the snapshots)

Listing all Completed Snapshots

                                                       

Instance     DB Name        Snap Id       Snap Started               Level

---------       ------------         ---------         ------------------              -----

orcl             ORCL                 1             08 Jun 2011 11:30          1

                                               3             08 Jun 2011 14:41         1

                                               4             08 Jun 2011 15:30         1

                                 .

 Data is truncated

         .            

                                                120           16 Jun 2011 05:30       1

                                                121           16 Jun 2011 06:30       1

                                                122            16 Jun 2011 07:30      1

                                                123            16 Jun 2011 08:30      1

                                                124            16 Jun 2011 09:30      1

                                                125            16 Jun 2011 10:30      1

                                                126            16 Jun 2011 11:30      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 125

Begin Snapshot Id specified: 125

Enter value for end_snap: 126

End   Snapshot Id specified: 126

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_125_126.html.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:          (Press enter if you want to use the above name)

Using the report name awrrpt_1_125_126.html

.

.

Report is truncated 

.

.

End of Report

</body></html>

Report written to awrrpt_1_125_126.html

SQL>

In the above report the line which are shaded with red colour are the entered values when it prompts.

 

How To Understand AWR Report / Statspack Report ?

How To Understand AWR Report / Statspack Report
==============================================

script is here $ORACLE_HOME\RDBMS\ADMIN\
awrrpt.sql
awrrpti.sql

execute like below

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jul 10 14:37:04 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> CONN sys@stlbas105 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> @G:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2515622958 STLBAS 1 stlbas


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: text

Type Specified: text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2515622958 1 STLBAS stlbas TESTSERVER

Using 2515622958 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 without
specifying a number lists all completed snapshots.


Enter value for num_days:
.......................
.........................
.........................
..........................
----------------------------------------------------------------------------

This document shows you some basic checks to be done from AWR to identify the problem.

Older Database versions like 8i,9i have Statspack report only.

From 10g onwards, AWR is available along with Statspack.
Automatic Workload Repository (AWR) : The Automatic Workload Repository (AWR) provides information to different manageabilities components. AWR consists of two components: in-memory statistics accessible through V$ dynamic views, and AWR snapshots saved in the database that represent the persistent and historical portion.

AWR snapshots can be generated at will using the following syntax:

EXECUTE dbms_workload_repository.create_snapshot();

By default in 10g, the AWR snapshots are generated automatically on hourly basis.

If you are facing any performance problem in the database and you have license for AWR,
then AWR reports can be generated for the problem period.

If there is no proper license for AWR available then statspack report can be generated.

The AWR/Statspack report should be taken for the interval not more than 60 minutes during problem.

Please dont take AWR / Statspack report for duration of like five or six hours as that would not be reliable.

The AWR report can be taken in both html/text format.
1) The first thing to be checked in AWR report is the following:-

Snap Id Snap Time Sessions Cursors/Session

Begin Snap: 112 11-Jun-09 00:00:57 191 6.7
End Snap: 113 11-Jun-09 01:00:11 173 7.4
Elapsed: 59.23 (mins)
DB Time: 710.73 (mins)

Check the "DB Time" metric. If it is much higher than the elapsed time, then it indicates that the sessions are waiting for something.

Here in this example, the Elapsed Time is around 60 minutes while the DB Time is around 700 minutes. This means that 700 minutes of time is spent by the sessions on waiting.

2) Next thing to be looked is the following:-

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.67 In-memory Sort %: 100.00
Library Hit %: 98.60 Soft Parse %: 99.69
Execute to Parse %: 5.26 Latch Hit %: 99.31
Parse CPU to Parse Elapsd %: 12.78 %Non-Parse CPU: 99.10

As per the thumb rule, Instance Efficieny Percentages should be ideally above 90%.
3) Then comes the Shared Pool Statistics.

Shared Pool Statistics

Begin End
Memory Usage %: 85.49 80.93
% SQL with executions>1: 42.46 82.96
% Memory for SQL w/exec>1: 47.77 81.03

The memory usage statistics of shared pool is shown.
Idealy this should be lesser. If it is very high like beyond 90, this shows the contention
in the shared pool.
4) Next thing to be looked after is the Top 5 Timed Events table.
This shows the most significant waits contributing to the DB Time.

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read 4,076,086 28,532 7 66.9 User I/O
CPU time 11,214 26.3
Backup: sbtbackup 4 4,398 1,099,452 10.3 Administrative
log file sync 37,365 2,421 65 5.7 Commit
log file parallel write 37,928 1,371 36 3.2 System I/O

Here, the significant wait is the db file sequential read which contributes to 67% of DB Time.

5) Then , SQL Statistics can be checked.

SQL Statistics

SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by Gets
SQL ordered by Reads

SQL Statistics section would have commonly the above four sections.

Each section shows the list of SQLs based on the order of the respective metric.
For example, SQL ordered by Elapsed Time section shows the list of SQLs in the order
of the Elapsed Time. High resource consuming SQLs can be spotted out and meant for
tuning.

Note: All the above four sections of SQL Statistics show the list of SQLs in descending order.
i.e, For ex: Highest elapsed time is shown as first.

6) Then comes the IO Stats section.

This shows the IO Statistics for each tablespaces in the database.
As the thumb rule, the Av Rd(ms) [Average Reads in milliseconds] should not cross beyond 30, add myself(not greater that 30)
which is considered to be IO bottleneck.

Tablespace IO Stats
ordered by IOs (Reads + Writes) desc

Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
TEMP 3,316,082 933 4.91 1.00 28,840 8 0 0.00
DAT1 520,120 146 16.06 1.21 185,846 52 902 13.00
DAT3 93,411 26 42.82 2.98 13,442 4 16 23.13
DAT2 98,171 28 91.97 7.97 5,333 2 325 34.89

In the above example, the Av Rd(ms) is high in all tablespaces indicating the IO contention.

7) Then , Advisory Statistics can be checked.

This section shows the following:-

Buffer Pool Advisory
PGA Aggr Summary
PGA Aggr Target Stats
PGA Aggr Target Histogram
PGA Memory Advisory
Shared Pool Advisory
SGA Target Advisory
Streams Pool Advisory
Java Pool Advisory

It is very commonly used to check the advisories for the most important SGA structures like shared pool, buffer cache etc and PGA.

8) Then finally, init.ora Parameters is shown which shows the list of parameters set at instance level.

init.ora Parameters

All the above said sections except the DB Time can be checked from Statspack report also.

The statspack snapshots are not generated automatically as in AWR.
It has to be generated during the problem period as follows:-

Take 2 snapshots between 60 minutes interval during the problem and generate the statspack report

exec statspack.snap
wait for 60 minutes
exec statspack.snap

Please run $ORACLE_HOME/rdbms/admin/spreport.sql
and specify BEGIN and END ID's of the snapshots taken during the problem.

The above said sections are the most common checks can be performed from user level.
Further intensive checking can be done through Oracle Support.