Monday, March 4, 2013

0 AWR Baseline

  • Moving window baseline: The baseline base on the entire set of the snapshot. The set is changing base on the “MOVING WINDOW”. MOVING WINDOW must smaller than retention period.
  • Single baseline: Baseline schedule base on the future date.
  • Repeating baseline : Define a baseline to be captured for a repeating period of time in the future

Relate tables

  • wrm$_baseline
  • wrm$_baseline_template
  • wrm$_baseline_details

 

  • DBA_HIST_BASELINE
  • DBA_HIST_BASELINE_DETAILS
  • DBA_HIST_BASELINE_TEMPLATE

 

MOVING WINDOW:wrm$_baseline

select baseline_name,baseline_type,moving_window_size from wrm$_baseline;

image

DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE can be used to modify it. See here .

Single baseline: dbms_workload_repository.create_baseline_template

Single baseline is used to define the baseline template so we can use it to capture the future workload. Fore example, if you know there is certain type of the work will be execute at Saturday afternoon.you can set it to capture the workload .

exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( start_time => to_date('20130305 19:30', 'yyyymmdd HH24:MI'), end_time =>  to_date('20130305 22:30', 'yyyymmdd HH24:MI'), baseline_name => 'singleBaseline_20130305', template_name => 'template_20130305', expiration => 30);
select template_name,baseline_name_prefix,  template_type,start_time,end_time,expiration from wrm$_baseline_template;

image

image

Repeating Baselines

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
day_of_week => 'Saturday', hour_in_day => 12,
duration => 3, expiration => 30,
start_time => to_date('20130309 19:30', 'yyyymmdd HH24:MI'), 
end_time => to_date('20131231 19:30', 'yyyymmdd HH24:MI'), 
baseline_name_prefix => 'baseline_sat_reports_', 
template_name => 'template_Sat_reports');
END;
/

image

List all the template

wrm$_baseline_template  -> DBA_HIST_BASELINE_TEMPLATE

SELECT TEMPLATE_NAME,BASELINE_NAME_PREFIX,  TEMPLATE_TYPE,START_TIME,END_TIME,EXPIRATION FROM WRM$_BASELINE_TEMPLATE;
select * from DBA_HIST_BASELINE_TEMPLATE;

image

image

Drop baseline template

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (template_name => 'template_Sat_reports');

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates