Oracle Database 10g New Advisor Features: The Oracle Database Advisors

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 10g's 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

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

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.

Read More ==>