修改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

蓬江网站制作公司哪家好，找创新互联！从网页设计、网站建设、微信开发、APP开发、响应式网站建设等网站项目制作，到程序开发，运营维护。创新互联2013年开创至今到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联。