Enhancements in AWR Baselines
A baseline is any set of snapshots taken over a period of time. The snapshots are selected such that they yield a set of baselines that change during the period of time that you’re collecting data. The baseline captures the time-based variations for a set of baseline statistics and alerts you when the current values differ significantly from the baseline values. An AWR baseline contains a set of AWR snapshots collected over a period of time that provides a frame of reference for a known “good period,” which you can then use as a reference period to compare performance during another time period of interest. The snapshots in an AWR baseline are grouped to provide a set of baseline values that change over time. For example, the I/O rate is highest during the peak usage times in the database. One of the most difficult problems you have in setting alert thresholds is setting those thresholds to the correct levels for appropriate alerts. Arbitrary alerts that remain identical throughout are not optimal because they will likely miss the natural peaks and valleys in the workload of a real production database. Baselines, on the other hand, are ideal for setting time- dependent alert thresholds because the baselines let the database compare apples with apples and oranges with oranges, by enabling the comparison of present performance with baseline data from a similar time period.
Oracle Database 11g enables you to collect two types of baselines: static baselines and moving window baselines. A static baseline can be a single baseline collected over a single fixed time period (for example, from Jan 1, 2008 at 10:00 A.M. to Jan 1, 2008 at 12:00 P.M.) or a repeating baseline collected over a repeating time period (for example, every first Monday in a month from 10:00 A.M. to 12:00 P.M. for the year 2008). The moving window baseline captures data over a window that keeps moving over time. Oracle Database 11g creates a system-defined moving window baseline by default. This default moving window corresponds to the AWR data captured during the AWR retention period, which is now eight days, rather than seven days, as it was in the Oracle Database 10g release.
Baselines help you set alert thresholds, monitor performance, and compare advisor reports. This is a definite improvement over the Oracle Database 10g release, where all you can really do with an AWR baseline is to just create and drop static single baselines. Oracle Database 11g provides several enhancements to the AWR baselines feature, including the following:
Baseline templates to schedule the creation of a baselineMoving window baselines from which you can specify adaptive thresholdsAWR Baseline Metric ThresholdsSingle AWR Baseline
The fixed, or static, baseline functionality is a little more flexible in Oracle 11g compared to that of Oracle 10g. Originally, the DBMS_WORKLOAD_REPOSITORY package included a single CREATE_BASELINE procedure allowing you to define baselines using specific snapshot IDs. It now includes overloaded procedures and functions allowing baselines to be created using start and end times, which are used to estimate the relevant snapshot IDs. The functions have the same parameter lists as the procedures, but return the baseline ID. By default baselines are kept forever, but the new expiration parameter allows them to be automatically expired after a specified number of days.
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
-- Using procedures.
DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_snap_id