Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  Oracle Tips by Burleson

Easy Oracle Automation
Chapter 4 -
Automatic Workload Repository and Automatic Database Diagnostic Monitor

Base Statistics, Metrics, and Statistics Levels

The modify_snapshot_settings procedure can be run to control the behavior of snapshots. The retention parameter determines how much AWR information is retained by the database. On a typical system, AWR collections require 200 MB to 300 MB of space if the data is kept for seven days.  The default is seven days (10080) and the minimum is one day.

The space consumption depends mainly on the number of active sessions in the system, so adjust accordingly.  If the retention value is set to 0, it will disable the automatic purging. If AWR detects that SYSAUX is out of space, it automatically deletes the oldest set of snapshots and reuses the space. Alerts will be sent to the DBA using the designated communication methods (email/pager) to inform them that SYSAUX is running out of space.

The interval parameter determines the frequency of snapshot generation. The minimum value is 10 minutes and the default is 60 minutes. Setting it to 0 will disable the automatic capturing of snapshots. Typical values are 10, 20, 30, 60, 120, etc.

Similar to STATSPACK, the AWR has a report generation mechanism to produce summary reports on statistics stored in the AWR repository. The analysis is done on statistics over a period of time and the reports are generated using the script awrrpt.sql in the $ORACLE_HOME/rdbms/admin directory. The script displays all the AWR snapshots available and prompts for options on how and where the reports are to be generated. The awrrpt.sql script produces two kinds of output. The default is HTML format with hyperlinks, and the other is in text format similar to STATSPACK reports. When the script is run, it prompts for the format of the report and the report name.

The above text is an excerpt from:

Easy Oracle Automation
Oracle10g Automatic Storage, Memory and Diagnostic Features

ISBN 0-9745993-6-0

by Dr. Arun Kumar R.

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks