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 

How to Use the AWR

To get started with the AWR, nothing needs to be installed other than the Oracle10g database.  This is one feature of the AWR that is a benefit over STATSPACK.  The AWR automatically collects data as soon as Oracle10g is installed and running.  With STATSPACK, the DBA determined the frequency of data collection and then scheduled a job to run at the appropriate interval to gather data.  With the AWR, the documentation states that these collections are done every 30 minutes, but the frequency can be modified by the user.  The resulting data is stored in the AWR for seven days, by default, after which the data is automatically purged. 

Based on user testing, it appears that the default of 30 minute snapshot intervals, as stated by the documentation, may not be correct.  In some cases, the interval appeared to be 60 minutes rather than 30 minutes.  A later section will provide a method for determining the actual “default” sampling interval.  Additionally, instructions on how to change these defaults are also included.  Every DBA should assess the frequency of the snapshots as well as the length of time they are retained and adjust the AWR settings accordingly. 

This data is gathered and populated into the AWR by the new 10g MMON and MMNL (MMON Lite) processes.  MMON is also responsible for issuing alerts when there are metrics that exceed thresholds. 

As far as using this data, this book will only address the use of scripts to access the AWR data.  Oracle Enterprise Manager (OEM) can also be used to access the data, but OEM is a topic unto itself.  In this book, this topic is limited to the non-gui tools or direct access to the data dictionary objects.

Since nothing extra needs to be installed, the data tables that support this feature are up for discussion.  The AWR contains a number of new tables in the data dictionary.  These are:

  • dba_hist_active_sess_history

  • dba_hist_baseline

  • dba_hist_database_instance

  • dba_hist_snapshot

  • dba_hist_sql_plan

  • dba_hist_wr_control

The structure of dba_hist_active_sess_history is essentially the same as v$active_session_history with the exception of the additional fields at the top with which to track the snapshot details for when and where the data was collected.  This view actually represents the data that is periodically collected from v$active_session_history in a  manner similar to the way STATSPACK collects data.

The above book excerpt is from:

Oracle Wait Event Tuning

High Performance with Wait Event Iinterface Analysis 

ISBN 0-9745993-7-9  

Stephen Andert

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