AWR生成快照间隔调整
AWR生成快照间隔调整
一、描述
测试环境经常会对数据库进行压力测试,在这个测试过程中,有稳定性,也有突发性,因为我对测试方面不是很专业,所以自己理解成这个样子,要在短时间内高并发的测试时,需要调整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.Parameter | Description |
---|---|
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 |
|
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. |
本文永久更新链接地址:
评论暂时关闭