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 ProceduresThis 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. SyntaxDBMS_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	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 a30SYS@OCM11G >col RETENTION for a30SYS@OCM11G >set lines 120SYS@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分鐘），以免產生過多的快照，佔用空間。

本文永久更新連結地址：