概述
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
