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 statistics_level

Oracle Tips by Burleson Consulting

The dynamic v$ views that were introduced or changed in the Oracle10g database were presented in another chapter of this book. This chapter covers, in more detail, the important database statistics known as metrics. Database metrics were introduced in version 10g of the Oracle database.  Metrics represent various database performance statistics as rates.  

These rates are measured using units such as time, number of database calls, number of transactions, etc. Metrics show the rate of change of cumulative database statistics. For example, the Hard Parse Count Per Txn metric reports the number of hard parses per transaction. The Total Table Scans Per Sec metric describes database full table scan activity per second.

One great benefit of metrics is that they are automatically computed by Oracle with minimal overhead placed on the database server. In previous Oracle releases, Oracle DBAs manually computed similar metrics using v$ views like v$sysstat . With the release of Oracle10g, database metrics have become immediately available for use. Database metrics are perfect candidates for assisting the DBA with database monitoring tasks such as system health monitoring, database workload monitoring, problem detection and alerting, and self-tuning. Metrics are intensively used by internal Oracle10g clients for self-tuning purposes.

The server alert mechanism introduced in Oracle10g also uses database metrics for alerting DBAs when certain metrics violate their thresholds. The Manageability Monitor  (MMON) background process, during its work, performs threshold verification and alert generation, if required. Using Advanced Queuing , the alerts generated are queued to a special alert queue owned by SYS. The Oracle10g Enterprise Manager (OEM) console provides access to the alert queue and notifies the DBA by e-mail or pager. Server generated alerts are always visible through OEM.

Custom thresholds for the database metrics that are available can be easily defined and activated using the OEM console. Furthermore, the DBA can create custom metrics and have MMON monitor them the same way it does the pre-defined metrics. Below is a sample OEM screen that provides access to database metrics.


Figure 7.1:  Access database metrics in the OEM Console.

Unlike STATSPACK, AWR uses a "Top N" method which defaults to collect the Top-30 SQL statements for each SQL category (statistics_level=typical).  If you set statistics_level = all, AWR will collect the top 100 SQL statements.

Database metrics are not computed by Oracle when the initialization parameter called statistics_level  is set to BASIC.  In this event, Oracle does not collect AWR or metric statistics at all. The statistics_level parameter must be set to the default setting of TYPICAL, or it can be set to a value of ALL if the DBA wants to view and monitor database metrics.



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