The top queries are listed and when the value all

Chapter 6 ■ tools and Utilities

Automatic Workload Repository

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level=>ALL);

The number of top SQL queries reported by the AWR is also controlled by the STATISTICS_LEVEL parameter. When the value is TYPICAL, the top 30 queries are listed and when the value is ALL, the top 100 queries are list. This can be overridden using the following procedure:

execute DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (459,476,'QAR1BLINE');

Querying the DB_HIST_BASELINE table can validate the baseline definition.

Chapter 6 ■ tools and Utilities

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.

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

Performs a data pump imports/loads of the AWR data. awrloadsql
awrgrpti.sql Generate RAC-related reports by choosing specific instances in the cluster.

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.