Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

v$metricgroup and MMON

Oracle Tips by Burleson Consulting

The v$metricgroup and MMON

The MMON is the new background process that is responsible for gathering database metrics. MMON computes short duration metrics every 15 seconds and long duration metrics every 60 seconds. Database metrics are grouped by their meaning and duration. The available metric groups are available in the v$metricgroup view:

 

SELECT

   name,interval_size

FROM

   v$metricgroup

ORDER BY

   interval_size;

 

The interval_size column specifies the sampling frequency in hundredths of a second for every metric group.

 

Some of these metric values are more important than others, so the details on the most important metrics are presented in groups as follows:

§       User Metrics are useful for getting details on total user waits and PGA usage.

 

METRIC_NAME                              METRIC_UNIT

---------------------------------------- -----------------

Average Users Waiting Counts             Users

Blocked User Session Count               Sessions

PGA Memory (Session)                     Bytes

 

§       Wait Metrics are useful for getting details on total waits and total session waits.  These metrics are also very valuable in the AWR trend identification process as they can serve as a baseline value to compare with other AWR data.  For example, a query could be written to determine the long-term relationship between Database Time Spent Waiting and suspected bottleneck events such as physical reads, CPU, Network waits, etc.

 

METRIC_NAME                              METRIC_UNIT

---------------------------------------- -----------------

Database Time Spent Waiting (%)          % (TimeWaited /

Total Time Waited                        CentiSeconds

Total Wait Counts                        Waits

Number of Sessions Waiting (Event)       Sessions

Total Time Waited                        CentiSeconds

Total Wait Counts                        Waits

 

§       I/O Metrics are valuable for any I/O-bound Oracle database, and they show the overall performance of the disk I/O sub-system.  Tracking these events is especially useful when determining where to replace platter disks with solid-state disks.

 

METRIC_NAME                              METRIC_UNIT

---------------------------------------- -----------------

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

Physical Reads (Session)                 Reads

Physical Reads Ratio (Sess/Sys) %        %

Logical Reads Ratio (Sess/Sys) %         %

 

§       CPU Metrics are especially valuable since Oracle’s SQL costing algorithm has shifted from I/O costing to CPU costing.  Oracle has recognized that large data buffer caches such as db_cache_size and db_keep_cache_size have reduced overall disk I/O and driven up CPU consumption on the typical Oracle 10g database.   These CPU metrics are also valuable for trend analysis in which overall “CPU Time (Session)” might be compared with consistent gets from the RAM data buffers.

 

METRIC_NAME                              METRIC_UNIT

---------------------------------------- ----------------------

CPU Time Per User Call                   Microseconds Per Call

Elapsed Time Per User Call               Microseconds Per Call

CPU Time (Session)                       CentiSeconds

CPU Usage Per Sec                        CentiSeconds Per

CPU Usage Per Txn                        CentiSeconds Per Txn

 

The AWR stores metrics history for those times when MMON takes the corresponding snapshot, but the AWR does not retain all of the database metric history as it occurred over the course of time.

 

SEE CODE DEPOT FOR FULL SCRIPTS


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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


 

 

��  
 
 
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