The Oracle Database Advisors by Robert Freeman

The 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 Intelligent Automated Infrastructure

The new 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:

1. The Advanced Workload Repository (AWR)
2. The Automated Database Diagnostic Monitor (ADDM)

In this article we will discuss the AWR. In the next article, we will cover the ADDM, which is also an advisor in its own right.

The AWR

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 statistics_level should be set to TYPICAL (the default) or ALL.

The 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.

If you 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:

• Metadata (WRM$)
• Historical data (WRH$)
• AWR tables related to advisor functions (WRI$)

Most of the AWR table names are pretty self-explanatory, such as WRM$_SNAPSHOT or WRH$_ACTIVE_SESSION_HISTORY.

Also 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 DBA_HIST_FILESTATS, DBA_HIST_DATAFILE, or DBA_HIST_SNAPSHOT.

Manually Managing the AWR

While 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 dbms_workload_repository 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.
Exec dbms_workload_repository.modify_snapshot_settings
(retention=>20160, interval=> 15);

(Setting the interval parameter to 0 will disable all statistics collection.)

To view the current retention and interval settings of the AWR, use the DBA_HIST_WR_CONTROL view. Here is an example of how to use this view:

SELECT * FROM dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION ---------- -------------------- -------------------- 2139184330 +00000 01:00:00.0 +00007 00:00

In this 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. The dbms_workload_repository.create_snapshot procedure creates a manual snapshot in the AWR as seen in this example:

EXEC dbms_workload_repository.create_snapshot;

You can see what snapshots are currently in the AWR by using the DBA_HIST_SNAPSHOT view as seen in this example:

SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1;

   SNAP_ID END_INTERVAL_TIME
---------- -------------------------
      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

Each 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 snapshot. The 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:

EXEC dbms_workload_repository.drop_snapshot_range -
(low_snap_id=>1107, high_snap_id=>1108);

AWR Snapshot Reports

Oracle 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: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/rdbms/admin.

The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on.

The 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.

AWR Baselines

It is frequently a good idea to create a baseline in the AWR. A baseline is defined as 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.

In this section, you will learn how to create baselines, remove baselines, and how to use baselines.

Creating 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:

EXEC dbms_workload_repository.create_baseline -
(start_snap_id=>1109, end_snap_id=>1111, -
baseline_name=>'EOM Baseline');

Baselines can be seen using the DBA_HIST_BASELINE view as seen in the following example:

SELECT baseline_id, baseline_name, start_snap_id, end_snap_id
FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME   START_SNAP_ID END_SNAP_ID
----------- --------------- ------------- -----------
          1 EOM Baseline             1109        1111

In this 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 snapshot IDs.

Removing baselines
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.

EXEC dbms_workload_repository.drop_baseline
(baseline_name=>'EOM Baseline', Cascade=>FALSE);

Note 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.

Managing AWR with Oracle Enterprise Manager Database Control

While we have demonstrated how to use the dbms_workload_repository package to manage the AWR repository, Oracle also provides the ability to manage AWR from the Oracle Enterprise Manager Database Control (OEMDBC). OEMDBC provides a nice interface into the management of AWR. From the OEMDBC home page, go to the administration page as seen in this screen print:

At the bottom of the page, under "Workload," notice the entry for the Automatic Workload Repository. Selecting this option takes you to the AWR page that is seen in this screen shot:

This AWR page provides a summary of the current AWR settings and gives you an option to modify them. You can also look at details about the snapshots in the AWR and create baseline AWR snapshots (called preserved snapshot sets in OEMDBC).

Let's look first at the AWR edit settings page, and then we will look at managing snapshots.

The AWR edit settings page
Press the EDIT button on the AWR page, and you get the AWR edit settings page. The AWR edit settings page allows you to:

• Determine snapshot retention
• Determine how frequently snapshots are collected (or if collection is turned off)
• Determine the current database statistics collection level.

Here is a screen print of the AWR Edit Settings page:

Simply click on the appropriate radio button and/or change the appropriate value, and press OK to commit the change. Also note the show SQL button. This button shows you the SQL that Oracle will execute to complete your change.

Snapshot details
You can also get snapshot details by clicking on the snapshots link on the AWR page which takes you to the snapshots page seen in this screen print:

The snapshots page displays the last several snapshots in the AWR, and allows you to review older snapshots if you wish. You can click on a specific snapshot number if you want detail information on that snapshot or if you want a printable report based on the snapshot you selected.

Preserved snapshots
If you wish to create or manage preserved snapshots sets (also known as baselines), then at the AWR page, click on the preserved snapshot sets link. You will then find yourself at the preserved snapshot sets page as seen in this example:

You can click on the Create Preserved Snapshot Set button at the top right of the screen to create a new snapshot set. Oracle will then prompt you for the beginning and ending snapshots to assign to the preserved snapshot set. Once you have created snapshots, you can use the actions pull-down box to perform many actions such as creating SQL tuning sets (which we will talk about in later articles), create reports much like statspack reports from earlier versions of Oracle, and create an ADDM task that will analyze the snapshot set and produce an analysis report. You can also use the pull-down box to delete preserved snapshot sets, and you can compare two sets of snapshot pairs. Comparing snapshots allows you to determine if differences exist between a baseline snapshot and a recent set of snapshots. Using the report generated from this action, you can determine if the current system performance is diverging from the baseline performance in some way.

What's to Come — Introducing the Oracle Advisors

The AWR is the base structure for host of new advisors introduced in Oracle Database 10g to ease database administration. AWR Along with the Automatic Database Diagnostic Monitor (ADDM), which we will cover in the next article, forms the basic infrastructure for other database advisors. In coming articles we will look at these advisors in more detail.