Creating AWR Baselines Automatically

Problem
You want to periodically create baselines in your database automatically.

Solution
You can create an AWR repeating template, which gives you the ability to have baselines created automatically based on a predefined interval and time frame. By using the CREATE_BASELINE_TEMPLATE procedure within the DBMS_WORKLOAD_REPOSITORY package, you can have a fixed baseline automatically created for this repeating interval and time frame. See the following example to set up an AWR template:

SQL> alter session set nls_date_format = ‘yyyy-mm-dd:hh24:mi:ss’;
SQL> exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template( -

day_of_week => ‘WEDNESDAY’, -
hour_in_day => 0, -
duration => 6, -
start_time => ‘2011-06-14:00:00:00’, -
end_time => ‘2011-06-14:06:00:00’, -
baseline_name_prefix => ‘Batch Baseline ‘, -
template_name => ‘Batch Template’, -
expiration => 365);
PL/SQL procedure successfully completed.

For the foregoing template, a fixed baseline will be created based on the midnight to 6 a.m. window every Wednesday. In this case, this template creates baselines for a normal batch window time frame.

How It Works
If you need to drop your template, you simply use the DROP_BASELINE_TEMPLATE procedure from the DBMS_WORKLOAD_REPOSITORY package. See the following example:

SQL> exec dbms_workload_repository.drop_baseline_template(‘Batch Template’);
PL/SQL procedure successfully completed.

If you wish to view information on any templates you have created, you can query the DBA_HIST_BASELINE_TEMPLATE view. See the following sample query:

column template_name format a14
column prefix format a14
column hr format 99
column dur format 999
column exp format 999
SELECT template_name, baseline_name_prefix prefix,
to_char(start_time,’mm/dd/yy:hh24’) start_time,
to_char(end_time,’mm/dd/yy:hh24’) end_time,
substr(day_of_week,1,3) day, hour_in_day hr, duration dur, expiration exp,
to_char(last_generated,’mm/dd/yy:hh24’) last
FROM dba_hist_baseline_template;

TEMPLATE_NAME PREFIX START_TIME END_TIME DAY HR DUR EXP LAST

---

Batch Template Batch Baseline 06/14/11:00 06/14/11:06 WED 0 6 365 06/14/11:00