Query the Enterprise Manager collected metrics

By Franck Pachot

.
Enterprise Manager (Cloud Control for example) gathers a lot of metrics. You can display them from the GUI, but you can also query the SYSMAN views directly. Today, I wanted to get the history of free space in an ASM disk group for the previous week. Here is how I got it.
Enterprise Manager metrics are aggregated in MGMT_METRICS_1HOUR (granularity 1 hour, retention 1 month) and MGMT_METRICS_1DAY (granularity 1 day, retention 1 year). But the detailed collected values are kept 7 days in MGMT_METRICS_RAW. This is what I ‘ll query.
All that is in the SYSMAN schema:

SQL> alter session set current_schema=SYSMAN;

The metrics are related to a target and a metric. Let’s find them.

target

First, let’s have a look at all available the targets types in MGMT_TARGETS:

select distinct target_type,type_display_name from mgmt_targets order by 1;

I want to see ASM metrics for my RAC cluster. The display name ‘Cluster ASM’ has the internal type as ‘osm_cluster’ (yes, it was initially called Oracle Storage Management).

Then here are all the targets I have for that target type:

SQL> select target_name,target_type,target_guid from mgmt_targets where target_type='osm_cluster';

In SYSMAN schema, we have GUID identifiers.

metric

Then, for each target type, there is a large number of metrics referenced in MGMT_METRICS:

select distinct target_type,metric_name,metric_label,metric_column,column_label,metric_guid 
from mgmt_metrics
where target_type='osm_cluster' and metric_label like 'Disk Group%'
order by target_type,metric_name,metric_column;

Ok there is a lot of metrics.
If you want more information about them, just go to the Enterprise manager documentation. I’m interrseted about disk group rebalancing and documentation for Disk Group Imbalance Status metrics is here.

collected values

Now let’s put that together and join to MGMT_METRICS_RAW where I’m interested in the ‘U90’ diskgroup:

select 
 to_char(collection_timestamp,'dd-mon-yyyy') day,to_char(collection_timestamp,'hh24:mi') hour
 ,metric_label||' - '||column_label label,key_value key,value
from
(select distinct target_name,target_type,target_guid from mgmt_targets where target_type='osm_cluster')
join (
 select distinct 
  target_type,metric_name,metric_label,metric_column,column_label,short_name,metric_guid 
 from mgmt_metrics
) using(target_type)
join mgmt_metrics_raw using(target_guid,metric_guid)
where key_value = 'U90' and collection_timestamp>sysdate-8
order by collection_timestamp desc,metric_label,column_label,key_value
;

I usually get the result from SQL Developer and export it as html. This is what I’ve pasted above. And it’s easy to open it with Excel and get a nice pivot chart from it:

In my case, I was interested by the available free space in my diskgroup disks during the week. A disk has been added on 24-feb 20:00 but the re-balance hanged for 24 hours. The blue area is the minimum free space (among all the disgroup disks – which have the same size) and the grey part is the size of the newly added disk that has to be re-balanced among all disks.

But the goal of this post is only to show how to get collected statistics:

• identify the target type
• identify the target
• identify the metric
• join that with the raw statistics

I need that very rarely, but it can help to analyze something that happened in the past.