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:
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.
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!
Maaz Anjum's Blog 
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
That’s a cool idea Dik. Thanks for sharing!!