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