Managing Baselines with PL/SQL

DBMS_WORKLOAD_REPOSITORY Package

Static , Single Baseline Creation
----------------------------------

BEGIN
  -- Using procedures.
  DBMS_WORKLOAD_REPOSITORY.create_baseline(
    start_snap_id => 56,
    end_snap_id   => 57,
    baseline_name => 'test1_bl',
    expiration    => 60);
END;
/


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);
END;
/


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;


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(7));  < 7 is the ID of the Baseline>

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 => 'test1_bl',
    new_baseline_name => 'sun1_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;
/

or

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'sun1_bl');
 
END;
/

Retention Period Details
---------------------------

SELECT retention FROM dba_hist_wr_control;


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

Current Moving Window
------------------------

SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';

How to change size of moving window
-------------------------------------

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

Baseline Templates
------------------
Baseline templates allow you to define baselines you would like to capture in the future.
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('20-DEC-2013 00:00', 'DD-MON-YYYY HH24:MI'),
    end_time      => TO_DATE('20-DEC-2013 05:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name => '01_dec_013_00_05_bl',
    template_name => '01_dec_013_00_05_tp',
    expiration    => 100);
END;
/

Templates for repeating baselines are a little different as they require some basic scheduling information.
-----------------------------------------------------------------------------------------------------------
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;

Baseline templates are dropped using the DROP_BASELINE_TEMPLATE procedure.
----------------------------------------------------------------------------

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