Oracle Database 10g New Advisor Features: The Oracle Database Advisors
President gets advisors; lawyers take things under advisement; when
you go to college, you are assigned an advisor; so why shouldn't
Oracle DBAs have some good advisors to count on? Well, in Oracle
Database 10g, we do. This article is the first in a series that
will uncover the Oracle Database 10g Advisors in some detail.
In this article, we will begin by introducing a principal
infrastructure component that the advisors are built around, the
Advanced Workload Repository.
Oracle Database 10g's Intelligent Automated
Oracle Database 10g advisors depend, in large part, on the new
intelligent, automated infrastructure in Oracle database 10g.
This automated infrastructure consists of the following components:
Advanced Workload Repository (AWR)
2. The Automated Database Diagnostic Monitor (ADDM)
article we will discuss the AWR. In the next article, we will cover
the ADDM, which is also an advisor in its own right.
has been described (by Tom Kyte, I believe) as Statspack on steroids.
AWR collects database statistics every 60 minutes out of the box (this
is configurable), and this data is maintained for a week and then
purged. The statistics collected by AWR are stored in the database. To
properly collect database statistics, the parameter
be set to TYPICAL (the
default) or ALL.
Oracle database uses AWR for problem detection and analysis as well as
for self-tuning. A number of different statistics are collected by the
AWR including wait events, time model statistics, active session
history statistics, various system and session-level statistics,
object usage statistics, and information on the most
resource-intensive SQL statements. Other Oracle Database 10g
features use the AWR, including ADDM and the other advisors in Oracle
Database 10g that we will discuss in this series of articles.
want to explore the AWR repository, feel free to do so. The AWR
consists of a number of tables owned by the
SYS schema and
typically stored in the SYSAUX
tablespace (currently no method exists to move these objects to
another tablespace). All AWR table names start with the identifier "WR."
Following WR is a mnemonic that identifies the type designation of the
table followed by a dollar sign ($). AWR tables come with three
different type designations:
Historical data (WRH$)
tables related to advisor functions (WRI$)
the AWR table names are pretty self-explanatory, such as
Oracle Database 10g offers several DBA tables that allow you to
query the AWR repository. The tables all start with
DBA_HIST, followed by a
name that describes the table. These include tables such as
Manually Managing the AWR
AWR is meant to be automatic, provisions for manual operations
impacting the AWR are available. You can modify the snapshot
collection interval and retention criteria, create snapshots, and
remove snapshots from the AWR. We will look at this process in more
detail in the next few sections.
Manual snapshot collection and retention
You can modify the snapshot collection interval using the
package. The procedure
dbms_workload_repository.modify_snapshot_settings is used in
this example to modify the snapshot collection so that it occurs every
15 minutes, and retention of snapshot data is fixed at 20160 minutes:
-- This causes the
repository to refresh every 15 minutes
-- and retain all data for 2 weeks.
(retention=>20160, interval=> 15);
(Setting the interval parameter to 0 will disable all statistics
the current retention and interval settings of the AWR, use the
view. Here is an example of how to use this view:
SELECT * FROM dba_hist_wr_control;
RETENTION ---------- -------------------- --------------------
2139184330 +00000 01:00:00.0 +00007 00:00
example, we see that the snapshot interval is every hour (the
default), and the retention is set for seven days.
Creating or removing snapshots
You can use the
dbms_workload_repository package to create or remove snapshots.
dbms_workload_repository.create_snapshot procedure creates a
manual snapshot in the AWR as seen in this example:
see what snapshots are currently in the AWR by using the
as seen in this example:
ORDER BY 1;
1107 03-OCT-04 01.24.04.449 AM
1108 03-OCT-04 02.00.54.717 AM
1109 03-OCT-04 03.00.23.138 AM
1110 03-OCT-04 10.58.40.235 PM
snapshot is assigned a unique snapshot ID that is reflected in the
SNAP_ID column. If you
have two snapshots, the earlier snapshot will always have a smaller
SNAP_ID than the later
END_INTERVAL_TIME column displays the time that the actual
snapshot was taken.
Sometimes you might want to drop snapshots manually. The
dbms_workload_repository.drop_snapshot_range procedure can be
used to remove a range of snapshots from the AWR. This procedure takes
two parameters, low_snap_id
and high_snap_id, as
seen in this example:
AWR Snapshot Reports
provides reports that you can run to analyze the data in the AWR.
These reports are much like the statspack reports prior to Oracle
Database 10g. There are two reports:
awrrpti.sql, which are
available in the directory
output of these reports is essentially the same, except that
allows you to define a specific instance to report on.
reports are much like the statspack reports of old, in that you define
a beginning and ending snapshot ID, and the output filename of the
report. Additionally, you can opt to produce the report in either text
format or HTML format.
frequently a good idea to create a baseline in the AWR. A baseline is
defined by a range of snapshots that can be used to
compare to other pairs of snapshots. The Oracle database server will
exempt the snapshots assigned to a specific baseline from the
automated purge routine. Thus, the main purpose of a baseline is to
preserve typical runtime statistics in the AWR repository, allowing
you to run the AWR snapshot reports on the preserved baseline
snapshots at any time and compare them to recent snapshots contained
in the AWR. This allows you to compare current performance (and
configuration) to established baseline performance, which can assist
in determining database performance problems.
section, you will learn how to create baselines, remove baselines, and
how to use baselines.
You can use the
create_baseline procedure contained in the
dbms_workload_repository stored PL/SQL package to
create a baseline as seen in this example:
(start_snap_id=>1109, end_snap_id=>1111, -
Baselines can be seen using the
as seen in the following example:
baseline_name, start_snap_id, end_snap_id
BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
----------- --------------- ------------- -----------
1 EOM Baseline 1109 1111
case, the column BASELINE_ID
identifies each individual baseline that has been defined. The name
assigned to the baseline is listed, as are the beginning and ending
You can remove a baseline using the
dbms_workload_repository.drop_baseline procedure as seen in
this example that drops the "EOM Baseline" that we just created.
(baseline_name=>'EOM Baseline', Cascade=>FALSE);
that the cascade parameter will cause all associated snapshots to be
removed if it is set to TRUE; otherwise, the snapshots will be cleaned
up automatically by the AWR automated processes.