AWR Baseline Enhancements in 11g Release 1

The Automatic Workload Repository (AWR) was introduced in Oracle 10g and included some simple baseline functionality. Creating a baseline allowed a specified range of snapshots to be retained, regardless of the AWR retention policy, and used for performance comparisons. This functionality, and the DBMS_WORKLOAD_REPOSITORY package that manages it, has been extended in Oracle 11g.

Note. Most of the procedures and functions in the DBMS_WORKLOAD_REPOSITORY package accept a DBID parameter, which defaults to the local database identifier. For that reason the following examples will omit this parameter.

Fixed Baselines
The Moving Window Baseline
Baseline Templates
Baseline Metric Thresholds

Fixed Baselines

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 => 2490,
    end_snap_id   => 2491,
    baseline_name => 'test1_bl',
    expiration    => 60);

  DBMS_WORKLOAD_REPOSITORY.create_baseline(
    start_time    => TO_DATE('09-JUL-2008 17:00', 'DD-MON-YYYY HH24:MI'),
    end_time      => TO_DATE('09-JUL-2008 18:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name => 'test2_bl',
    expiration    => NULL);

  -- Using functions.
  l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
                start_snap_id => 2492,
                end_snap_id   => 2493,
                baseline_name => 'test3_bl',
                expiration    => 30); 
  DBMS_OUTPUT.put_line('Return: ' || l_return);

  l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
                start_time    => TO_DATE('09-JUL-2008 19:00', 'DD-MON-YYYY HH24:MI'),
                end_time      => TO_DATE('09-JUL-2008 20:00', 'DD-MON-YYYY HH24:MI'),
                baseline_name => 'test4_bl',
                expiration    => NULL);

  DBMS_OUTPUT.put_line('Return: ' || l_return);
END;
/

Return: 8
Return: 9

PL/SQL procedure successfully completed.

SQL>

The new baselines are visible in DBA_HIST_BASELINE view.

COLUMN baseline_name FORMAT A15

SELECT baseline_id, baseline_name, START_SNAP_ID, 
       TO_CHAR(start_snap_time, 'DD-MON-YYYY HH24:MI') AS start_snap_time,       
       END_SNAP_ID,            
       TO_CHAR(end_snap_time, 'DD-MON-YYYY HH24:MI') AS end_snap_time
FROM   dba_hist_baseline
WHERE  baseline_type = 'STATIC'
ORDER BY baseline_id;

BASELINE_ID BASELINE_NAME   START_SNAP_ID START_SNAP_TIME   END_SNAP_ID END_SNAP_TIME
----------- --------------- ------------- ----------------- ----------- -----------------
          6 test1_bl                 2490 09-JUL-2008 17:00        2491 09-JUL-2008 18:00
          7 test2_bl                 2490 09-JUL-2008 17:00        2491 09-JUL-2008 18:00
          8 test3_bl                 2492 09-JUL-2008 19:00        2493 09-JUL-2008 20:00
          9 test4_bl                 2492 09-JUL-2008 19:00        2493 09-JUL-2008 20:00

4 rows selected.

SQL>

Information about a specific baseline can be displayed by using the BASELINE_ID with the SELECT_BASELINE_DETAILS pipelined table function, or the BASELINE_NAME with the SELECT_BASELINE_METRIC pipelined table function.

SELECT *
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_details(6));

SELECT *
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_metric('SYSTEM_MOVING_WINDOW'));

Baselines are renamed using the RENAME_BASELINE procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.rename_baseline(
    old_baseline_name => 'test4_bl',
    new_baseline_name => 'test5_bl');
END;
/

Baselines are dropped using the DROP_BASELINE procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test1_bl');
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test2_bl');
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test3_bl');
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test5_bl');
END;
/

Enterprise Manager is probably the most convenient way to manage AWR baselines. From the "AWR Baselines" screen (Server > AWR Baselines), click the "Create" button.



Select the interval type of "Single" and click the "Continue" button.



Enter a name for the baseline and mark the start and end of the baseline by either clicking on the snapshot icons or entering the time range manually, then click the "Finish" button.



The newly created baseline is now displayed in the "AWR Baselines" screen.



The baseline is renamed or dropped by checking its "Select" box and clicking the "Edit" or "Delete" button respectively. Once the baseline is created you can schedule statistics computation by checking its "Select" box, selecting "Schedule Statistics Computation" in the drop down list, then clicking "Go" button.

The Moving Window Baseline

Oracle 11g introduces the concept of a moving window baseline, which is used to calculate metrics for the adaptive thresholds. The window is a view of the AWR data within the retention period. The default size of the window matches the default AWR retention period of 8 days, but it can be set as a subset of this value. Before you can increase the size of the window you must first increase the size of the AWR retention period.

The current AWR retention period can be displayed by querying the RETENTION column of the DBA_HIST_WR_CONTROL view.

SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

1 row selected.

SQL>

The retention period is altered using the MODIFY_SNAPSHOT_SETTINGS procedure, which accepts a RETENTION parameter in minutes.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200);  -- Minutes (= 30 Days).
END;
/

SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0

1 row selected.

SQL>

The current moving window size is displayed by querying the DBA_HIST_BASELINE view.

SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                 8

1 row selected.

SQL>

The size of the moving window baseline is altered using the MODIFY_BASELINE_WINDOW_SIZE procedure, which accepts a WINDOW_SIZE parameter in days.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(
    window_size => 20);
END;
/

SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                20

1 row selected.

SQL>

Oracle recommend of window size greater than or equal to 30 days when using adaptive thresholds.

To adjust the retention periods in Enterprise Manager, click on the "Edit" button in the "Automatic Workload Repository" screen (Server > Automatic Workload Repository).



Edit the "Use Time-Based Retention" to the appropriate number of days and click the "OK" button.



Next, navigate to the "AWR baselines" screen (Server > AWR Baselines), select the "SYSTEM_MOVING_WINDOW" baseline and click the "Edit" button.



Set the appropriate "Window Size" and click the "Apply" button.

Baseline Templates

Baseline templates allow you to define baselines you would like to capture in the future. Overloads of the CREATE_BASELINE_TEMPLATE procedure define the capture of individual baselines, or repeating baselines. Creating a single baseline template is similar to creating a time-based baseline, except the time is in the future.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
    start_time    => TO_DATE('01-DEC-2008 00:00', 'DD-MON-YYYY HH24:MI'),
    end_time      => TO_DATE('01-DEC-2008 05:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name => '01_dec_008_00_05_bl',
    template_name => '01_dec_008_00_05_tp',
    expiration    => 100);
END;
/

Templates for repeating baselines are a little different as they require some basic scheduling information. The START_TIME and END_TIME parameters define when the template is activated and deactivated. The DAY_OF_WEEK, HOUR_IN_DAY and DURATION parameters define the day (MONDAY - SUNDAY or ALL) the baselines are generated on and the start and end point of the baseline. Since the template will generate multiple baselines, the baseline name is derived from the BASELINE_NAME_PREFIX concatenated to the date. The following example creates a template that will run for the next six months, gathering a baseline every Monday between 00:00 and 05:00.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
   day_of_week          => 'MONDAY',
   hour_in_day          => 0, 
   duration             => 5,
   start_time           => SYSDATE,
   end_time             => ADD_MONTHS(SYSDATE, 6),
   baseline_name_prefix => 'monday_morning_bl_',
   template_name        => 'monday_morning_tp',
   expiration           => NULL);
END;
/

Information about baseline templates is displayed using the DBA_HIST_BASELINE_TEMPLATE view.

SELECT template_name,
       template_type,
       baseline_name_prefix,
       start_time,
       end_time,
       day_of_week,
       hour_in_day,
       duration,
       expiration
FROM   dba_hist_baseline_template;

TEMPLATE_NAME                  TEMPLATE_ BASELINE_NAME_PREFIX           START_TIME
------------------------------ --------- ------------------------------ --------------------
END_TIME             DAY_OF_WE HOUR_IN_DAY   DURATION EXPIRATION
-------------------- --------- ----------- ---------- ----------
01_dec_008_00_05_tp            SINGLE    01_dec_008_00_05_bl            01-DEC-2008 00:00:00
01-DEC-2008 05:00:00                                         100

monday_morning_tp              REPEATING monday_morning_bl_             11-JUL-2008 14:43:36
11-JAN-2009 14:43:36 MONDAY              0          5


2 rows selected.

SQL>

Notice the BASELINE_NAME_PREFIX column holds either the prefix or full baseline name depending on the type of baseline being captured.

Baseline templates are dropped using the DROP_BASELINE_TEMPLATE procedure.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => '01_dec_008_00_05_tp');
  DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => 'monday_morning_tp');
END;
/

Enterprise Manager uses the same screens for creating single baseline templates as it does for creating baselines. Navigate to the "AWR Baselines" screen (Server > AWR Baselines) and click the "Create" button.


Select the interval type of "Single" and click the "Continue" button.



Enter a name for the baseline template and mark the start and end of the baseline by entering the time range manually, then click the "Finish" button. If the time range is in the future, a baseline template is automatically created, rather than a regular baseline.



To view the baseline template, click the "AWR Baseline Templates" link at the bottom of the "AWR baselines" screen.



To creating the repeating baseline templates, start the baseline creation as before, but select the interval type of "Repeating" and click the "Continue" button.



Enter the baseline name prefix, which is also used as the template name, along with the interval and scheduling details, then click the "Finish" button.



Once again, the baseline template is visible on the "AWR Baseline Templates" screen.

Baseline Metric Thresholds

Oracle 11g simplifies the definition of metric thresholds by allowing them to be applied to baselines, including the moving window baseline. The easiest way to start using baseline metric thresholds is to click the "Quick Configuration" button on the "Baseline Metric Thresholds" screen (Home > Baseline Metric Thresholds).



Select the relevant "Workload Profile" for your system, then click the "Continue" button.



Click the "Finish" button on the review screen.



The adaptive thresholds are then visible on the "Baseline Metric Thresholds" screen. Click the category name or "Edit Threshold" icon to edit the threshold settings.



If you need to amend the threshold settings, remember to click the "Apply Thresholds" button once you have finished.



For more information see: