dbms_workload_repository手工管理AWR

1、修改快照设置

sys@ORCL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL             RETENTION                      TOPNSQL
---------- ------------------------- ------------------------------ ----------
1301772781 +00000 01:00:00.0         +00008 00:00:00.0              DEFAULT

sys@ORCL> exec dbms_workload_repository.modify_snapshot_settings(retention => 15*1440,interval => 30);

PL/SQL 过程已成功完成。

sys@ORCL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL             RETENTION                      TOPNSQL
---------- ------------------------- ------------------------------ ----------
1301772781 +00000 00:30:00.0         +00015 00:00:00.0              DEFAULT

interval：将间隔设置为0时，oracle将禁止采用自动和手工快照的机制。
retention：当保存时间设置为0时，oracle将永久地保存这个快照。

2、创建和删除快照

sys@ORCL> exec dbms_workload_repository.create_snapshot();

PL/SQL 过程已成功完成。

sys@ORCL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 10,high_snap_id => 12);

PL/SQL 过程已成功完成。

sys@ORCL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 628,high_snap_id => 629);

PL/SQL 过程已成功完成。

dba_hist_snapshot视图将列出有关可用快照的信息。

3、创建和删除基线

sys@ORCL> exec dbms_workload_repository.create_baseline(start_snap_id => 630,end_snap_id => 635,baseline_name => 'base line');

PL/SQL 过程已成功完成。

sys@ORCL> select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;

BASELINE_NAME                                                    START_SNAP_ID END_SNAP_ID
---------------------------------------------------------------- ------------- -----------
base line                                                                  630         635
SYSTEM_MOVING_WINDOW                                                       636         637

sys@ORCL> exec dbms_workload_repository.drop_baseline(baseline_name => 'base line',cascade => true);

PL/SQL 过程已成功完成。

sys@ORCL> select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;

BASELINE_NAME                                                    START_SNAP_ID END_SNAP_ID
---------------------------------------------------------------- ------------- -----------
SYSTEM_MOVING_WINDOW                                                       636         637

sys@ORCL> exec dbms_workload_repository.create_baseline(to_date('2013-11-03 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2013-11-03 06:00:00','yyyy-mm-dd hh24:mi:ss'),'base line2');

PL/SQL 过程已成功完成。

sys@ORCL> select baseline_name,start_snap_id,end_snap_id from dba_hist_baseline;

BASELINE_NAME                                                    START_SNAP_ID END_SNAP_ID
---------------------------------------------------------------- ------------- -----------
base line                                                                  685         686
base line2                                                                 685         686
SYSTEM_MOVING_WINDOW                                                       640         696

4、自动创建AWR基线

sys@ORCL> alter session set NLS_DATE_FORMAT= 'yyyy-mm-dd hh24:mi:ss';

会话已更改。

sys@ORCL> exec DBMS_WORKLOAD_REPOSITORY.create_baseline_template( -
>     day_of_week          => 'WEDNESDAY', -
>     hour_in_day          => 0, -
>     duration             => 6, -
>     start_time           => '2013-11-01:00:00:00', -
>     end_time             => '2013-12-31:06:00:00', -
>     baseline_name_prefix => 'Batch Baseline ', -
>     template_name        => 'Batch Template', -
>     expiration           => 365);

PL/SQL 过程已成功完成。

sys@ORCL> select t.template_name,
  2         t.template_type,
  3         t.start_time,
  4         t.end_time,
  5         t.day_of_week,
  6         t.hour_in_day,
  7         t.duration
  8    from dba_hist_baseline_template t;

TEMPLATE_NAME                  TEMPLATE_ START_TIME          END_TIME            DAY_OF_WE HOUR_IN_DAY   DURATION
------------------------------ --------- ------------------- ------------------- --------- ----------- ----------
Batch Template                 REPEATING 2013-11-01 00:00:00 2013-12-31 06:00:00 WEDNESDAY           0          6

sys@ORCL> exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template('Batch Template');

PL/SQL 过程已成功完成。

day_of_week：天或者星期，基线在这个时间上重复。Specify one of the following values:('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY','THURSDAY', 'FRIDAY', 'SATURDAY', 'ALL')
hour_in_day：0-23，执行基线在这个小时开始。
duration：持续时间(小时数)。
start_time：创建基线的开始时间。
end_time：创建基线的结束时间。
expiration :基线过期的天数。

对于上面这个模板，将会在每周三根据0:00到上午6：00的时间窗口创建固定基线。