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.

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: