#em12c Metrics – Part 1: An Introduction

From the middle of 2013, I’d been busy in preparation for one of my presentations for IOUG’s Collaborate 14 Conference in Las Vegas. It is on Capacity Planning Enterprise Manager 12c’s Metrics (available on slideshare), one which I had the honor of presenting earlier at a Georgia Oracle User Group meeting in Atlanta, GA this past week. Metrics in any version of Enterprise Manager are collected (via the Enterprise Manager agents) and stored in its repository database, to be used for rendering historical viewing, but only at each targets respective home page. With EM12c, the list of monitored targets has grown to a staggering amount, especially with the advent of Extensibility Exchange and Metric Extensions (previously known as User Defined Metrics).

From Oracle Databases, multiple Unix Platforms, various types of Middleware products, Oracle VM, The Oracle Cloud, Engineered Systems such as Exadata, Exalogic, and the Big Data Appliance, and many other targets, this tool sure does cover a wide spectrum with an even wider range of metrics (or insights) into each managed target. In addition, using Plug-ins developed either by Oracle or by third party vendors, external hardware/software monitoring is also possible on technology such as VMware, NetApp, Cisco, Brocade, HP Storage, EMC Storage, F5 Load Balancers, and like wise many others. All of this monitored data is indeed stored somewhere, and as I have mentioned earlier, it is simply kept in the Enterprise Managers repository.

Starting with this post, I’d like to begin a series that discusses the various parts and pieces associated with Metrics in Enterprise Manager 12c.

I have already established that data is collected from managed/monitored targets, but have yet to explain the delicate intricacies of that collection. By default, once a target is discovered and promoted in EM12c, the collection of certain metrics that are enabled on a collection schedule. Both of which depend on the target type. As an example, lets take a look at an “Oracle Database”. Each time one is added to the EM inventory, we automatically assume that information regarding its configuration, status, etc will be displayed. That is precisely the kind of “default collection of metrics on a schedule” that I mentioned earlier.

So, how does the data get to the repository? One way to look at it, and some of depictions are straight out of my presentation, is that data from targets is collected by the EM Agents, and pulled into the Management Repository. This is a big shift from the previous releases of Enterprise Manager because they employed the push method (from agents) as opposed to a pull method from the Management Server.

NewImage

The data lands in the em_metric_value table which contains the “raw” data. A quick look at this table’s structure and data reveals the rawness of the information that is collected.

desc em_metric_values
Name            Null     Type
--------------- -------- -----------------------
METRIC_ITEM_ID  NOT NULL NUMBER(38)
COLLECTION_TIME NOT NULL DATE
MET_VALUES      NOT NULL EM_METRIC_VALUE_ARRAY()
 
col metric_item_id format 9999999 heading "Metric Item ID"
col collection_time format a25 heading "Collection Time"
col met_values format a100 heading "Metric Values"

select metric_item_id
      ,collection_time
      ,met_values
  from em_metric_values
 where rownum < 11; -- Only used to restrict the data returned.

 Metric Item ID Collection Time           Metric Values
-------------- ------------------------- ----------------------------------------------------------------------------------------------------
       1561578 13-FEB-14 12.13.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,0.209,null,0,72.899,null,null,16,0,null,0.018)
       1561578 13-FEB-14 12.28.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,0.191,null,0,68.343,null,null,15,0,null,0.017)
       1561578 13-FEB-14 12.43.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,3.604,null,0,651623.938,null,null,2310,0,null,2.57)
       1561578 13-FEB-14 12.58.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,0.187,null,0,68.343,null,null,15,0,null,0.017)
       1561578 13-FEB-14 01.13.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,0.206,null,0,68.343,null,null,15,0,null,0.017)
       1561578 13-FEB-14 01.28.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,0.184,null,0,68.343,null,null,15,0,null,0.017)
       1561578 13-FEB-14 01.43.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,4.53,null,0,958473.112,null,null,3347,0,null,3.723)
       1561578 13-FEB-14 01.58.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,0.195,null,0,68.343,null,null,15,0,null,0.017)
       1561578 13-FEB-14 02.13.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,0.191,null,0,72.899,null,null,16,0,null,0.018)
       1561578 13-FEB-14 02.28.46 AM     SYSMAN.EM_METRIC_VALUE_ARRAY(null,null,0,0.19,null,0,63.786,null,null,14,0,null,0.016)               

 10 rows selected

At regular intervals, this table’s data is aggregated into hourly and daily metric values. The corresponding tables are em_metric_values_hourly and em_metric_values_daily.

To ensure adequate performance, all three tables are partitioned as per the chart below. More information regarding the partitioning strategy can be found in “12c Cloud Control Repository: How to Modify the Default Retention and Purging Policies for Metric Data? (Doc ID 1405036.1)”.

NewImage

Now, I probably know what you are thinking. If I query the raw data, then what good is it to me in the above format. To understand and view the data coherently, the mgmt$metric_values, mgmt$metric_values_hourtly, mgmt$metric_values_daily OR gc$metric_values, gc$metric_values_hourly, gc$metric_values_daily views which are compliments of the tables mentioned earlier.

You might have seen various queries that use the mgmt$ tables, but from what I seen the gc$ tables are newer versions with slightly different metric column names and labels.

Let’s take a quick look at the gc$metric_values and its contents.

desc gc$metric_values
Name                      Null     Type
------------------------- -------- -------------
ENTITY_TYPE               NOT NULL VARCHAR2(64)
ENTITY_NAME               NOT NULL VARCHAR2(256)
TYPE_META_VER             NOT NULL VARCHAR2(8)
METRIC_GROUP_NAME         NOT NULL VARCHAR2(64)
METRIC_COLUMN_NAME        NOT NULL VARCHAR2(64)
COLUMN_TYPE               NOT NULL NUMBER(1)
COLUMN_INDEX              NOT NULL NUMBER(3)
DATA_COLUMN_TYPE          NOT NULL NUMBER(2)
METRIC_GROUP_ID           NOT NULL NUMBER(38)
METRIC_GROUP_LABEL                 VARCHAR2(64)
METRIC_GROUP_LABEL_NLSID           VARCHAR2(64)
METRIC_COLUMN_ID          NOT NULL NUMBER(38)
METRIC_COLUMN_LABEL                VARCHAR2(64)
METRIC_COLUMN_LABEL_NLSID          VARCHAR2(64)
DESCRIPTION                        VARCHAR2(128)
SHORT_NAME                         VARCHAR2(40)
UNIT                               VARCHAR2(32)
IS_FOR_SUMMARY                     NUMBER
IS_STATEFUL                        NUMBER
NON_THRESHOLDED_ALERTS             NUMBER
METRIC_KEY_ID             NOT NULL NUMBER(38)
KEY_PART_1                NOT NULL VARCHAR2(256)
KEY_PART_2                NOT NULL VARCHAR2(256)
KEY_PART_3                NOT NULL VARCHAR2(256)
KEY_PART_4                NOT NULL VARCHAR2(256)
KEY_PART_5                NOT NULL VARCHAR2(256)
KEY_PART_6                NOT NULL VARCHAR2(256)
KEY_PART_7                NOT NULL VARCHAR2(256)
COLLECTION_TIME           NOT NULL DATE
COLLECTION_TIME_UTC                DATE
VALUE                              NUMBER
col entity_type format a10 heading "Entity|Type"
col entity_name format a25 heading "Entity|Name"
col metric_group_label format a7 heading "Metric|Group|Label"
col metric_group_name format a14 heading "Metric|Group|Name"
col metric_column_label format a50 heading "Metric|Column|Label"
col metric_column_name format a14 heading "Metric|Column|Name"
col short_name format a15 heading "Short|Name"
col value format 99.99 heading "Value"

select entity_type
      ,entity_name
      ,metric_group_name
      ,metric_column_name
      ,metric_group_label
      ,metric_column_label
      ,short_name
      ,collection_time
      ,value
  from gc$metric_values
 where rownum < 11; -- Only used to restrict rows returned.  

                                     Metric         Metric         Metric  Metric
Entity     Entity                    Group          Column         Group   Column                                             Short
 Type       Name                     Name           Name           Label   Label                                               Name            Collection Time            Value
---------- ------------------------- -------------- -------------- ------- -------------------------------------------------- --------------- ------------------------- ------
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.01.56 AM       4.08
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.06.56 AM       4.11
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.11.56 AM       4.03
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.16.56 AM       4.03
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.21.56 AM       4.01
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.26.56 AM       4.00
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.31.56 AM       4.01
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.36.56 AM       4.11
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.41.56 AM       4.01
host       server01.planet.net       Load           cpuLoad_1min   Load    Run Queue Length (1 minute average,per core)       CPU Load (1min) 13-FEB-14 12.46.56 AM       4.00 

 10 rows selected

I know this blog posts probably lends itself to more questions. What data, other than the one showed above, do we actually have access to in Enterprise Manager? How can we obtain the information and then create reports on resource utilization for trend analysis, and capacity planning? How does Enterprise Manager allow data visualization? Which tools could I use for custom reports? Enterprise Manager does indeed monitor, keep track of, and enables the user to gather a myriad of information from each target.

The data is there.

Stay tuned for future posts which will cover the topics I have touched on in the sections above. If you are headed to Collaborate this year, and are interested in hearing further in-person, my Session # is 102 Capacity Planning: How to Leverage OEM12c for Engineered Systems.

Cheers!

 

 

 

 

 

 

 

 

Advertisements

10 comments

  1. Thanks! Be on the lookout for more, as I have only scratched the surface 🙂

  2. Back in version 2.0 of OEM, Oracle allowed you to define the retention for the rollups – daily, monthly, etc.

    The default is now one year retention of daily stats but I don’t see any discussion of how to change the number of years it keeps.

    Do you know how to change it?

    Would you like some sqldeveloper charts I created that go against EM12c metrics? Send email to jacornell@landolakes.com and I will send you a collection. Use them at your own risk with no expectation of support from me…

    1. Joe,

      Great question! Take a look at “12c Cloud Control Repository: How to Modify the Default Retention and Purging Policies for Metric Data? (Doc ID 1405036.1)”.

      I often change the retention for the hourly to 1 year, and daily metrics to 3 years so I can perform trend analysis for resource utilization and capacity planning.

      Check out this presentation I did at Collaborate this year: http://www.slideshare.net/MaazAnjum/maaz-anjum-ioug-em12c-capacity-planning-with-oem-metrics

      Sure, I’d be happy to take a look at SQL Developer charts – I love that tool 🙂 In fact, I can share them with the product team who will definitely be interested.

      Cheers,
      Maaz

  3. Hi Maaz,

    Your blogs are very informative especially i am trying to understand the metrics part how oem stores the values.

    But still not clear is, how the values in EM_METRIC_VALUES table Metric_values column data is stored as raw data in Array. noticed this column has 3 sets of values in a array for every metric.. out of 3 values in set which one is for what?

    Thanks
    Srini.

    1. Hi Srini,

      Thanks for the feedback! You know, this would likely be a great question for the EM Product Team. But more importantly, why does it matter which column the RAW data is stored? I only showed that to illustrate the source and mechanism by which the data is moved from one set of tables to another.

      Cheers,
      Maaz

  4. so, instead of relying on em_metric_values table/view we can go to gc$metric_values table right?
    same gc$metric_values table infact gives the metric property name & category details along with the value fom a same table too.

    Thanks
    Srini.

  5. Dineshkumar · · Reply

    Hi Maazanjum,

    I am trying to generate the I/O report for the targets in OEM 12c. But not able to get the correct view from which i can get the data. Can you please throw some lights how to generate the report for I/O based for all the targets.

    1. Hi Dinesh,

      IO could mean a lot of things depending on the type of target you’re referring to; database, server, asm, physical disks etc. Take a look at one of my subsequent blog posts https://maazanjum.com/2014/07/25/em12c-metrics-part-3-breakdown-of-metrics-using-repository-views/ for more information on how to find what you’re looking for and keep my posted.

      Thanks,
      Maaz

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Musings

Things I see and learn!

Thoughts from James H. Lui

If you Care a Little More, Things Happen. Bees can be dangerous. Always wear protective clothing when approaching or dealing with bees. Do not approach or handle bees without proper instruction and training.

bdt's oracle blog

Sharing experience (by Bertrand Drouvot)

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Vishal desai's Oracle Blog

Just another WordPress.com weblog

%d bloggers like this: