概述
官方说明：DBMS_WORKLOAD_REPOSITORY
Overview of the Automatic Workload Repository
AWR机制：通过对系统整体动态采样收集快照信息，存储在SYSAUX表空间，，MMON进程实施，快照分析后写入DBA_HIST_%开头的数据字典。
select table_name from dictionary where table_name like 'DBA_HIST_%';
DBMS_WORKLOAD_REPOSITORY包管理AWR(Automatic Workload Repository)，执行诸如管理快照和基线等操作。
因包中功能众多，这里我们仅关注MODIFY_SNAPSHOT_SETTINGS
MODIFY_SNAPSHOT_SETTINGS Procedures
默认快照间隔1小时，10g保存7天，11g保存8天
可以通过dbms_workload_repository.MODIFY_SNAPSHOT_SETTINGS存过来调整AWR快照的相关参数
我们来看下Oralce对这段存过标注的注释 ，说明均在注释里，请仔细阅读
--
-- modify_snapshot_settings()
-- Procedure to adjust the settings of the snapshot collection.
--
-- Input arguments:
-- retention - new retention time (in minutes). The
-- specified value must be in the range:
-- MIN_RETENTION (1 day) to
-- MAX_RETENTION (100 years)
--
-- If ZERO is specified, snapshots will be
-- retained forever. A large system-defined
-- value will be used as the retention setting.
--
-- If NULL is specified, the old value for
-- retention is preserved.
--
-- ***************
-- NOTE: The retention setting must be
-- greater than or equal to the window
-- size of the 'SYSTEM_MOVING_WINDOW'
-- baseline. If the retention needs
-- to be less than the window size,
-- the 'modify_baseline_window_size'
-- routine can be used to adjust the
-- window size.
-- ***************
--
-- interval - the interval between each snapshot, in
-- units of minutes. The specified value
-- must be in the range:
-- MIN_INTERVAL (10 minutes) to
-- MAX_INTERVAL (100 years)
--
-- If ZERO is specified, automatic and manual
-- snapshots will be disabled. A large
-- system-defined value will be used as the
-- interval setting.
--
-- If NULL is specified, the
-- current value is preserved.
--
-- topnsql (NUMBER) - Top N SQL size. The number of Top SQL
-- to flush for each SQL criteria
-- (Elapsed Time, CPU Time, Parse Calls,
-- Shareable Memory, Version Count).
--
-- The value for this setting will be not
-- be affected by the statistics/flush level
-- and will override the system default
-- behavior for the AWR SQL collection. The
-- setting will have a minimum value of 30
-- and a maximum value of 50000.
--
-- IF NULL is specified, the
-- current value is preserved.
--
-- topnsql (VARCHAR2) - Users are allowed to specify the following
-- values: ('DEFAULT', 'MAXIMUM', 'N')
--
-- Specifying 'DEFAULT' will revert the system
-- back to the default behavior of Top 30 for
-- level TYPICAL and Top 100 for level ALL.
--
-- Specifying 'MAXIMUM' will cause the system
-- to capture the complete set of SQL in the
-- cursor cache. Specifying the number 'N' is
-- equivalent to setting the Top N SQL with
-- the NUMBER type.
--
-- Specifying 'N' will cause the system
-- to flush the Top N SQL for each criteria.
-- The 'N' string is converted into the number
-- for Top N SQL.
--
-- dbid - database identifier for the database to
-- adjust setting. If NULL is specified, the
-- local dbid will be used.
--
-- For example, the following statement can be used to set the
-- Retention and Interval to their minimum settings:
--
-- dbms_workload_repository.modify_snapshot_settings
-- (retention => DBMS_WORKLOAD_REPOSITORY.MIN_RETENTION
-- interval => DBMS_WORKLOAD_REPOSITORY.MIN_INTERVAL)
--
-- The following statement can be used to set the Retention to
-- 8 days and the Interval to 60 minutes and the Top N SQL to
-- the default setting:
--
-- dbms_workload_repository.modify_snapshot_settings
-- (retention => 11520, interval => 60, topnsql => 'DEFAULT');
--
-- The following statement can be used to set the Top N SQL
-- setting to 200:
-- dbms_workload_repository.modify_snapshot_settings
-- (topnsql => 200);
--
存过定义如下
PROCEDURE modify_snapshot_settings(retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL
);
PROCEDURE modify_snapshot_settings(retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql IN VARCHAR2,
dbid IN NUMBER DEFAULT NULL
);
如何修改默认的参数值呢？
This example changes the interval setting to one hour and the retention setting to two weeks for the local database:
EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 60,
retention => 20160);
重新查询 DBA_HIST_WR_CONTROL 可以发现新的规则已经生效。
AWR参数存放的表 DBA_HIST_WR_CONTROL
snapshot的信息存放在DBA_HIST_SNAPSHOT
