A moving window snapshot baseline represents a continuously updating set of AWR snapshots over a specified period. Oracle database automatically maintains a system-defined moving window baseline.
The DBA_HIST_BASELINE view contains baseline information:
SELECT baseline_name, start_snap_id, end_snap_id
FROM dba_hist_baseline;
-- Sample output:
BASELINE_NAME START_SNAP_ID END_SNAP_ID
Normal Workload Baseline 1630 1632
SYSTEM_MOVING_WINDOW 1435 1645
You can drop baseline at a later date using:
EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE ( -
baseline_name => 'Normal Workload Baseline');
You can also create baseline for a future contiguous time period using baseline templates. Baseline templates are very useful for ensuring that you create baselines for important future moments. The following PL/SQL creates a baseline template for Black Friday in 2025:
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => TO_DATE('28-NOV-2025 00:00:00', 'DD-MON-YYYY HH24:MI:SS'),
end_time => TO_DATE('28-NOV-2025 23:59:59', 'DD-MON-YYYY HH24:MI:SS'),
baseline_name => 'Black_Friday_2025',
template_name => 'Black_Friday_2025',
expiration => NULL );
END;
/
The expiration => NULL parameter means that this baseline will be kept forever.
If your application experiences a weekly peak load at every Sunday night, you can specify a template to create baselines based on the repeating time periods:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
day_of_week => 'SUNDAY',
hour_in_day => 23,
duration => 2,
start_time => SYSDATE,
end_time => ADD_MONTHS(SYSDATE, 12),
baseline_name_prefix=> 'Sunday_night_bl',
template_name => 'Sunday_night_bl',
expiration => 365
);
END;
/
You can see baseline templates in the DBA_HIST_BASELINE_TEMPLATE view:
SELECT template_name,
template_type,
TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
duration,
expiration,
repeat_interval
FROM dba_hist_baseline_template;
A sample output:
TEMPLATE_NAME TEMPLATE_TYPE START_TIME END_TIME DURATION EXPIRATION REPEAT_INTERVAL
Black_Friday_2025 SINGLE 2025-11-28 00:00:00 2025-11-28 23:59:59
Sunday_night_bl REPEATING 2024-05-30 11:15:13 2025-05-30 11:15:13 2 365 FREQ=WEEKLY;INTERVAL=1;BYDAY=SUN;BYHOUR=23;BYMINUTE=0;BYSECOND=0
You can drop the base at a later date using:
EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE ( -
TEMPLATE_name => 'Black_Friday_2025');
Yuan Yao, An Oracle ACE's Blog 
Leave a comment