美创科技技术社区

注册

 

发新话题 回复该主题

修改AWRsnapshot运行间隔 [复制链接]

1#

awr snapshot运行间隔缺省以1小时为单位运行,在很多时候1小时的运行时间间隔太长,我们为了定位问题,需要缩减到15分钟左右的范畴,我们可以利用dbms_workload_repository包完成间隔时间的设置:

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(   retention   IN  NUMBER    DEFAULT NULL,   interval    IN  NUMBER    DEFAULT NULL,   topnsql     IN  NUMBER    DEFAULT NULL,   dbid        IN  NUMBER    DEFAULT NULL);DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(   retention   IN  NUMBER    DEFAULT NULL,   interval    IN  NUMBER    DEFAULT NULL,   topnsql     IN  VARCHAR2,   dbid        IN  NUMBER    DEFAULT NULL);

Parameters


Table 119-16 MODIFY_SNAPSHOT_SETTINGS Procedure Parameters

Parameter

Description

retention

The new retention time (in minutes). The specified value must be in the range of 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.

interval

The new interval setting between each snapshot, in units of minutes. The specified value must be in the range MIN_RETENTION (10 minutes) to MAX_RETENTION (1 year).

If ZERO is specified, automatic and manual snapshots will be disabled. A large system-defined value will be used as the retention setting.

If NULL is specified, the current value is preserved.

topnsql


  • If 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 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 100000000. Specifying NULL will keep the current setting.
  • If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics 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 NULL for this argument will keep the current setting.

dbid

The database identifier in AWR for which to modify the snapshot settings. If NULL is specified, the local dbid will be used. Defaults to NULL

分享 转发
TOP
发新话题 回复该主题