Adjust the interval between snapshots generated by AWR

Source: Internet
Author: User

Adjust the interval between snapshots generated by AWR

I. Description

The test environment often performs stress tests on the database. During the test process, the database is stable and burstable, because I am not very professional in the test, so I understand it like this, to perform highly concurrent tests in a short period of time, you must adjust the interval between AWR and snapshot generation in the Oracle database to generate an AWR report in a short period of time. You can use the MODIFY_SNAPSHOT_SETTINGS stored procedure in the DBMS_WORKLOAD_REPOSITORY package to modify. Of course, you can also manually generate snapshots (dbms_workload_repository.create_snapshot ). 2. Official documents: The DBMS_WORKLOAD_REPOSITORY package lets you manage the Workload Repository, and sort Ming 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. partition (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 cals, retriable 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.

Iii. Experiment 1. check the AWR report generation interval 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. change the AWR report generation interval to 10 minutes (minimum 10 minutes, maximum 1 year). SYS @ OCM11G> exec dbms_workload_re Pository. modify_snapshot_settings (interval => 10); PL/SQL procedure successfully completed. 3. check the modified Configuration Parameter SYS @ OCM11G> select * from dba_hist_wr_control; DBID SNAP_INTERVAL retention topnsql ---------- ---------------------------- hour ---------- 3465366841 + 00000 00:10:00. 0 + 00008 00:00:00. 0 DEFAULT 4. wait for 10 minutes to verify that the new snapshot file SYS @ OCM11G> select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTE RVAL_TIME from DBA_HIST_SNAPSHOT order by BEGIN_INTERVAL_TIME; SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- Jun 152 23-MAY-16 may pm 26-MAY-16 04.00.10.358 PM 153 26-MAY-16 may 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 1 Running AM 156 01-JUN-16 running AM too PM 157 running PM 01-JUN-16 running PM 158 01-JUN-16 running PM 01-JUN-16 running PM 159 01-JUN-16 running PM 01-JUN-16 running PM 01-JUN-16 running PM 01-JUN-16 running PM selecting PM 9 Ro. iv. Summary The modify_snapshot_settings stored procedure in the dbms_workload_repository package is used this time. Note that if you do not need it after it is modified to a short time, we recommend that you change it Back to the default 1 hour (60 minutes), to avoid generating excessive snapshots and occupying space.

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.