Download the post in pdf version here:
1- AWR Concept:
In this post, I am going to show you how to generate AWR reports for Real Application Clusters (RAC) environment.
AWR was first introduced in 10g version of Oracle as a repository of all important statistics for an Oracle database. At a regular intervals, Oracle makes a snapshots of those vital statistics and stores them in SYSAUX tablespace. In 11g version, These snapshots are retained for 8 days. It is the MMON background process who is responsible of AWR collection.
AWR scripts allow you to generate the reports in HTML or TEXT formats. These reports allow you to analyze the workload of the database at a given intervals. Oracle extends the AWR tool for RAC databases by adding the possibility to generate snapshots reports against the whole cluster database, some database instances of the cluster database, a specific database instance of the cluster database, statistics for a specific select query. In the following sections, I am going to show you how to generate AWR reports for each case.
2- Generate AWR reports for the current Instance:
Run the awrrpt
script from $ORACLE_HOME/rdbms/admin/
as a sys user. The script will ask you for the output format of the report (HTML or TEXT), the the number of days (n) will result in the most recent
(n) days of snapshots being listed (If you hit enter, you will get all the available snapshots listed), begin and end snapshot interval Ids …
In my Example below, this is the output of a six node RAC database.
-bash-3.2$ cd /opt/app/oracle/product/11.2.0/db_1/rdbms/admin/ -bash-3.2$ -bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 28 13:04:54 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options SQL> @awrrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1399294885 ddb 1 ddb1 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 ------------ -------- ------------ ------------ ------------ 1399294885 6 ddb ddb6 srvdb06 1399294885 4 ddb ddb4 srvdb04 1399294885 2 ddb ddb2 srvdb02 1399294885 5 ddb ddb5 srvdb05 * 1399294885 1 ddb ddb1 srvdb01 1399294885 3 ddb ddb3 srvdb03 Using 1399294885 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. Pressingwithout specifying a number lists all completed snapshots. Enter value for num_days: Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- ddb1 ddb 286 26 Sep 2011 23:00 1 287 27 Sep 2011 00:00 1 288 27 Sep 2011 01:00 1 289 27 Sep 2011 02:00 1 290 27 Sep 2011 03:00 1 291 27 Sep 2011 04:00 1 292 27 Sep 2011 05:00 1 293 27 Sep 2011 06:00 1 294 27 Sep 2011 07:00 1 295 27 Sep 2011 08:00 1 296 27 Sep 2011 09:00 1 297 27 Sep 2011 10:00 1 298 27 Sep 2011 11:00 1 299 27 Sep 2011 12:00 1 300 27 Sep 2011 13:00 1 301 27 Sep 2011 14:00 1 302 27 Sep 2011 15:00 1 303 27 Sep 2011 16:00 1 304 27 Sep 2011 17:00 1 305 27 Sep 2011 18:00 1 306 27 Sep 2011 19:00 1 307 27 Sep 2011 20:00 1 308 27 Sep 2011 21:00 1 309 27 Sep 2011 22:00 1 310 27 Sep 2011 23:00 1 311 28 Sep 2011 00:00 1 312 28 Sep 2011 01:00 1 313 28 Sep 2011 02:00 1 314 28 Sep 2011 03:00 1 315 28 Sep 2011 04:00 1 316 28 Sep 2011 05:00 1 317 28 Sep 2011 06:00 1 318 28 Sep 2011 07:00 1 319 28 Sep 2011 08:00 1 320 28 Sep 2011 09:00 1 321 28 Sep 2011 10:00 1 322 28 Sep 2011 11:00 1 323 28 Sep 2011 12:00 1 324 28 Sep 2011 13:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 323 Begin Snapshot Id specified: 323 Enter value for end_snap: 324 End Snapshot Id specified: 324 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_323_324.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: ... ... End of Report