Rem
Rem $Header: chronos_rep_coll_pkgbody.sql 22-jun-2005.14:43:04 yxie Exp $
Rem
Rem chronos_rep_coll_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_rep_coll_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    yxie        06/06/05 - changes depends_on to supports_eum_on 
Rem    yxie        04/15/05 - changing contains to depends_on 
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    eporter     12/13/04 - Remove MGMT_RT_REGION_MEMBERS table 
Rem    ramalhot    08/25/04 - cutover to new assoc tables 
Rem    jhsy        08/20/04 - 
Rem    rpinnama    05/16/02 - rpinnama_reorg_rep_scripts_2
Rem    rpinnama    05/16/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY CHRONOS_REPOSITORY_COLLECTIONS
IS

PROCEDURE CHRONOS_REGION_COLLECTION
(
  v_target_guid         IN RAW,
  v_metric_guid		IN RAW,
  v_metric_result       OUT MGMT_METRIC_RESULTS
)
IS
  TYPE MemberTgtThresholds IS TABLE OF NUMBER;
  -- Returns the list of domains that belong to a given region
  -- min_ip is -1 if the region is defined by domain
  CURSOR member_region_domain_cs
  (
    comp_target_guid mgmt_targets.target_guid%TYPE,
    in_region_name mgmt_rt_regions.region_name%TYPE
  )
  IS
    SELECT e.domain
      FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping m, 
           mgmt_rt_regions r
      WHERE r.target_guid = comp_target_guid
        AND r.region_name = in_region_name
        AND m.region_guid = r.region_guid
        AND m.id = e.id
        AND e.min_ip < 0;

  -- Returns the list of subnets that belong to a given region
  -- min_ip is >= 0 if the region is defined by subnet
  CURSOR member_region_subnet_cs
  (
    comp_target_guid mgmt_targets.target_guid%TYPE,
    in_region_name mgmt_rt_regions.region_name%TYPE
  )
  IS
    SELECT e.domain
      FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping m, 
           mgmt_rt_regions r
      WHERE r.target_guid = comp_target_guid
        AND r.region_name = in_region_name
        AND m.region_guid = r.region_guid
        AND m.id = e.id
        AND e.min_ip >= 0;

  -- Returns the sum of all elapsed times, and the count of entries processed
  -- (hits) for a given region and a given target, in the time interval 
  -- specified by ]min_time, max_time]
  CURSOR event_data_cs
  (
    this_target_guid mgmt_targets.target_guid%TYPE,
    reg_member_domains MgmtChrnsRegionMembers,
    reg_member_subnets MgmtChrnsRegionMembers,
    max_elapsed_time NUMBER,
    min_time DATE,
    max_time DATE
  )
  IS
    SELECT SUM(r.elapsed_time), COUNT(*)
      FROM mgmt_rt_metrics_raw r
      WHERE r.target_guid = this_target_guid
        AND min_time < r.collection_timestamp
        AND max_time >= r.collection_timestamp
        AND r.elapsed_time < max_elapsed_time
        AND 
          (
            r.visitor_domain IN 
            (
              SELECT *
                FROM TABLE(CAST(reg_member_domains AS MgmtChrnsRegionMembers))
            )
          OR
            SUBSTR(r.visitor_ip, 1, INSTR(r.visitor_ip, '.', -1, 1) - 1) IN
            (
              SELECT *
                FROM TABLE(CAST(reg_member_subnets AS MgmtChrnsRegionMembers))
            )
          );

  l_target_name mgmt_targets.target_name%TYPE;
  l_target_type mgmt_targets.target_type%TYPE;
  region_list SMP_EMD_STRING_ARRAY;
  evaluation_time DATE;
  last_eval_time DATE;
  last_loaded_time DATE;
  low_water_mark DATE;
  member_tgt_guids MgmtChrnsTgtGuids;
  member_tgt_thresholds MemberTgtThresholds;
  region_member_domains MgmtChrnsRegionMembers;
  region_member_subnets MgmtChrnsRegionMembers;
  frequency_filter NUMBER;
  new_elem_counter NUMBER := 1;
  total_avg NUMBER;
  current_count PLS_INTEGER;
  current_sum PLS_INTEGER;
  region_count PLS_INTEGER;
  region_sum PLS_INTEGER;
  max_window_size NUMBER;
BEGIN
  --DBMS_APPLICATION_INFO.SET_MODULE(
  --  module_name => 'Oracle Enterprise Manager.Eval chronos region events',
  --  action_name => 'start');

  -- Get execution time
  --SELECT SYSTIMESTAMP INTO evaluation_time
    --FROM dual;

  --DBMS_OUTPUT.PUT_LINE('Start chronos region event eval: ' || 
    --to_char(evaluation_time, 'MM/DD/YYYY HH24:MI:SS'));

  -- Get the list region names
  region_list := 
    Emd_Collection.get_key_values_with_thresholds(v_target_guid, v_metric_guid);

  DBMS_OUTPUT.PUT_LINE('region_list.COUNT: ' || region_list.COUNT);
  
  -- Test whether any thresholds defined for this target.
  IF (region_list.COUNT = 0 ) THEN
     RETURN;
  END IF;

  -- Get the composite target's name and type.
  SELECT r.target_name, r.target_type INTO l_target_name, l_target_type
    FROM mgmt_targets r
    WHERE v_target_guid = r.target_guid;

  DBMS_OUTPUT.PUT_LINE('Composite target name/type: ' || l_target_name ||
    '/' || l_target_type);

  -- Get last execution time
  BEGIN
    SELECT rollup_timestamp INTO last_eval_time
      FROM mgmt_target_rollup_times r
      WHERE target_guid = v_target_guid
        AND rollup_table_name = 'REGION_EVENTS_VIRTUAL_TABLE';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      last_eval_time := TO_DATE('01-01-1900', 'DD-MM-YYYY');
  END;

  DBMS_OUTPUT.PUT_LINE('Last execution time: ' || 
    to_char(last_eval_time, 'MM/DD/YYYY HH24:MI:SS'));

  -- Get this targets maximum window size (in minutes)
  BEGIN
    SELECT property_value INTO max_window_size
      FROM mgmt_rt_target_properties
      WHERE property_name = 'mgmt_rt_event_window_sz'
        AND target_guid = v_target_guid;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      SELECT parameter_value INTO max_window_size
        FROM mgmt_parameters
        WHERE parameter_name = 'mgmt_rt_event_window_sz';
  END;

  DBMS_OUTPUT.PUT_LINE('Max window size (minutes): ' || max_window_size);

  -- Get the list of member target guids and their respective 
  -- thresholds (max_elapsed_time).
  SELECT MgmtChrnsTgtGuid(m.target_guid), 
    NVL(p.property_value, n.parameter_value) 
    BULK COLLECT INTO member_tgt_guids, member_tgt_thresholds
    FROM
      (
        SELECT s.target_guid 
          FROM mgmt_targets s, mgmt_target_assocs r, mgmt_targets ct
          WHERE ct.target_name = l_target_name
            AND ct.target_type = l_target_type
            AND r.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid
            AND r.source_target_guid = ct.target_guid
            AND r.assoc_target_guid = s.target_guid
      ) m,
      mgmt_rt_target_properties p,
      mgmt_parameters n
    WHERE p.target_guid (+) = m.target_guid
      AND p.property_name (+) = 'mgmt_rt_max_elapsed_time'
      AND n.parameter_name = 'mgmt_rt_max_elapsed_time';

  DBMS_OUTPUT.PUT_LINE('Number of member targets: ' || 
    member_tgt_guids.COUNT);

  -- Get the collection timestamp of last loaded data 
  SELECT MAX(r.collection_timestamp) INTO last_loaded_time
    FROM mgmt_rt_metrics_raw r
    WHERE r.target_guid IN 
      (
        SELECT target_guid
        FROM TABLE(CAST(member_tgt_guids AS MgmtChrnsTgtGuids)) q
      );

  DBMS_OUTPUT.PUT_LINE('Collection timestamp of last loaded data: ' || 
    to_char(last_loaded_time, 'MM/DD/YYYY HH24:MI:SS'));

  -- Since we are using MAX, no exceptions are raised when no data is found
  -- instead, a NULL value is returned
  IF (last_loaded_time IS NULL) OR (last_loaded_time <= last_eval_time) THEN
    -- No data so far or no new data, so there is nothing to eval!
    RETURN;
  END IF;

  -- Find out the low water mark
  low_water_mark := last_loaded_time - max_window_size/(24*60);

  DBMS_OUTPUT.PUT_LINE('Low water mark: ' ||
    to_char(low_water_mark, 'MM/DD/YYYY HH24:MI:SS'));

  -- Set the low water mark to the last evaluation time if that is yonger 
  -- then the low water mark
  IF (low_water_mark < last_eval_time) THEN
    low_water_mark := last_eval_time;
  END IF;

  -- Get the frequency filter for this composite target
  BEGIN
    SELECT property_value INTO frequency_filter
      FROM mgmt_rt_target_properties r
      WHERE r.target_guid = v_target_guid
        AND r.property_name = 'mgmt_rt_min_hits';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      SELECT r.parameter_value INTO frequency_filter
        FROM mgmt_parameters r
        WHERE r.parameter_name = 'mgmt_rt_min_hits';
  END;

  DBMS_OUTPUT.PUT_LINE('Frequency filter: ' || frequency_filter);


  -- Initialize v_metric_result to it's final MAX size
  v_metric_result := MGMT_METRIC_RESULTS();
  v_metric_result.EXTEND(region_list.COUNT);

  -- Loop on all regions
  FOR i IN 1..region_list.COUNT LOOP

    DBMS_OUTPUT.PUT_LINE('Region: ' || region_list(i));

    -- Get this region's member domains
    OPEN member_region_domain_cs(v_target_guid, region_list(i));
    FETCH member_region_domain_cs BULK COLLECT INTO region_member_domains;
    CLOSE member_region_domain_cs;
    
    DBMS_OUTPUT.PUT_LINE('# member domains: ' || region_member_domains.COUNT);

    -- Get this regions member subnets
    OPEN member_region_subnet_cs(v_target_guid, region_list(i));
    FETCH member_region_subnet_cs BULK COLLECT INTO region_member_subnets;
    CLOSE member_region_subnet_cs;

    DBMS_OUTPUT.PUT_LINE('# member subnets: ' || region_member_subnets.COUNT);

    -- Intialize necessary variables
    region_count := 0;
    region_sum := 0;

    -- Collect the latency for each target at a time (because of filters)
    FOR j IN 1..member_tgt_guids.COUNT LOOP

      DBMS_OUTPUT.PUT_LINE('Tgt guid: ' || member_tgt_guids(j).target_guid);
      DBMS_OUTPUT.PUT_LINE('Target threshold : ' || member_tgt_thresholds(j));

      -- Get the sum and count for this target
      OPEN event_data_cs(member_tgt_guids(j).target_guid, 
        region_member_domains, region_member_subnets, 
        member_tgt_thresholds(j), low_water_mark, last_loaded_time);
      FETCH event_data_cs INTO current_sum, current_count;
      CLOSE event_data_cs;

      -- PS: a := a + NULL --> sets a to NULL regardless of a's inital value
      IF(current_count IS NOT NULL) AND (current_sum IS NOT NULL) THEN
        -- Add up the current values with the previously obtained ones
        region_sum := region_sum + current_sum;
        region_count := region_count + current_count;
      END IF;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('region_count: ' || region_count);
    DBMS_OUTPUT.PUT_LINE('region_sum: ' || region_sum);

    -- Check for the frequency filter (force > 0 for data integrity assurance)
    IF (region_count > frequency_filter) AND (region_count > 0) THEN

      -- calculate total_avg
      total_avg := ROUND(region_sum/(region_count * 1000), 3);

      DBMS_OUTPUT.PUT_LINE('The total average in seconds for the region ' ||
        region_list(i)|| ' was: ' || total_avg);
  
      v_metric_result(new_elem_counter) := 
        MGMT_METRIC_RESULT('LATENCY', region_list(i), total_avg);

      new_elem_counter := new_elem_counter + 1;

    END IF;

  END LOOP;

  -- Trim the excess of elements
  v_metric_result.TRIM(v_metric_result.COUNT - new_elem_counter + 1);

  DBMS_OUTPUT.PUT_LINE('Total count is now ' || v_metric_result.COUNT);

  UPDATE mgmt_target_rollup_times r
    SET r.rollup_timestamp = last_loaded_time
    WHERE r.target_guid = v_target_guid
      AND r.rollup_table_name = 'REGION_EVENTS_VIRTUAL_TABLE';

  DBMS_OUTPUT.PUT_LINE('Updated last evaluation time in table.');

  -- If no rows were updated, then insert one row
  IF (SQL%ROWCOUNT = 0) THEN
    DBMS_OUTPUT.PUT_LINE('Inserting last_loaded_time into table.');

    INSERT INTO mgmt_target_rollup_times r
      (target_guid, rollup_table_name, rollup_timestamp)
      VALUES 
        (v_target_guid, 'REGION_EVENTS_VIRTUAL_TABLE', last_loaded_time);
  END IF;

END CHRONOS_REGION_COLLECTION;

END CHRONOS_REPOSITORY_COLLECTIONS;
/
show errors;