修改AWR收集时间间隔和手动创建或删除AWR收集

--默认的AWR是每小时执行一次，并且保留8天
SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- ----------
2478546149 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 0


可以通过dbms_workload_repository.modify_snapshot_settings来修改收集的时间间隙，和AWR报告的保留时间。如果收集时间间隔参数设置为0会停止所有统计数据的收集。
例子：修改AWR每15分钟收集一次，快照数据保留时间设置为20160分钟（14天）
exec dbms_workload_repository.modify_snapshot_settings(retention=>20160,interval=>15);


--可以查询出已经修改完毕。AWR每15分钟收集一次，快照数据保留14天。
SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------- ----------
2478546149 +00000 00:15:00.0 +00014 00:00:00.0 DEFAULT 0




可以手动创建AWR快照
exec dbms_workload_repository.create_snapshot;


删除AWR快照
exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>300,high_snap_id=>301);




--查看AWR所有快照
SQL> select SNAP_ID,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_LEVEL from dba_hist_snapshot order by 1;

SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- --------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
302 2 27-OCT-15 04.20.07.131 PM 27-OCT-15 05.00.16.054 PM 1
302 1 27-OCT-15 04.20.06.956 PM 27-OCT-15 05.00.15.862 PM 1
303 2 27-OCT-15 05.00.16.054 PM 27-OCT-15 06.00.29.251 PM 1
303 1 27-OCT-15 05.00.15.862 PM 27-OCT-15 06.00.27.468 PM 1
304 1 27-OCT-15 06.00.27.468 PM 27-OCT-15 07.00.12.771 PM 1
304 2 27-OCT-15 06.00.29.251 PM 27-OCT-15 07.00.12.570 PM 1
305 1 27-OCT-15 07.00.12.771 PM 27-OCT-15 08.00.21.897 PM 1

SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- --------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
305 2 27-OCT-15 07.00.12.570 PM 27-OCT-15 08.00.21.743 PM 1
306 1 27-OCT-15 08.00.21.897 PM 27-OCT-15 09.00.02.813 PM 1
306 2 27-OCT-15 08.00.21.743 PM 27-OCT-15 09.00.02.923 PM 1
307 1 27-OCT-15 09.00.02.813 PM 27-OCT-15 10.00.11.459 PM 1
307 2 27-OCT-15 09.00.02.923 PM 27-OCT-15 10.00.12.006 PM 1
308 1 27-OCT-15 10.00.11.459 PM 27-OCT-15 11.00.50.885 PM 1
308 2 27-OCT-15 10.00.12.006 PM 27-OCT-15 11.00.49.493 PM 1
309 1 27-OCT-15 11.00.50.885 PM 28-OCT-15 12.14.20.256 AM 1
309 2 27-OCT-15 11.00.49.493 PM 28-OCT-15 12.00.57.972 AM 1
310 2 28-OCT-15 12.00.57.972 AM 28-OCT-15 01.00.06.613 AM 1
311 1 28-OCT-15 01.11.44.978 AM 28-OCT-15 02.00.40.285 AM 1

SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- --------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
311 2 28-OCT-15 01.00.06.613 AM 28-OCT-15 02.16.06.729 AM 1
312 2 28-OCT-15 02.16.06.729 AM 28-OCT-15 03.00.06.136 AM 1
312 1 28-OCT-15 02.00.40.285 AM 28-OCT-15 03.00.06.072 AM 1
313 2 28-OCT-15 03.00.06.136 AM 28-OCT-15 04.00.15.598 AM 1
313 1 28-OCT-15 03.00.06.072 AM 28-OCT-15 04.00.15.535 AM 1
314 1 28-OCT-15 04.00.15.535 AM 28-OCT-15 05.00.35.594 AM 1
314 2 28-OCT-15 04.00.15.598 AM 28-OCT-15 05.00.35.487 AM 1
315 2 28-OCT-15 05.00.35.487 AM 28-OCT-15 06.00.07.814 AM 1
315 1 28-OCT-15 05.00.35.594 AM 28-OCT-15 06.00.07.743 AM 1
316 2 28-OCT-15 06.00.07.814 AM 28-OCT-15 07.00.17.015 AM 1
316 1 28-OCT-15 06.00.07.743 AM 28-OCT-15 07.00.16.955 AM 1



--创建基线
SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 309, end_snap_id => 314, baseline_name => 'EMO BASELINE');

PL/SQL procedure successfully completed.


SQL> select BASELINE_NAME,BASELINE_TYPE,BASELINE_ID,START_SNAP_ID,END_SNAP_ID from dba_hist_baseline;

BASELINE_NAME BASELINE_TYPE BASELINE_ID START_SNAP_ID END_SNAP_ID
--------------- ------------- ----------- ------------- -----------
EMO BASELINE STATIC 2 309 314
SYSTEM_MOVING_W MOVING_WINDOW 0 300 343
INDOW

--删除基线
SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'EMO BASELINE',Cascade=>FALSE);
注意：如果参数Cascade=>true,就会删除所有相关的快照。否则，AWR自动进程自动清除这些快照，默认是FALSE。

PL/SQL procedure successfully completed.

SQL> select SNAP_ID ,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot order by 1;

SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
300 2 21-OCT-15 03.00.25.000 PM 21-OCT-15 03.12.07.602 PM
300 1 21-OCT-15 03.03.08.000 PM 21-OCT-15 03.12.07.787 PM
301 1 27-OCT-15 04.08.36.000 PM 27-OCT-15 04.20.06.956 PM
301 2 27-OCT-15 04.10.55.000 PM 27-OCT-15 04.20.07.131 PM
302 2 27-OCT-15 04.20.07.131 PM 27-OCT-15 05.00.16.054 PM
302 1 27-OCT-15 04.20.06.956 PM 27-OCT-15 05.00.15.862 PM
303 2 27-OCT-15 05.00.16.054 PM 27-OCT-15 06.00.29.251 PM
303 1 27-OCT-15 05.00.15.862 PM 27-OCT-15 06.00.27.468 PM
304 1 27-OCT-15 06.00.27.468 PM 27-OCT-15 07.00.12.771 PM
304 2 27-OCT-15 06.00.29.251 PM 27-OCT-15 07.00.12.570 PM
305 1 27-OCT-15 07.00.12.771 PM 27-OCT-15 08.00.21.897 PM

SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
305 2 27-OCT-15 07.00.12.570 PM 27-OCT-15 08.00.21.743 PM
306 1 27-OCT-15 08.00.21.897 PM 27-OCT-15 09.00.02.813 PM
306 2 27-OCT-15 08.00.21.743 PM 27-OCT-15 09.00.02.923 PM
307 1 27-OCT-15 09.00.02.813 PM 27-OCT-15 10.00.11.459 PM
307 2 27-OCT-15 09.00.02.923 PM 27-OCT-15 10.00.12.006 PM
308 1 27-OCT-15 10.00.11.459 PM 27-OCT-15 11.00.50.885 PM
308 2 27-OCT-15 10.00.12.006 PM 27-OCT-15 11.00.49.493 PM
309 1 27-OCT-15 11.00.50.885 PM 28-OCT-15 12.14.20.256 AM
309 2 27-OCT-15 11.00.49.493 PM 28-OCT-15 12.00.57.972 AM
310 2 28-OCT-15 12.00.57.972 AM 28-OCT-15 01.00.06.613 AM
311 1 28-OCT-15 01.11.44.978 AM 28-OCT-15 02.00.40.285 AM

SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
311 2 28-OCT-15 01.00.06.613 AM 28-OCT-15 02.16.06.729 AM
312 2 28-OCT-15 02.16.06.729 AM 28-OCT-15 03.00.06.136 AM
312 1 28-OCT-15 02.00.40.285 AM 28-OCT-15 03.00.06.072 AM
313 2 28-OCT-15 03.00.06.136 AM 28-OCT-15 04.00.15.598 AM
313 1 28-OCT-15 03.00.06.072 AM 28-OCT-15 04.00.15.535 AM
314 1 28-OCT-15 04.00.15.535 AM 28-OCT-15 05.00.35.594 AM
314 2 28-OCT-15 04.00.15.598 AM 28-OCT-15 05.00.35.487 AM
315 2 28-OCT-15 05.00.35.487 AM 28-OCT-15 06.00.07.814 AM
315 1 28-OCT-15 05.00.35.594 AM 28-OCT-15 06.00.07.743 AM
316 2 28-OCT-15 06.00.07.814 AM 28-OCT-15 07.00.17.015 AM
316 1 28-OCT-15 06.00.07.743 AM 28-OCT-15 07.00.16.955 AM

SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
317 2 28-OCT-15 07.00.17.015 AM 28-OCT-15 08.00.03.044 AM
317 1 28-OCT-15 07.00.16.955 AM 28-OCT-15 08.00.03.110 AM
318 2 28-OCT-15 08.00.03.044 AM 28-OCT-15 09.00.12.153 AM
318 1 28-OCT-15 08.00.03.110 AM 28-OCT-15 09.00.12.216 AM
319 2 28-OCT-15 09.00.12.153 AM 28-OCT-15 10.00.31.063 AM
319 1 28-OCT-15 09.00.12.216 AM 28-OCT-15 10.00.31.153 AM
320 2 28-OCT-15 10.37.05.000 AM 28-OCT-15 11.00.52.462 AM
320 1 28-OCT-15 10.34.51.000 AM 28-OCT-15 11.00.52.640 AM
321 2 28-OCT-15 11.00.52.462 AM 28-OCT-15 12.00.01.420 PM
321 1 28-OCT-15 11.00.52.640 AM 28-OCT-15 12.00.01.555 PM
322 1 28-OCT-15 02.25.56.000 PM 28-OCT-15 02.35.37.639 PM