Oracle-AWR管理包DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
【摘要】
概述MODIFY_SNAPSHOT_SETTINGS Procedures
概述
官方说明：DBMS_WORKLOAD_REPOSITORY
Overview of th...
概述
Overview of the Automatic Workload Repository
AWR机制：通过对系统整体动态采样收集快照信息，存储在SYSAUX表空间，，MMON进程实施，快照分析后写入DBA_HIST_%开头的数据字典。
select table_name from dictionary where table_name like 'DBA_HIST_%';
- 1
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);
--
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
存过定义如下
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
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
如何修改默认的参数值呢？
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);
- 1
- 2
- 3
重新查询 DBA_HIST_WR_CONTROL 可以发现新的规则已经生效。
AWR参数存放的表 DBA_HIST_WR_CONTROL
snapshot的信息存放在DBA_HIST_SNAPSHOT
文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。
原文链接：artisan.blog.csdn.net/article/details/79777340
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）