This was first published on https://blog.dbi-services.com/query-the-enterprise-manager-collected-metrics (2015-03-02)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
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;
TARGET_TYPE | TYPE_DISPLAY_NAME |
---|---|
cluster | Cluster |
composite | Group |
has | Oracle High Availability Service |
host | Host |
j2ee_application | Application Deployment |
metadata_repository | Metadata Repository |
microsoft_sqlserver_database | Microsoft SQL Server |
oracle_apache | Oracle HTTP Server |
oracle_beacon | Beacon |
oracle_database | Database Instance |
oracle_dbsys | Database System |
oracle_em_service | EM Service |
oracle_emd | Agent |
oracle_emrep | OMS and Repository |
oracle_emsvrs_sys | EM Servers System |
oracle_home | Oracle Home |
oracle_ias_farm | Oracle Fusion Middleware Farm |
oracle_listener | Listener |
oracle_oms | Oracle Management Service |
oracle_oms_console | OMS Console |
oracle_oms_pbs | OMS Platform |
osm_cluster | Cluster ASM |
osm_instance | Automatic Storage Management |
rac_database | Cluster Database |
weblogic_domain | Oracle WebLogic Domain |
weblogic_j2eeserver | Oracle WebLogic Server |
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';
TARGET_NAME | TARGET_TYPE | TARGET_GUID |
---|---|---|
+ASM_xxzhorac1 | osm_cluster | B8A5A42E2F8F6FCF6CF9FEB082B4CD79 |
In SYSMAN schema, we have GUID identifiers.
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;
METRIC_NAME | METRIC_LABEL | COLUMN_LABEL |
---|---|---|
DiskGroup_Target_Component | Disk Group Target Component | |
DiskGroup_Target_Component | Disk Group Target Component | Disk Group Name |
DiskGroup_Target_Component | Disk Group Target Component | Disk Count |
DiskGroup_Usage | Disk Group Usage | |
DiskGroup_Usage | Disk Group Usage | Disk Group Name |
DiskGroup_Usage | Disk Group Usage | Disk Group Free (MB) |
DiskGroup_Usage | Disk Group Usage | Disk Group Used % |
DiskGroup_Usage | Disk Group Usage | Used % of Safely Usable |
DiskGroup_Usage | Disk Group Usage | Size (MB) |
DiskGroup_Usage | Disk Group Usage | Redundancy |
DiskGroup_Usage | Disk Group Usage | Disk Group Usable Free (MB) |
DiskGroup_Usage | Disk Group Usage | Disk Group Usable (MB) |
asm_diskgroup | Disk Groups | |
asm_diskgroup | Disk Groups | Allocation Unit Size (MB) |
asm_diskgroup | Disk Groups | Disk Count |
asm_diskgroup | Disk Groups | Disk Group |
asm_diskgroup | Disk Groups | Redundancy |
asm_diskgroup | Disk Groups | Size (GB) |
asm_diskgroup | Disk Groups | Contains Voting Files |
asm_diskgroup_attribute | Disk Group Attributes | |
asm_diskgroup_attribute | Disk Group Attributes | Attribute Name |
asm_diskgroup_attribute | Disk Group Attributes | Disk Group |
asm_diskgroup_attribute | Disk Group Attributes | Value |
diskgroup_imbalance | Disk Group Imbalance Status | |
diskgroup_imbalance | Disk Group Imbalance Status | Disk Group Imbalance (%) without Rebalance |
diskgroup_imbalance | Disk Group Imbalance Status | Disk Maximum Used (%) with Rebalance |
diskgroup_imbalance | Disk Group Imbalance Status | Disk Minimum Free (%) without Rebalance |
diskgroup_imbalance | Disk Group Imbalance Status | Disk Count |
diskgroup_imbalance | Disk Group Imbalance Status | Disk Group |
diskgroup_imbalance | Disk Group Imbalance Status | Actual Imbalance (%) |
diskgroup_imbalance | Disk Group Imbalance Status | Actual Minimum Percent Free |
diskgroup_imbalance | Disk Group Imbalance Status | Rebalance In Progress |
diskgroup_imbalance | Disk Group Imbalance Status | Disk Size Variance (%) |
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.
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 ;
DAY | HOUR | LABEL | KEY | VALUE |
---|---|---|---|---|
02-mar-2015 | 15:43 | Disk Group Usage – Disk Group Free (MB) | U90 | 939137 |
02-mar-2015 | 15:43 | Disk Group Usage – Disk Group Usable (MB) | U90 | 1279980 |
02-mar-2015 | 15:43 | Disk Group Usage – Disk Group Usable Free (MB) | U90 | 939137 |
02-mar-2015 | 15:43 | Disk Group Usage – Disk Group Used % | U90 | 26.629 |
02-mar-2015 | 15:43 | Disk Group Usage – Size (MB) | U90 | 1279980 |
02-mar-2015 | 15:43 | Disk Group Usage – Used % of Safely Usable | U90 | 26.629 |
02-mar-2015 | 15:39 | Disk Group Imbalance Status – Actual Imbalance (%) | U90 | 0.164381953 |
… |
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:
I need that very rarely, but it can help to analyze something that happened in the past.
This article was mind blowing. I had no idea SYSMAN was collecting all this data. I have been tasked to display disk group growth over the last six months in order to justify purchasing more disk space. I am hoping to be able to create a spreadsheet based on this data. You have given me hope!
Thanks, Jason
Hi Jason, thanks for your feedback.
Hi,
I want to find out enable component list in oem12c order by users last used. please advise.
Regards, Sharif