阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

AWR生成快照间隔调整

219次阅读
没有评论

共计 4954 个字符,预计需要花费 13 分钟才能阅读完成。

一、描述

       测试环境经常会对数据库进行压力测试,在这个测试过程中,有稳定性,也有突发性,因为我对测试方面不是很专业,所以自己理解成这个样子,要在短时间内高并发的测试时,需要调整 Oracle 数据库 AWR 生成快照的间隔才可以生成短时间内的 AWR 报告,使用 DBMS_WORKLOAD_REPOSITORY 包的 MODIFY_SNAPSHOT_SETTINGS 存储过程来修改。当然也可以采取手工生成快照方式(dbms_workload_repository.create_snapshot)。
 
二、官方文档
The DBMS_WORKLOAD_REPOSITORY package lets you manage the Workload Repository, performing operations such as managing snapshots and baselines.  
 
MODIFY_SNAPSHOT_SETTINGS Procedures
This procedure controls three aspects of snapshot generation.
The INTERVAL setting affects how often snapshots are automatically captured.
The RETENTION setting affects how long snapshots are retained in the Workload Repository.
The number of SQL captured for each Top criteria. If the user manually specifies a value for Top N SQL, the AWR SQL collection will use the user-specified number for both automatic and manual snapshots.
There are two overloads. The first takes a NUMBER and the second takes a VARCHAR2 for the topnsql argument. The differences are described under the Parameters description.
 
Syntax
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);
 
 
If you query the DBA_HIST_WR_CONTROL table after this procedure is executed, you will see the changes to these settings.
 
ParameterDescription

retention

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.

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 Procedure can be used to adjust the window size.

interval

New interval setting between each snapshot, in units of minutes. The specified value must be in the range MIN_INTERVAL (10 minutes) to MAX_INTERVAL (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 50,000. Specifying NULL will keep the current setting.

  • If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where Nis 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

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

 
三、实验
1. 检查 AWR 报告生成时间间隔
SYS@OCM11G >col SNAP_INTERVAL for a30
SYS@OCM11G >col RETENTION for a30
SYS@OCM11G >set lines 120
SYS@OCM11G >select * from dba_hist_wr_control;
 
      DBID SNAP_INTERVAL                  RETENTION                      TOPNSQL
———- —————————— —————————— ———-
3465366841 +00000 01:00:00.0              +00008 00:00:00.0              DEFAULT
 
2. 修改 AWR 报告生成时间间隔为 10 分钟(最小 10 分钟,最大 1 年)
SYS@OCM11G >exec dbms_workload_repository.modify_snapshot_settings(interval  =>  10);
 
PL/SQL procedure successfully completed.
 
3. 检查修改后的配置参数
SYS@OCM11G >select * from dba_hist_wr_control;
 
      DBID SNAP_INTERVAL                  RETENTION                      TOPNSQL
———- —————————— —————————— ———-
3465366841 +00000 00:10:00.0              +00008 00:00:00.0              DEFAULT
 
4. 等待 10 分钟后,验证生成新的快照文件
SYS@OCM11G >select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from DBA_HIST_SNAPSHOT order by BEGIN_INTERVAL_TIME;
 
   SNAP_ID BEGIN_INTERVAL_TIME                      END_INTERVAL_TIME
———- —————————————- ——————————
       152 23-MAY-16 06.00.58.515 PM                26-MAY-16 04.00.10.358 PM
       153 26-MAY-16 04.00.10.358 PM                26-MAY-16 05.00.12.350 PM
       154 26-MAY-16 05.00.12.350 PM                26-MAY-16 06.00.14.315 PM
       155 26-MAY-16 06.00.14.315 PM                01-JUN-16 11.30.27.958 AM
       156 01-JUN-16 11.30.27.958 AM                01-JUN-16 04.23.33.145 PM
       157 01-JUN-16 04.23.33.145 PM                01-JUN-16 05.00.34.463 PM
       158 01-JUN-16 05.00.34.463 PM                01-JUN-16 06.00.36.501 PM
       159 01-JUN-16 06.00.36.501 PM                01-JUN-16 06.10.36.899 PM
       160 01-JUN-16 06.10.36.899 PM                01-JUN-16 06.20.37.172 PM
 
9 rows selected.
 
 
四、总结
     此次用到 dbms_workload_repository 包中的 modify_snapshot_settings 存储过程,注意在修改成较短时间后,如果不需要的话,建议改回默认的 1 小时(60 分钟),以免生成过多的快照,占用空间。

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-06/132170.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4954字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中