Oracle Database Metric v$ Views Oracle Tips by Burleson Consulting

Oracle Database Metric v$ Views

In Oracle database releases prior to 11g, Oracle DBAs were often tasked to compute numerous statistics like ratios that give an overview of database performance and workload at a glance. For example, database buffer hit ratio , library cache hit ratio , or physical reads per second , etc. In order to compute such statistics, Oracle DBAs were required to write complex queries, which joined several v$ views like v$sysstat and v$statname. These queries were quite resource costly and placed additional overhead on the database.

Now, nearly all significant database statistics such as those mentioned above are already automatically pre-computed by the database and are available for immediate use. The Oracle11g database introduces a new type of statistic known as a database metric. There are several database metric-related v$ views available. The most interesting and valuable views will be presented in the following text.

All database metrics are grouped into two categories: long duration; and short duration. The long duration database metrics are computed by Oracle every 60 seconds, and short duration metrics are computed every 15 seconds. Furthermore, all metrics are grouped by their meaning. The available metric groups are available in the v$metricgroup view:

SQL> SELECT name,interval_size FROM v$metricgroup ORDER BY interval_size;

The interval_size column specifies the sampling frequency, in hundredths of seconds, for every metric group. The names of the metric groups are self explanatory and identify each group as a long or short duration group.

The available database metrics computed by Oracle can all be viewed through the v$metricname view. There are more than 180 metrics available. This view displays information such as metric name, corresponding metric group, and metric unit that exposes the meaning of the particular metric.

SELECT
 group_name,
 metric_name,
 metric_unit
FROM
 v$metricname
ORDER BY
 group_name,
 metric_name;

The truncated output of this query shows the different metrics available:

GROUP_NAME                     METRIC_NAME                            METRIC_UNIT
---------------------------------------------------------------------------------------
Event Class Metrics            Average Users Waiting Counts           Users
                               Database Time Spent Waiting(%)       % TimeWaited/DBTime
                               Total Time Waited                      CentiSeconds
                               Total Wait Counts                      Waits
Event Metrics                  Number of Sessions Waiting (Event)     Sessions
                               Total Time Waited                      CentiSeconds
                               Total Wait Counts                      Waits
File Metrics Long Duration     Average File Read Time (Files-Long)    CentiSeconds Per Read
                               Average File Write Time (Files-Long    CentiSeconds Per Write
                               Physical Block Reads (Files-Long)      Blocks
                               Physical Block Writes (Files-Long)     Blocks
                               Physical Reads (Files-Long)            Reads
                               Physical Writes (Files-Long)           Writes
Service Metrics                CPU Time Per User Call                 Microseconds Per Call
                               Elapsed Time Per User Call             Microseconds Per Call
Session Metrics Long Duration  Blocked User Session Count             Sessions
Session Metrics Short Duration CPU Time (Session)                     CentiSeconds 
                               Hard Parse Count (Session)             Parses
                               Logical Reads Ratio (Sess/Sys) %      %SessLogRead/SystemLogRead
                               PGA Memory (Session)                   Bytes
                               Physical Reads (Session)               Reads
                               Physical Reads Ratio (Sess/Sys) %     %SessPhyRead/SystemPhyRead
                               Total Parse Count (Session)            Parses
                               User Transaction Count (Session)       Transactions
System Metrics Long Duration   Background Checkpoints Per Sec         Check Points Per Second
                               Branch Node Splits Per Sec             Splits Per Second
                               Branch Node Splits Per Txn             Splits Per Txn
                               Buffer Cache Hit Ratio          %      (LogRead - PhyRead)/LogRead
                               CPU Usage Per Sec                      CentiSeconds Per Second
                               CPU Usage Per Txn                      CentiSeconds Per Txn
                               CR Blocks Created Per Sec              Blocks Per Second
                               CR Blocks Created Per Txn              Blocks Per Txn
                               CR Undo Records Applied Per Sec        Undo Records Per Second
                               CR Undo Records Applied Per Txn        Records Per Txn
                               Consistent Read Changes Per Sec        Blocks Per Second

Oracle keeps a history for database metrics in a circular memory buffer.  This history is available through several v$ metric views. This history is periodically saved to the AWR repository by MMON process as well. By default, Oracle retains database metrics history for one hour duration.

A rolling forward history for database metrics can be exposed through the use of such v$ views as v$sysmetric_history , v$waitclassmetric_history , and v$filemetric_history.

---

If you are looking to enhance your Oracle tuning skills, you may enjoy Rampant TechPress – Oracle Tuning: The Definitive Reference by Donald K. Burleson , with over 900 pages of BC's favorite tuning tips & scripts.   You can buy it directly from the publisher and get instant access to the code depot of Oracle tuning scripts.