Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







AWR dbms_workload_repository .modify_snapshot_settings

Oracle Tips by Burleson Consulting

Statistic Management in AWR and STATSPACK

Both STATSPACK and AWR take a snapshot of the v$ dynamic view and store it in repositories. 


The AWR has a special background process, MMON, which is responsible for gathering regular snapshots. The DBA is able to specify the frequency at which MMON gathers snapshots via the dbms_workload_repository  .modify_snapshot_settings procedure:


SQL> desc dbms_workload_repository




 Argument Name     Type            In/Out Default?

 ----------------- --------------- ------ --------

 RETENTION         NUMBER          IN     DEFAULT

 INTERVAL          NUMBER          IN     DEFAULT

 DBID              NUMBER          IN     DEFAULT


The interval parameter sets the time interval, in minutes, between the snapshots. The default interval between snapshots is 60 minutes. The valid range of values for this parameter ranges from 10 minutes to 52,560,000 minutes (100 years). The dbms_workload_repository package has the global variables min_interval  and max_interval , which set the lower and upper limits for this parameter. If the value specified for the interval is zero, automatic and manual snapshots will be prohibited.


The first dbms_workload_repository.modify_snapshot_settings procedure parameter, retention , allows the DBA to specify the time period, in minutes.  The AWR will preserve that particular snapshot in the repository. The default value for this parameter is 10,080 minutes (seven days). The valid range of values for this parameter also ranges from 10 minutes to 52,560,000 minutes (100 years). The dbms_workload_repository package has global variables min_retention and max_retention, which set up the lower and upper limits for the retention parameter. If a zero value is specified for retention, snapshots will be stored for an unlimited time.


The current settings for AWR retention and interval parameters can be viewed using dba_hist_wr_control  data dictionary view using awr_settings.sql script:






      extract( day from snap_interval) *24*60+

      extract( hour from snap_interval) *60+

      extract( minute from snap_interval ) "Snapshot Interval",

      extract( day from retention) *24*60+

      extract( hour from retention) *60+

      extract( minute from retention ) "Retention Interval"

from dba_hist_wr_control;


This script returns the current AWR interval values in minutes:


Snapshot Interval Retention Interval

----------------- ------------------

               60              10080


The Ion tool provides a GUI interface, shown in Figure 5.2, which can be used to set the AWR interval parameters:


Figure 5.2:  Setting AWR parameters in the Ion tool.


STATSPACK has many more settings that can be configured.  These are kept in the stats$statspack_parameter table. This table stores a single row for the database parameters with the corresponding STATSPACK settings.  These settings influence the amount of information STATSPACK gathers from the v$ views.


SQL> desc stats$statspack_parameter


Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 DBID                                      NOT NULL NUMBER

 INSTANCE_NUMBER                           NOT NULL NUMBER

 SESSION_ID                                NOT NULL NUMBER

 SNAP_LEVEL                                NOT NULL NUMBER

 NUM_SQL                                   NOT NULL NUMBER

 EXECUTIONS_TH                             NOT NULL NUMBER

 PARSE_CALLS_TH                            NOT NULL NUMBER

 DISK_READS_TH                             NOT NULL NUMBER

 BUFFER_GETS_TH                            NOT NULL NUMBER

 SHARABLE_MEM_TH                           NOT NULL NUMBER

 VERSION_COUNT_TH                          NOT NULL NUMBER

 PIN_STATSPACK                             NOT NULL VARCHAR2(10)

 ALL_INIT                                  NOT NULL VARCHAR2(5)

 LAST_MODIFIED                                      DATE

 UCOMMENT                                           VARCHAR2(160)

 JOB                                                NUMBER


The stats$statspack_parameter configuration table stores the following settings for STATSPACK:


*       See the code depot page for instructions on downloading a free copy of Ion


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts:




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational