Archive for the ‘Performance Views’ Category

Performance Monitoring

List of Common Oracle Metrics

Monitoring Methodologies


  • Reactive
  • Proactive

Reactive monitoring is not a recommend approach but at times it becomes inevitable. Oracle 10g provide tools that aid in proactively monitoring database. Server-generated alerts and Automated Database Diagnostic Monitor(AADM) are going to be discussed in another post.


Database and Instance Metrics

Performance measurements are commonly referred as databases metrics which is basically the rate of change of cumulative statistic that need to be monitored. Metrics are indicators of the health of various database targets. Excerpt from Oracle Database 10g New Features by Rampant TechPress

“The Metrics are the statistics derived from base statistics. They represent the delta values between the snapshot periods. Metrics are used by internal components (clients) for system health monitoring, problem detection and self-tuning. There are hundreds of different measurement points available to monitor database system.

Each metric is also associated with a metric name. You can query the view v$metricname to find the names of all the metrics.

SQL> select METRIC_NAME, METRIC_UNIT from v$metricname; "


Metric                                                  Description                                                                                                  
Event Class Metrics Metrics collected on the wait event class level. e.g.  DB_TIME_WAITING
Event Metrics Metrics collected on various wait events
File Metrics Long Duration Metrics collected at the file level. e.g. AVERAGE_FILE_WRITE_TIME
Service Metrics Metrics collected at the service level. e.g. CPU_TIME_PER_CALL
Session Metrics Short Duration Metrics collected at the session level. e.g. BLOCKED_USERS
System Metrics Short Duration Metrics collected at the system level
Tablespae Metrics Metrics collected at the Tablespace level e.g. TABLESPACE_PCT_FULL


Viewing Metrics

The new MMON background process collects database metrics continuously and automatically saves them in the SGA for one hour.It also transfers the memory version of AWR stats to disk on regular basis(in snapshots). Performance metrics are available through

  • Data Dictionary
  • Dynamic Performance Views
  • Optimizer Statistics

Data Dictionary Metrics

Data dictionary metrics provide information about space consumption and object status. Database indexes and stored procedures both need to be VALID to be used.

Unusable indexes always require DBA intervention to restore them to a valid state. Invalid
PL/SQL objects will normally recompile automatically the first time they are called, but
sometimes require DBA intervention in cases where the automatic recompilation fails.

Compiling PL/SQL procedures and Packages

Indexes can become unusable due to normal maintenance operations on tables. Unusable indexes can be rebuild to make them valid.

Rebuilding Indexes


Viewing In-Memory Metrics

All system related metrics saved in memory and they can be viewed using dynamic performance views. Most of the cumulative statistics can be viewed through following:


Example of few system metrics maintained in V$SYSMETRIC view:

  • Buffer Cache Hit Ratio
  • CPU Usage Per Sec
  • Disk Sort Per Sec
  • Host CPU Utilization (%)
  • Library Cache Hit Ratio
  • SQL Service Response Time
  • Shared Pool Free (%)

Viewing Saved Metrics

After every 60 min, MMON places metric info from SGA to disk through AWR snapshot mechanism. The metric data that AWR collects through MMON is permanently stored in DBA_HIST_* views like


Optimizer Statistics

Optimizer statistics for tables and indexes are stored in the data dictionary. These statistics are not intended to provide real-time data. They provide the optimizer a statistically correct snapshot of data storage and distribution which the optimizer uses to make decisions on how to access data.

Metrics collected include:

  • Size of the table or index in database blocks
  • Number of rows
  • Average row size and chain count (tables only)
  • Height and number of deleted leaf rows (indexes only)

As data is inserted, deleted, and modified these values change. The performance impact of
maintaining real-time data distribution statistics would be prohibitive, so these statistics are updated by periodically gathering statistics on tables and indexes.