{"id":4385,"date":"2015-03-02T12:10:37","date_gmt":"2015-03-02T11:10:37","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/query-the-enterprise-manager-collected-metrics\/"},"modified":"2015-03-02T12:10:37","modified_gmt":"2015-03-02T11:10:37","slug":"query-the-enterprise-manager-collected-metrics","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/query-the-enterprise-manager-collected-metrics\/","title":{"rendered":"Query the Enterprise Manager collected metrics"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nEnterprise 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.
\nEnterprise 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.
\nAll that is in the SYSMAN schema:<\/p>\n

SQL> alter session set current_schema=SYSMAN;<\/code><\/pre>\n
The metrics are related to a target and a metric. Let’s find them.<\/p>\n
target<\/h3>\n
First, let’s have a look at all available the targets types in MGMT_TARGETS:<\/p>\n
select distinct target_type,type_display_name from mgmt_targets order by 1;<\/code><\/pre>\n
<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
TARGET_TYPE<\/th>\nTYPE_DISPLAY_NAME<\/th>\n<\/tr>\n<\/tbody>\n
cluster<\/td>\nCluster<\/td>\n<\/tr>\n
composite<\/td>\nGroup<\/td>\n<\/tr>\n
has<\/td>\nOracle High Availability Service<\/td>\n<\/tr>\n
host<\/td>\nHost<\/td>\n<\/tr>\n
j2ee_application<\/td>\nApplication Deployment<\/td>\n<\/tr>\n
metadata_repository<\/td>\nMetadata Repository<\/td>\n<\/tr>\n
microsoft_sqlserver_database<\/td>\nMicrosoft SQL Server<\/td>\n<\/tr>\n
oracle_apache<\/td>\nOracle HTTP Server<\/td>\n<\/tr>\n
oracle_beacon<\/td>\nBeacon<\/td>\n<\/tr>\n
oracle_database<\/td>\nDatabase Instance<\/td>\n<\/tr>\n
oracle_dbsys<\/td>\nDatabase System<\/td>\n<\/tr>\n
oracle_em_service<\/td>\nEM Service<\/td>\n<\/tr>\n
oracle_emd<\/td>\nAgent<\/td>\n<\/tr>\n
oracle_emrep<\/td>\nOMS and Repository<\/td>\n<\/tr>\n
oracle_emsvrs_sys<\/td>\nEM Servers System<\/td>\n<\/tr>\n
oracle_home<\/td>\nOracle Home<\/td>\n<\/tr>\n
oracle_ias_farm<\/td>\nOracle Fusion Middleware Farm<\/td>\n<\/tr>\n
oracle_listener<\/td>\nListener<\/td>\n<\/tr>\n
oracle_oms<\/td>\nOracle Management Service<\/td>\n<\/tr>\n
oracle_oms_console<\/td>\nOMS Console<\/td>\n<\/tr>\n
oracle_oms_pbs<\/td>\nOMS Platform<\/td>\n<\/tr>\n
osm_cluster<\/td>\nCluster ASM<\/strong><\/td>\n<\/tr>\n
osm_instance<\/td>\nAutomatic Storage Management<\/td>\n<\/tr>\n
rac_database<\/td>\nCluster Database<\/td>\n<\/tr>\n
weblogic_domain<\/td>\nOracle WebLogic Domain<\/td>\n<\/tr>\n
weblogic_j2eeserver<\/td>\nOracle WebLogic Server<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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).<\/p>\n
Then here are all the targets I have for that target type:<\/p>\n
SQL> select target_name,target_type,target_guid from mgmt_targets where target_type='osm_cluster';<\/code><\/pre>\n\n\n\n\n\n
TARGET_NAME<\/th>\nTARGET_TYPE<\/th>\nTARGET_GUID<\/th>\n<\/tr>\n<\/tbody>\n
+ASM_xxzhorac1<\/td>\nosm_cluster<\/td>\nB8A5A42E2F8F6FCF6CF9FEB082B4CD79<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
In SYSMAN schema, we have GUID identifiers.<\/p>\n
metric<\/h3>\n
Then, for each target type, there is a large number of metrics referenced in MGMT_METRICS:<\/p>\n
select distinct target_type,metric_name,metric_label,metric_column,column_label,metric_guid \nfrom mgmt_metrics\nwhere target_type='osm_cluster' and metric_label like 'Disk Group%'\norder by target_type,metric_name,metric_column;<\/code><\/pre>\n
 <\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
METRIC_NAME<\/th>\nMETRIC_LABEL<\/th>\nCOLUMN_LABEL<\/th>\n<\/tr>\n<\/tbody>\n
DiskGroup_Target_Component<\/td>\nDisk Group Target Component<\/td>\n<\/td>\n<\/tr>\n
DiskGroup_Target_Component<\/td>\nDisk Group Target Component<\/td>\nDisk Group Name<\/td>\n<\/tr>\n
DiskGroup_Target_Component<\/td>\nDisk Group Target Component<\/td>\nDisk Count<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\n<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\nDisk Group Name<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\nDisk Group Free (MB)<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\nDisk Group Used %<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\nUsed % of Safely Usable<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\nSize (MB)<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\nRedundancy<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\nDisk Group Usable Free (MB)<\/td>\n<\/tr>\n
DiskGroup_Usage<\/td>\nDisk Group Usage<\/td>\nDisk Group Usable (MB)<\/td>\n<\/tr>\n
asm_diskgroup<\/td>\nDisk Groups<\/td>\n<\/td>\n<\/tr>\n
asm_diskgroup<\/td>\nDisk Groups<\/td>\nAllocation Unit Size (MB)<\/td>\n<\/tr>\n
asm_diskgroup<\/td>\nDisk Groups<\/td>\nDisk Count<\/td>\n<\/tr>\n
asm_diskgroup<\/td>\nDisk Groups<\/td>\nDisk Group<\/td>\n<\/tr>\n
asm_diskgroup<\/td>\nDisk Groups<\/td>\nRedundancy<\/td>\n<\/tr>\n
asm_diskgroup<\/td>\nDisk Groups<\/td>\nSize (GB)<\/td>\n<\/tr>\n
asm_diskgroup<\/td>\nDisk Groups<\/td>\nContains Voting Files<\/td>\n<\/tr>\n
asm_diskgroup_attribute<\/td>\nDisk Group Attributes<\/td>\n<\/td>\n<\/tr>\n
asm_diskgroup_attribute<\/td>\nDisk Group Attributes<\/td>\nAttribute Name<\/td>\n<\/tr>\n
asm_diskgroup_attribute<\/td>\nDisk Group Attributes<\/td>\nDisk Group<\/td>\n<\/tr>\n
asm_diskgroup_attribute<\/td>\nDisk Group Attributes<\/td>\nValue<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\n<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nDisk Group Imbalance (%) without Rebalance<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nDisk Maximum Used (%) with Rebalance<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nDisk Minimum Free (%) without Rebalance<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nDisk Count<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nDisk Group<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nActual Imbalance (%)<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nActual Minimum Percent Free<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nRebalance In Progress<\/td>\n<\/tr>\n
diskgroup_imbalance<\/td>\nDisk Group Imbalance Status<\/td>\nDisk Size Variance (%)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Ok there is a lot of metrics.
\nIf 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<\/a>.<\/p>\n
collected values<\/h3>\n
Now let’s put that together and join to MGMT_METRICS_RAW where I’m interested in the ‘U90’ diskgroup:<\/p>\n
select \n to_char(collection_timestamp,'dd-mon-yyyy') day,to_char(collection_timestamp,'hh24:mi') hour\n ,metric_label||' - '||column_label label,key_value key,value\nfrom\n(select distinct target_name,target_type,target_guid from mgmt_targets where target_type='osm_cluster')\njoin (\n select distinct \n  target_type,metric_name,metric_label,metric_column,column_label,short_name,metric_guid \n from mgmt_metrics\n) using(target_type)\njoin mgmt_metrics_raw using(target_guid,metric_guid)\nwhere key_value = 'U90' and collection_timestamp>sysdate-8\norder by collection_timestamp desc,metric_label,column_label,key_value\n;<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n
DAY<\/th>\nHOUR<\/th>\nLABEL<\/th>\nKEY<\/th>\nVALUE<\/th>\n<\/tr>\n<\/tbody>\n
02-mar-2015<\/td>\n15:43<\/td>\nDisk Group Usage – Disk Group Free (MB)<\/td>\nU90<\/td>\n939137<\/td>\n<\/tr>\n
02-mar-2015<\/td>\n15:43<\/td>\nDisk Group Usage – Disk Group Usable (MB)<\/td>\nU90<\/td>\n1279980<\/td>\n<\/tr>\n
02-mar-2015<\/td>\n15:43<\/td>\nDisk Group Usage – Disk Group Usable Free (MB)<\/td>\nU90<\/td>\n939137<\/td>\n<\/tr>\n
02-mar-2015<\/td>\n15:43<\/td>\nDisk Group Usage – Disk Group Used %<\/td>\nU90<\/td>\n26.629<\/td>\n<\/tr>\n
02-mar-2015<\/td>\n15:43<\/td>\nDisk Group Usage – Size (MB)<\/td>\nU90<\/td>\n1279980<\/td>\n<\/tr>\n
02-mar-2015<\/td>\n15:43<\/td>\nDisk Group Usage – Used % of Safely Usable<\/td>\nU90<\/td>\n26.629<\/td>\n<\/tr>\n
02-mar-2015<\/td>\n15:39<\/td>\nDisk Group Imbalance Status – Actual Imbalance (%)<\/td>\nU90<\/td>\n0.164381953<\/td>\n<\/tr>\n
…<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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:<\/p>\n
\"b2ap3_thumbnail_CaptureEM-Metrics.JPG\"<\/a><\/p>\n
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.<\/p>\n
But the goal of this post is only to show how to get collected statistics:<\/p>\n