Rem Rem $Header: group_eval_pkgbody.sql 15-jul-2005.11:57:28 gan Exp $ Rem Rem group_eval_pkgbody.sql Rem Rem Copyright (c) 2002, 2005, Oracle. All rights reserved. Rem Rem NAME Rem group_eval_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem gan 07/15/05 - bug 4175788 Rem rpatti 03/15/05 - remove references to comp_target_def table Rem ramalhot 01/17/05 - g_member_guid->g_contains_guid Rem rpatti 10/25/04 - fix Rem rpatti 10/15/04 - support the new summary metrics Rem ramalhot 08/27/04 - cutover to new assoc tables Rem rpinnama 05/16/02 - rpinnama_reorg_rep_scripts_2 Rem rpinnama 05/16/02 - Created Rem ---------------------------- -- PACKAGE IMPLEMENTATION -- ---------------------------- CREATE OR REPLACE PACKAGE BODY EMD_COMPOSITE_TARGETS IS -- PROCEDURE EVALUATE_SUMMARY_METRICS ( v_target_guid IN RAW, v_metric_guid IN RAW, v_coll_name IN VARCHAR2, v_metric_result OUT MGMT_METRIC_VALUE_ARRAY ) IS avg_value NUMBER; min_value NUMBER; max_value NUMBER; sum_value NUMBER; stdev_value NUMBER; l_avg_name_val_array MGMT_NAMEVALUE_ARRAY := MGMT_NAMEVALUE_ARRAY(); l_min_name_val_array MGMT_NAMEVALUE_ARRAY := MGMT_NAMEVALUE_ARRAY(); l_max_name_val_array MGMT_NAMEVALUE_ARRAY := MGMT_NAMEVALUE_ARRAY(); l_sum_name_val_array MGMT_NAMEVALUE_ARRAY := MGMT_NAMEVALUE_ARRAY(); l_stdev_name_val_array MGMT_NAMEVALUE_ARRAY := MGMT_NAMEVALUE_ARRAY(); l_key_value MGMT_METRICS_RAW.key_value%TYPE; l_column_metric_guid MGMT_METRICS.metric_guid%TYPE; l_last_date MGMT_CURRENT_METRICS.collection_timestamp%TYPE; l_max_dep_date MGMT_CURRENT_METRICS.collection_timestamp%TYPE; l_group_timezone MGMT_TARGETS.timezone_region%TYPE; CURSOR performance_stats( v_target_guid RAW, v_target_timezone MGMT_TARGETS.timezone_region%TYPE, v_metric_guid RAW) IS SELECT AVG(c.value) AS avg_value, MIN(c.value) AS min_value, MAX(c.value) AS max_value, SUM(c.value) AS sum_value, STDDEV(c.value) AS stdev_value, MAX(mgmt_global.adjust_tz( c.collection_timestamp, t.timezone_region, v_target_timezone)) AS max_coll_time FROM mgmt_current_metrics c, ( SELECT tgt.target_guid, tgt.timezone_region FROM mgmt_targets tgt, mgmt_target_assocs mem, mgmt_current_availability a WHERE mem.source_target_guid = v_target_guid AND mem.assoc_guid = MGMT_ASSOC.g_contains_guid AND mem.assoc_target_guid = tgt.target_guid AND tgt.target_guid = a.target_guid AND a.current_status = mgmt_global.G_STATUS_UP ) t WHERE c.target_guid = t.target_guid AND c.metric_guid = v_metric_guid; l_mem_metric_guid mgmt_metrics.metric_guid%TYPE := NULL; l_value mgmt_coll_item_properties.property_value%TYPE := NULL; BEGIN SELECT timezone_region INTO l_group_timezone FROM MGMT_TARGETS WHERE target_guid = v_target_guid; SELECT UNIQUE cm.metric_guid INTO l_column_metric_guid FROM mgmt_metrics tm, mgmt_metrics cm WHERE tm.metric_guid = v_metric_guid AND tm.target_type = cm.target_type AND tm.metric_name = cm.metric_name AND cm.metric_column = em_groups_ui.G_SUM_METRIC_COLUMN; l_value := EM_COLL_UTIL.get_coll_item_property( p_object_guid => v_target_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_coll_name => v_coll_name, p_metric_guid => v_metric_guid, p_property_name => EM_GROUPS_UI.G_MEM_METRIC_GUID_PROP_NAME); l_mem_metric_guid := HEXTORAW(l_value); l_key_value := em_groups_ui.get_avg_metric_label(l_mem_metric_guid); -- assume all key (avg, min, max, std) are evaluate at the same time BEGIN SELECT max(collection_timestamp) INTO l_last_date FROM mgmt_current_metrics WHERE target_guid = v_target_guid AND metric_guid = l_column_metric_guid AND key_value = l_key_value; EXCEPTION WHEN NO_DATA_FOUND THEN l_last_date := NULL; END; v_metric_result := NULL; OPEN performance_stats(v_target_guid, l_group_timezone, l_mem_metric_guid); LOOP FETCH performance_stats INTO avg_value, min_value, max_value, sum_value, stdev_value, l_max_dep_date; EXIT WHEN performance_stats%NOTFOUND; IF l_max_dep_date IS NULL OR (l_last_date IS NOT NULL AND l_max_dep_date <= l_last_date) THEN EXIT; END IF; IF ( avg_value IS NOT NULL AND min_value IS NOT NULL AND max_value IS NOT NULL AND sum_value IS NOT NULL AND stdev_value IS NOT NULL) THEN v_metric_result := MGMT_METRIC_VALUE_ARRAY(); v_metric_result.EXTEND(5); l_avg_name_val_array := MGMT_NAMEVALUE_ARRAY(); l_avg_name_val_array.extend(2); l_key_value := em_groups_ui.get_avg_metric_label(l_mem_metric_guid); l_avg_name_val_array(1) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_KEY, p_value => l_key_value ); l_avg_name_val_array(2) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_COLUMN, p_value => avg_value ); l_min_name_val_array := MGMT_NAMEVALUE_ARRAY(); l_min_name_val_array.extend(2); l_key_value := em_groups_ui.get_min_metric_label(l_mem_metric_guid); l_min_name_val_array(1) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_KEY, p_value => l_key_value ); l_min_name_val_array(2) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_COLUMN, p_value => min_value ); l_max_name_val_array := MGMT_NAMEVALUE_ARRAY(); l_max_name_val_array.extend(2); l_key_value := em_groups_ui.get_max_metric_label(l_mem_metric_guid); l_max_name_val_array(1) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_KEY, p_value => l_key_value ); l_max_name_val_array(2) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_COLUMN, p_value => max_value ); l_sum_name_val_array := MGMT_NAMEVALUE_ARRAY(); l_sum_name_val_array.extend(2); l_key_value := em_groups_ui.get_sum_metric_label(l_mem_metric_guid); l_sum_name_val_array(1) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_KEY, p_value => l_key_value ); l_sum_name_val_array(2) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_COLUMN, p_value => sum_value ); l_stdev_name_val_array := MGMT_NAMEVALUE_ARRAY(); l_stdev_name_val_array.extend(2); l_key_value := em_groups_ui.get_stdev_metric_label(l_mem_metric_guid); l_stdev_name_val_array(1) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_KEY, p_value => l_key_value ); l_stdev_name_val_array(2) := MGMT_NAMEVALUE_OBJ.NEW( p_name => em_groups_ui.G_SUM_METRIC_COLUMN, p_value => stdev_value ); v_metric_result(1) := MGMT_METRIC_VALUE_OBJ.new( p_target_guid => v_target_guid, p_status => 0, p_status_message => NULL, p_metric_values => l_avg_name_val_array, p_collection_timestamp => l_max_dep_date); v_metric_result(2) := MGMT_METRIC_VALUE_OBJ.new( p_target_guid => v_target_guid, p_status => 0, p_status_message => NULL, p_metric_values => l_min_name_val_array, p_collection_timestamp => l_max_dep_date); v_metric_result(3) := MGMT_METRIC_VALUE_OBJ.new( p_target_guid => v_target_guid, p_status => 0, p_status_message => NULL, p_metric_values => l_max_name_val_array, p_collection_timestamp => l_max_dep_date); v_metric_result(4) := MGMT_METRIC_VALUE_OBJ.new( p_target_guid => v_target_guid, p_status => 0, p_status_message => NULL, p_metric_values => l_sum_name_val_array, p_collection_timestamp => l_max_dep_date); v_metric_result(5) := MGMT_METRIC_VALUE_OBJ.new( p_target_guid => v_target_guid, p_status => 0, p_status_message => NULL, p_metric_values => l_stdev_name_val_array, p_collection_timestamp => l_max_dep_date); END IF; END LOOP; CLOSE performance_stats; dbms_output.put_line( 'EVALUATE_SUMMARY_METRICS:OUT()'); EXCEPTION WHEN others THEN -- close any open cursors before proceeding. IF performance_stats%ISOPEN THEN CLOSE performance_stats; END IF; raise; END EVALUATE_SUMMARY_METRICS; END EMD_COMPOSITE_TARGETS; / show errors;