Database Reference
In-Depth Information
Be aware that the length of the interval at which snapshots are taken is of paramount importance. In fact,
intervals of one hour or longer are generally less useful than shorter ones. There are two main reasons. First,
computing a rate or an average over a too-long period of time can be very misleading. Second, since the information
provided by some dynamic performance views is highly volatile, at the time a snapshot is taken, useful information
might no longer be available. For example, a SQL statement that consumes a lot of resources might be missing from
a snapshot because it gets removed from the library cache before the snapshot is taken. Therefore, I usually advise
using an interval of 20 or 30 minutes.
The key differences between AWR and Statspack are summarized in Table
5-1
. Since AWR is more powerful than
Statspack, you should use AWR except when licensing requirements prevent you from doing so.
Table 5-1.
Key Differences Between AWR and Statspack
Automatic Workload Repository
Statspack
Tightly integrated with the database engine and, therefore,
automatically installed and managed.
Manually installed and managed by the DBA.
Stores system-level and SQL statement-level information, as well
as session-level information based on Active Session History.
Stores system-level and SQL statement-level
information only.
Content can be leveraged through Enterprise Manager.
No Enterprise Manager integration.
Content is used by advisories to automatically diagnose
performance issues.
Content isn't used by advisories.
Requires Oracle Diagnostics Pack option and, therefore,
Enterprise Edition.
Freely available in all editions.
Can't be used on a standby database open in read-only mode.
From version 11.1 onward, can be used on a
standby database open in read-only mode.
Automatic Workload Repository
This section describes how to configure AWR, take snapshots, and manage baselines. How to take advantage of the
information stored in AWR is described later, in the “Analysis With Diagnostics Pack” section. For the moment, it's
important to know only that information stored in AWR is exposed through data dictionary views that have the
dba_hist
prefix (in a 12.1 multitenant environment, views with the
cdb_hist
prefix also exist).
Performing Configuration
AWR is automatically installed and configured on every database. As a result, from the very beginning of its existence,
the database engine takes snapshots describing the workload that the database is subject to.
■
When the
statistics_level
initialization parameter is set to
basic
, the database engine doesn't
automatically take snapshots.
Caution
The configuration is based on three parameters:
•
Snapshot interval
: The interval (in minutes) between two snapshots. The minimum and
maximum values are 10 minutes and 100 years, respectively. The default value is 1 hour.
