#em12c Metrics – Part 3: Breakdown of Metrics using Repository Views

With my previous post on the “All Metrics” page breakdown in Enterprise Manager I talked about the collected, aggregated, and available information on metrics for a given Target type. As we’ve seen, the Metrics Categories are numerous and really depend on the monitored targets. Well, what does that mean? And, how does one find out the Metrics which are collected in the repository itself?

A quick search on google for mgmt$ views shows several blog posts that dive into specific queries that author(s) created to retrieve their specific data. Is there a single place where one can find all the management repository views? Of course there is, and its located right here in the Oracle Docs for Enterprise Manager.

These views contain data across a myriad of different area’s and frameworks within Enterprise Manager. A brief list includes

  • Application Deployment Views
  • Blackout Views
  • Chargeback Views
  • Compliance Views
  • Compliance Real-time Monitoring Views
  • Configuration Management Views
  • Custom Configuration Specification Views
  • Database Configuration Views
  • Events Views
  • Glassfish Views
  • Hardware Views
  • Inventory Views
  • Job Views
  • Linux Patching Views
  • Management Template Views
  • Metric Views
  • Monitoring Views
  • Operating System Views
  • Oracle Home Directory Patching Views
  • Oracle Home Directory Views
  • Oracle WebLogic Server Views
  • Oracle WebLogic Domain Views
  • Oracle WebLogic Cluster Views
  • Security Views
  • Service Tag Views
  • Storage Reporting Views
  • Target Views
  • VT Target Views

For myself and with relevance to this post, the important one(s) are the “Target” and “Metric” views. As of the 12.1.0.4.0 release, it seems the documentation has slightly changed where it doesn’t mention all the metric views.

col view_name format a50 heading "View Name"

select view_name
from user_views
where view_name like 'MGMT$METRIC%'
or view_name like 'GC$METRIC%';

View Name
--------------------------------------------------
GC$METRIC_CATEGORIES
GC$METRIC_COLUMNS
GC$METRIC_COLUMNS_TARGET
GC$METRIC_ERROR_CURRENT
GC$METRIC_ERROR_HISTORY
GC$METRIC_GROUPS
GC$METRIC_GROUPS_TARGET
GC$METRIC_KEYS
GC$METRIC_LATEST
GC$METRIC_STR_VALUES
GC$METRIC_STR_VALUES_LATEST
GC$METRIC_VALUES
GC$METRIC_VALUES_DAILY
GC$METRIC_VALUES_HOURLY
GC$METRIC_VALUES_LATEST
MGMT$METRIC_CATEGORIES
MGMT$METRIC_COLLECTION
MGMT$METRIC_CURRENT
MGMT$METRIC_DAILY
MGMT$METRIC_DETAILS
MGMT$METRIC_DETAILS_MULTI_NUM
MGMT$METRIC_DETAILS_MULTI_STR
MGMT$METRIC_DETAILS_SINGLE_NUM
MGMT$METRIC_DETAILS_SINGLE_STR
MGMT$METRIC_ERROR_CURRENT
MGMT$METRIC_ERROR_HISTORY
MGMT$METRIC_HOURLY

27 rows selected

So, the gc$ views (as far as I can tell) were introduced with EM12c and are more closely aligned with the Metric Categories and Names I mentioned with my previous post. For example, on a Host’s “All Metrics” page, under the “Load” category you would see the following metrics:

NewImage

The same information is available in the gc$metric_column tables. For example:

col entity_type format a20 heading "Entity|Type"
col metric_group_label format a7 heading "Metric|Group|Label"
col metric_column_label format a60 heading "Metric|Column|Label"

select entity_type
,metric_group_label
,metric_column_label
from gc$metric_columns
where metric_group_label = 'Load'
and entity_type = 'host';

                     Metric  Metric
Entity               Group   Column
Type                 Label   Label
-------------------- ------- ------------------------------------------------------------
host                 Load    Active Logical Memory, Kilobytes
host                 Load    Active Memory, Kilobytes
host                 Load    CPU in I/O Wait (%)
host                 Load    CPU Interrupt Time (%)
host                 Load    CPU in System Mode (%)
host                 Load    Run Queue Length (5 minute average,per core)
host                 Load    Run Queue Length (15 minute average,per core)
host                 Load    Run Queue Length (1 minute average,per core)
host                 Load    CPU Queue Length
host                 Load    CPU in User Mode (%)
host                 Load    CPU Utilization (%)
host                 Load    Free Memory, Kilobytes
host                 Load    Swap Free (KB)
host                 Load    Logical Free Memory (%)
host                 Load    Memory Utilization (%)
host                 Load    Free Memory (%)
host                 Load    Total Processes
host                 Load    Total Users
host                 Load    Memory Page Scan Rate (per second)
host                 Load    Page Transfers Rate
host                 Load    Swap Utilization (%)
host                 Load    Used Logical Memory (%)
host                 Load    Swap Utilization, Kilobytes

Coincidentally, the same information is available (albeit indirectly) from the mgmt$metric_current view.

col target_name format a20 heading "Target|Name"
col metric_label format a7 heading "Metric|Label"
col column_label format a60 heading "Column|Label"

select distinct
target_name
,metric_label
,column_label
from mgmt$metric_current
where target_name = 'coloriss.greenn.net'
and metric_label = 'Load'
order by metric_label
,column_label;

Target               Metric  Column
Name                 Label   Label
-------------------- ------- ------------------------------------------------------------
coloriss.greenn.net  Load    Active Logical Memory, Kilobytes
coloriss.greenn.net  Load    Active Memory, Kilobytes
coloriss.greenn.net  Load    CPU Utilization (%)
coloriss.greenn.net  Load    CPU in I/O Wait (%)
coloriss.greenn.net  Load    CPU in System Mode (%)
coloriss.greenn.net  Load    CPU in User Mode (%)
coloriss.greenn.net  Load    Free Memory (%)
coloriss.greenn.net  Load    Logical Free Memory (%)
coloriss.greenn.net  Load    Memory Page Scan Rate (per second)
coloriss.greenn.net  Load    Memory Utilization (%)
coloriss.greenn.net  Load    Run Queue Length (1 minute average,per core)
coloriss.greenn.net  Load    Run Queue Length (15 minute average,per core)
coloriss.greenn.net  Load    Run Queue Length (5 minute average,per core)
coloriss.greenn.net  Load    Swap Utilization (%)
coloriss.greenn.net  Load    Total Processes
coloriss.greenn.net  Load    Total Users
coloriss.greenn.net  Load    Used Logical Memory (%)                                      

 17 rows selected

Let’s focus our attention back to the gc$ views again. I’ve yet to contact the EM product team and probe them further on the reason why it was separated, but I am quite sure there was a significant reason for the deviation. Perhaps the collection and storage method needed to change to accommodate the new frameworks.

So, the list of metrics that can be captured obviously depends on the types of targets that are currently monitored by EM. In my environment, the breakdown was the following:

col type_display_name format a40 heading "Type|Display|Name"
col per_type_groups_count format 9999 heading "Per|Type|Group|Count"

select b.type_display_name
,count(metric_group_label) as per_type_groups_count
from gc$metric_groups a
,mgmt_target_types b
where a.entity_type = b.target_type
group by b.type_display_name
order by per_type_groups_count DESC;

                                                          Per
Type                                                     Type
Display                                                 Group
Name                                                    Count
---------------------------------------- --------------------
Database Instance                                         569
Cluster Database                                          337
Oracle WebLogic Server                                    264
Oracle WebCenter Portal                                   169
Fusion J2EE Application                                   159
Application Deployment                                    157
Host                                                      138
Oracle Access Management Server                           108
Fusion Instance                                           104
Oracle Portal                                              94
SOA Infrastructure                                         91
Oracle Identity Manager                                    88
OC4J                                                       88
Scheduler Service                                          82
Oracle Authorization Policy Manager                        82
Oracle Internet Directory                                  72
Identity Federation Server                                 64
Pluggable Database                                         64
Oracle Adaptive Access Manager                             55
Oracle Coherence Cluster                                   54
Web Cache                                                  54
Oracle WebLogic Cluster                                    53
Oracle Universal Content Management                        52
Oracle Social Network                                      52
Oracle Access Management Cluster                           52
Oracle BI Instance                                         49
Oracle HTTP Server                                         49
Oracle GlassFish Server                                    48
Oracle Coherence Node                                      48
Connector Extension Framework                              47
Automatic Storage Management                               47
...

So what type of metrics do we actually capture for, lets say an 11gR2 database instance?

col entity_type format a40 heading "Entity Type"
col metric_group_label format a40 heading "Metric Group Label"
col number_of_metrics format 999 heading "# of Metrics"

select entity_type
,metric_group_label
,count(1) as number_of_metrics
from gc$metric_columns
where entity_type = 'oracle_database'
group by entity_type, metric_group_label
order by entity_type, metric_group_label;

 Entity Type                              Metric Group Label                       # of Metrics
---------------------------------------- ---------------------------------------- ------------
oracle_database                            File Permission and Owner                         3
oracle_database                           DB Alert Log Error Status                          5
oracle_database                           network/admin Directory Permission                 3
oracle_database                          ADDM Report - Global                               16
oracle_database                          ALL Privileges                                      3
oracle_database                          ANY Privileges                                      3
oracle_database                          Access To Important Tables And Views                3
oracle_database                          Active Sessions by CPU and Wait  Classes            3
oracle_database                          Alert Log                                           8
oracle_database                          Alert Log Content                                   1
oracle_database                          Alert Log Error Status                              5
oracle_database                          Archive Area                                        5
oracle_database                          Archive Area - RAC Instance                         5
oracle_database                          Audit Settings                                      3
oracle_database                          Audit Syslog Level                                  3
oracle_database                          AutoTask Client                                     2
...

The list above is actually quite exhaustive, but we can quickly found out the actual metrics captured for a given target:

col entity_type format a40 heading "Entity Type"
col metric_group_label format a40 heading "Metric Group Label"
col number_of_metrics format 99999 heading "# of Metrics"

select entity_type
      ,metric_group_label
      ,count(1) as number_of_metrics
  from gc$metric_values_daily
 where entity_type = 'oracle_database'
   and entity_name = ’SIDBA'
 group by entity_type, metric_group_label
 order by entity_type, metric_group_label;

Entity Type                              Metric Group Label                       # of Metrics
---------------------------------------- ---------------------------------------- ------------
oracle_database                          Alert Log Error Status                            515
oracle_database                          CPU Usage                                         127
oracle_database                          Database Limits                                   635
oracle_database                          Database Size                                     254
oracle_database                          Dump Area                                        1524
oracle_database                          Efficiency                                       2413
oracle_database                          Global Cache Statistics                           508
oracle_database                          Interconnect Traffic                              127
oracle_database                          Memory Usage                                      127
oracle_database                          OCM Instrumentation                               381
oracle_database                          Optimal SGA                                       127
oracle_database                          PGA Allocated                                     127
oracle_database                          Response                                            3
oracle_database                          SCN Instance Statistics                           127
oracle_database                          SGA Pool Wastage                                  381
oracle_database                          Standard Tablespace Monitor                       222
oracle_database                          System Response Time Per Call                     127
...

The EM team has done a superb job of documenting the metrics in the Oracle Enterprise Manager Oracle Database Plug-in Metric Reference Manual. I have used this page as a reference many times in the past, and what is important to note is that the Metric Names listed in the document is the same as the “Metric Column Name” in the gc$metric_values_% tables. Not only that, but the descriptions are quite informative as well.

NewImage

I could go continue to dive deeper and deeper into these metrics, however I feel that’s an adventure you should embark upon yourself. The one common question I’ve heard from most people that have approached me on metrics is “What does they actually mean?”. My answer is more often than not, that is just depends on how you look at it. How does one interpret DB Time or CPU Utilization %?

Start with the documentation, ask the questions in forums and oracle-l, and I promise an answer will be waiting for you.

Look out for more posts on how I’ve visualized these metrics by using BI Publisher.

Cheers!

2 comments

  1. Thanks for pointing me in the right direction.
    I made a Metric Extension called dblink.
    Al it does is :
    SELECT d.name ||’:’
    ||’:’ || dbl.owner
    ||’:’ || dbl.db_link
    ||’:’ || dbl.username
    ||’:’ || dbl.created RIJ
    , ‘DBLINKINFO:’ text
    FROM dba_db_links dbl,
    v$database d

    Next I added rij as the key column and tekst as the value column.

    Next Next Finish
    Deploy to targets

    And now you can see ALL your database links from a single point of view in your EM12C repository database.

    select entity_name,metric_key_value, count(*)
    from GC_METRIC_STR_VALUES a
    where metric_group_label=’dblink’
    group by entity_name,metric_key_value

    Regards

    Dik Pater
    The Netherlands

    1. That’s a cool idea Dik. Thanks for sharing!!

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: