AWR Baselines

=> AWR Baselines serve as preserved collections of AWR snapshots, capturing a snapshot of specific time periods and safeguarded from automatic purging within the AWR framework. 

=> These baselines can be retained indefinitely or for a designated period and are particularly beneficial for capturing diverse system load patterns, including normal load periods, peak hours, batch processing, and backup times. 

=> When encountering performance problems, AWR Baselines come into play by allowing comparisons with other snapshots, aiding in the identification of anomalies or trends.

=> Create an AWR base line by using either begin and end snap id's or using begin and end time as below

SQL> 

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE
( START_SNAP_ID => 586,
END_SNAP_ID  => 594,
BASELINE_NAME => 'NORMAL_LOAD',
EXPIRATION  => 3650);
END;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

Where, 

START_SNAP_ID is the begin snap id 

END_SNAP_ID is the end snap id 

BASELINE_NAME is any meaningful name to the baseline 

Expiration is the expiration period for baseline in days, for the above example it is 10 years 

=> Create baseline by providing time frame instead of snapshots, snap id closest to the time frame will be automatically picked up 

SQL> 

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
START_TIME => TO_DATE('15-JAN-2024 08:00','DD-MON-YYYY HH24:MI'),
END_TIME    => TO_DATE('15-JAN-2024 16:00','DD-MON-YYYY HH24:MI'),
BASELINE_NAME   => 'NORMAL_LOAD_MONDAY');
END;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

If no expiration is provided then the baseline will reside in database indefinitely. 

=> dba_hist_baseline view can be queried to check the existing baselines 

SQL>

col BASELINE_NAME for a20 
col START_SNAP_TIME for a25 
col END_SNAP_TIME for a25 
set lines 1000

select 
    baseline_id
    , baseline_name
    , baseline_type
    , start_snap_id
    , start_snap_time
    , end_snap_id
    , end_snap_time
    , creation_time
    , expiration 
from 
    dba_hist_baseline
order by
    baseline_id;

BASELINE_ID BASELINE_NAME        BASELINE_TYPE START_SNAP_ID START_SNAP_TIME           END_SNAP_ID END_SNAP_TIME             CREATION_ EXPIRATION
----------- -------------------- ------------- ------------- ------------------------- ----------- ------------------------- --------- ----------
          0 SYSTEM_MOVING_WINDOW MOVING_WINDOW           448 08-JAN-24 03.00.28.225 PM         595 16-JAN-24 01.33.10.840 PM 15-OCT-20
          1 NORMAL_LOAD          STATIC                  586 14-JAN-24 09.00.17.020 AM         594 14-JAN-24 05.00.44.483 PM 16-JAN-24       3650
          2 NORMAL_LOAD_MONDAY   STATIC                  594 14-JAN-24 05.00.44.483 PM         595 16-JAN-24 01.33.10.840 PM 16-JAN-24

SQL>