Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 148

While AWR provides information concerning RAC, statistics are collected from all instances and stored by an
instance number under a common SNAP_ID ( SNAP_ID is the primary key for the AWR and is generated for every
snapshot collection); in other words, statistics are collected by instance and not at the global level.
AWR reports or snapshot comparisons can be done either using a command line interface by executing an
Oracle-provided SQL script ( $ORACLE_HOME/rdbms/admin/awrrpt.sql ) or using the EM interfaces. An HTML file can
be generated and viewed using a browser by either method.
The script provides an option to pick a range of snapshots to generate comparison report. Once the range is
selected, the report is generated in the default directory. In a RAC environment, starting with Oracle Database11g
Release 2, a cluster/group level summary report can be generated. Similar to the awrrpt.sql script, Oracle provides
several scripts for data collection. These scripts and their purpose are listed in Table 6-1 .

Table 6-1. EM-Provided Scripts

Script Description

awrrpt.sql Generates AWR reports for pairs of snapshot ids. The script defaults to the current database.

awrrpti.sql Generates AWR reports for pairs of snapshot ids. Allows selection of a database.

awrddrpt.sql

Generates a comparison report between two snapshot periods. The reports being compared can
be from the same database.

awrddrpi.sql Generates a comparison report between two AWR reports. The reports being compared can be

from the two different databases.

awrextr.sql Performs a datapump export of the AWR data. This data can be moved to another environment

or database.

awrloadsql Performs a data pump imports/loads of the AWR data.

awrgrpti.sql Generate RAC-related reports by choosing specific instances in the cluster.

awrgrpt.sql Generate RAC-related reports for all available instances in the cluster.

awrgdrpt.sql Generates RAC-related comparison reports between instances in the same database.

awrgdrpti.sql

Generates RAC-related comparison reports between different databases.

awrinfo.sql Helps collect database-related general information. Information collected includes tablespace

usage, schema/option usage, segment usage, retention policies, etc.

awrsqrpt.sql Generates a report for a single SQL statement over multiple snapshots.

awrblmig.sql Migrates the AWR baseline data from renamed BL tables back to the base tables.

addmrpt.sql

Generates ADDM analysis on a pair of AWR snapshots and generates a report.

ashrpt.sql Generates an ASH report for the go live time.

ashrpti.sql Apart from the basic database information, it shows major waits, top services/modules,

top clients, etc.

Tuning, using the AWR, starts with identifying the top 10 foreground wait events reported in the first page of the
report, illustrated in Figure 6-5 .