Rem Rem $Header: bslni_pkgbody.sql 09-dec-2005.02:28:10 jsadras Exp $ Rem Rem bslni_pkgbody.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem bslni_pkgbody.sql - implementation of baselines internal package Rem Rem DESCRIPTION Rem The baselines internal package isolates deployment-specific code Rem (DB Control or EM Grid Control) away from mgmt_bsln package Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem jsadras 12/09/05 - Bug:4691227: Handle exceptions for procedures Rem called from dbms_jobs Rem jberesni 08/10/05 - fix target_delete_callback Rem jberesni 08/05/05 - fix 4522784 & 4536980 Rem jberesni 07/22/05 - add emdw_log.debug calls Rem jberesni 07/21/05 - add final commit and exception handler to purge_extract Rem jberesni 07/20/05 - new set_all_thresholds_EM Rem scgrover 07/07/05 - add extended sql trace Rem jberesni 06/03/05 - beacon keyval delete callback Rem jberesni 05/13/05 - add metric test fcns Rem jberesni 05/12/05 - add target_delete_callback Rem jberesni 04/16/05 - merge from DB control Rem jberesni 03/22/05 - expose job defs for logging Rem jberesni 03/05/05 - sync with DB control Rem jberesni 12/30/04 - prevent_override flag Rem jberesni 11/19/04 - sho err Rem jberesni 11/18/04 - 9.2 compatibility Rem jberesni 11/18/04 - refactor Rem jberesni 10/14/04 - fix 3952552 Rem jberesni 09/28/04 - migrate from DB control Rem jberesni 09/23/04 - fix 3910279 Rem jberesni 08/10/04 - daynight Rem jberesni 08/01/04 - compute_all and set_all Rem jberesni 07/28/04 - restructure Rem jberesni 07/23/04 - misc fixes Rem jsoule 07/21/04 - fix set thresholds Rem jberesni 07/15/04 - candidate1 Rem jsoule 05/24/04 - extraction Rem jsoule 05/19/04 - add exceptions Rem jsoule 05/18/04 - dynamic sql Rem jsoule 05/17/04 - update Rem jsoule 05/11/04 - Created Rem create or replace package body mgmt_bsln_internal ----------------------------- -- Grid Control deployment 0 ----------------------------- as ----------------------------------------------------------------------------- -- -- package-private constants -- ----------------------------------------------------------------------------- -- NOTE: job definition constants made public for logging ---------------------------------------------------------------------------- ----------------------------------------------------------------------------- -- -- package-private global variables -- ----------------------------------------------------------------------------- G_RAWDATA_RETENTION integer := 91; ----------------------------------------------------------------------------- -- -- package-private types and subtypes -- ----------------------------------------------------------------------------- subtype module_name_t is varchar2(30); subtype alert_threshold_t is mgmt_bsln.alert_threshold_t; subtype THR_rectype is MGMT_BSLN.THR_rectype; subtype server_alert_op_t is binary_integer; type server_alert_rectype is record (instance_name mgmt_bsln_datasources.instance_name%type ,metric_id mgmt_bsln_datasources.metric_id%type ,warning_operator server_alert_op_t ,warning_value alert_threshold_t ,critical_operator server_alert_op_t ,critical_value alert_threshold_t ,observation_period binary_integer ,consecutive_occurrences binary_integer ); ------------------------------------------------- -- forward declare assert ------------------------------------------------- PROCEDURE assert (bool_IN IN BOOLEAN ,msg_IN IN VARCHAR2 := null); ------------------------------------------------- ------------------------------------------------- -- -- cursor definitions by function/procedure -- ---------------------------------------------------------------- -- TODO: comments on rectype and cursor var type ---------------------------------------------------------------- type BslnThr_rectype is record (bsln_guid guid_t ,datasource_guid guid_t ,asof_date date ,subinterval_code subinterval_code_t -- sources:EM,DB ,source_type mgmt_bsln_datasources.source_type%TYPE -- EM identifiers ,target_guid guid_t ,metric_guid guid_t ,key_value key_value_t -- DB identifiers ,dbid mgmt_bsln_datasources.dbid%TYPE ,instance_name mgmt_bsln_datasources.instance_name%TYPE ,metric_id mgmt_bsln_datasources.metric_id%TYPE -- bsln threshold parms ,threshold_method mgmt_bsln_threshold_parms.threshold_method%TYPE ,num_occurrences mgmt_bsln_threshold_parms.num_occurrences%TYPE ,warning_param mgmt_bsln_threshold_parms.WARNING_PARAM%TYPE ,critical_param mgmt_bsln_threshold_parms.critical_param%TYPE ,fail_action mgmt_bsln_threshold_parms.fail_action%TYPE -- bsln statistics for subinterval asof_date ,sample_count mgmt_bsln_statistics.sample_count%TYPE ,minimum mgmt_bsln_statistics.minimum%TYPE ,maximum mgmt_bsln_statistics.maximum%TYPE ,pctile_95 mgmt_bsln_statistics.pctile_95%TYPE ,pctile_99 mgmt_bsln_statistics.est_pctile_99%TYPE ,pctile_999 mgmt_bsln_statistics.est_pctile_999%TYPE ,pctile_9999 mgmt_bsln_statistics.est_pctile_9999%TYPE ,est_fit_quality mgmt_bsln_statistics.est_fit_quality%TYPE ,est_sample_count mgmt_bsln_statistics.est_sample_count%TYPE ); type BT_cvtype is REF CURSOR RETURN BslnThr_rectype; procedure open_BT_cvar (BT_cv IN OUT BT_cvtype ,bsln_guid_in in guid_t := null ,ds_guid_in in guid_t := null ,date_in in date := SYSDATE); function BslnThr(bsln_guid_in guid_t ,ds_guid_in guid_t ,date_in date) return BslnThr_rectype; ---------------------------------------------------- -- helper functions to assert valid input types ---------------------------------------------------- function valid_source_type (source_type_in in mgmt_bsln_datasources.source_type%TYPE) return boolean is begin return source_type_in in (mgmt_bsln.K_SOURCE_EM, mgmt_bsln.K_SOURCE_DB); end valid_source_type; function valid_threshold_method (threshold_method_in in threshold_method_t) return boolean is begin return NVL(threshold_method_in, mgmt_bsln.K_METHOD_SIGLVL) in (mgmt_bsln.K_METHOD_SIGLVL,mgmt_bsln.K_METHOD_PCTMAX); end valid_threshold_method; function valid_fail_action (fail_action_in in fail_action_t) return boolean is begin return fail_action_in in (mgmt_bsln.K_FAIL_ACTION_UNSET, mgmt_bsln.K_FAIL_ACTION_PRESERVE); end valid_fail_action; function valid_siglvl_param (siglvl_param_in in param_value_t) return boolean is begin return NVL(siglvl_param_in,mgmt_bsln.K_SIGLVL_95) IN (mgmt_bsln.K_SIGLVL_95, mgmt_bsln.K_SIGLVL_99, mgmt_bsln.K_SIGLVL_999,mgmt_bsln.K_SIGLVL_9999) ; end valid_siglvl_param; ------------------------------------------------------------------------- -- Procedure: compute_load_statsEM -- Description: Computes stats over the target baseline metric data per -- the baseline definition. Created to be called by create_bsln_prvt. -- Arguments: compute_date_in - determines simple moving window interval -- bsln_guid_in - unique baseline identifier ----------------------------------------------------- procedure compute_load_statsEM (compute_date_IN IN date ,bsln_guid_IN IN guid_t ) is myname module_name_t := 'COMPUTE_LOAD_STATSEM'; begin assert(compute_date_in is not null,myname||':compute_date_in not null'); assert(bsln_guid_in is not null,myname||':bsln_guid_in not null'); ---------------------------------------------------------- -- clean out old stats for this baseline ---------------------------------------------------------- delete from mgmt_bsln_statistics where bsln_guid = bsln_guid_in; ------------------------------------------- -- compute and insert statistics ------------------------------------------- insert into mgmt_bsln_statistics select XX.* from TABLE(mgmt_bsln.extract_compute_stats (CURSOR( select DS.datasource_guid ,BL.bsln_guid ,BL.subinterval_key ,RD.obs_time as obs_time ,RD.obs_value as obs_value from mgmt_bsln_rawdata RD ,mgmt_bsln_datasources DS ,(select B.bsln_guid ,B.target_uid ,B.subinterval_key ,CASE B.type when 'R' then TRUNC(compute_date_in)-AI.interval_days when 'S' then AI.interval_begin else to_date(null) END as interval_begin ,CASE B.type when 'R' then TRUNC(compute_date_in) when 'S' then AI.interval_end else to_date(null) END as interval_end from mgmt_bsln_baselines B ,mgmt_bsln_intervals AI where AI.bsln_guid = B.bsln_guid and B.bsln_guid = bsln_guid_IN ) BL where RD.datasource_guid = DS.datasource_guid and BL.target_uid = DS.target_uid and RD.obs_time between BL.interval_begin and BL.interval_end ))) XX; end compute_load_statsEM; ------------------------------------------------------------------------ procedure compute_load_stats (compute_date_IN IN date ,bsln_guid_IN IN varchar2 ) is myname module_name_t := 'COMPUTE_LOAD_STATS'; l_bsln_rec mgmt_bsln_baselines%ROWTYPE; begin assert(bsln_guid_IN is not null,myname||':bsln_guid not null'); assert(compute_date_IN is not null, myname||':compute_date_in not null'); --------------------------------------- -- fetch/validate bsln_guid --------------------------------------- l_bsln_rec := mgmt_bsln.baseline_rec(bsln_guid_IN); ---------------------------------- -- call deployment-specific code ---------------------------------- compute_load_statsEM (compute_date_IN => compute_date_IN ,bsln_guid_IN => HEXTORAW(bsln_guid_IN) ); end compute_load_stats; ------------------------------------------------------------------------- -- Procedure: open_BT_cvar -- Description: Open a cursor variable of type BT_cvtype using parameters -- to determine query. -- Arguments: BT_cv - BT_cvtype ref cursor to open. -- bsln_guid_in - unique baseline identifier -- ds_guid_in - unique datasource identifier -- date_in - date for which to find statistics -- Notes: If bsln_guid_in IS NULL then all ACTIVE baselines are -- returned. -- If ds_guid_in IS NULL then only rows for this datasource -- are returned. -- If date_in IS NULL then SYSDATE is used. -------------------------------------------------------------------------- procedure open_BT_cvar (BT_cv IN OUT BT_cvtype ,bsln_guid_in in guid_t := null ,ds_guid_in in guid_t := null ,date_in in date := SYSDATE) is myname module_name_t := 'OPEN_BT_CVAR'; l_date date; begin ------------------------------- -- default to SYSDATE ------------------------------- l_date := NVL(date_in,SYSDATE); --------------------------------------------- -- cursor must return BslnThr_rectype --------------------------------------------- open BT_cv for select BLDS.bsln_guid ,BLDS.datasource_guid ,l_date ,BLDS.subinterval_code -- ,BLDS.source_type -- ,BLDS.target_guid ,BLDS.metric_guid ,BLDS.key_value -- ,BLDS.dbid ,BLDS.instance_name ,BLDS.metric_id -- ,TP.threshold_method ,NVL(TP.num_occurrences,1) as num_occurrences ,TP.warning_param ,TP.critical_param ,TP.fail_action -- ,ST.sample_count ,ROUND(ST.minimum,3) as minimum ,ROUND(ST.maximum,3) as maximum ,ROUND(ST.pctile_95,3) as pctile_95 ,ROUND(ST.est_pctile_99,3) as pctile_99 ,ROUND(ST.est_pctile_999,3) as pctile_999 ,ROUND(ST.est_pctile_9999,3) as pctile_9999 ,est_fit_quality ,est_sample_count from mgmt_bsln_threshold_parms TP ,mgmt_bsln_statistics ST ,(select B.bsln_guid as bsln_guid ,B.target_uid as target_uid ,B.subinterval_key as subinterval_key ,MGMT_BSLN.cached_subinterval_code(B.subinterval_key,l_date) as subinterval_code ,D.datasource_guid as datasource_guid ,D.source_type as source_type ,D.target_guid as target_guid ,D.metric_guid as metric_guid ,D.key_value as key_value ,D.dbid as dbid ,D.instance_name as instance_name ,D.metric_id as metric_id from mgmt_bsln_baselines B ,mgmt_bsln_datasources D where B.status = DECODE(bsln_guid_in,null,'ACTIVE',B.status) and B.bsln_guid = NVL(bsln_guid_in,B.bsln_guid) and B.target_uid = D.target_uid and D.datasource_guid = NVL(ds_guid_in,D.datasource_guid) ) BLDS where BLDS.bsln_guid = TP.bsln_guid and BLDS.datasource_guid = TP.datasource_guid and BLDS.bsln_guid = ST.bsln_guid(+) and BLDS.datasource_guid = ST.datasource_guid(+) and BLDS.subinterval_code = ST.subinterval_code(+); end open_BT_cvar; ------------------------------------------------------------------------- -- Function: BslnThr -- Description: Returns baseline-threshold record for using most recent -- matching stats by bsln subinterval code for the date -- Arguments: bsln_guid_in - baseline definition to use -- ds_guid_in - datasource definition to use -------------------------------------------------------------------------- function BslnThr(bsln_guid_in guid_t ,ds_guid_in guid_t ,date_in date) return BslnThr_rectype is myname module_name_t := 'BSLNTHR'; l_BT_rec BslnThr_rectype; l_BT_rec_toss BslnThr_rectype; l_BT_cv BT_cvtype; begin assert(bsln_guid_in is not null,myname||':bsln_guid_in not null'); assert(ds_guid_in is not null,myname||':ds_guid_in not null'); assert(date_in is not null,myname||':date_in not null'); open_BT_cvar (BT_cv => l_BT_cv ,bsln_guid_in => bsln_guid_in ,ds_guid_in => ds_guid_in ,date_in => date_in ); fetch l_BT_cv into l_BT_rec; -- candidate return ---------------------------------------- -- detect no data or too many rows ---------------------------------------- if l_BT_cv%NOTFOUND then close l_BT_cv; -- call some logging procedure here RAISE_APPLICATION_ERROR(MGMT_BSLN.X_BSLNTHR_ERROR ,'no data found'); else fetch l_BT_cv into l_BT_rec_toss; if l_BT_cv%FOUND then close l_BT_cv; -- call some logging procedure here RAISE_APPLICATION_ERROR(MGMT_BSLN.X_BSLNTHR_ERROR ,'too many rows'); end if; end if; close l_BT_cv; RETURN l_BT_rec; end BslnThr; ------------------------------------------------------------------------- -- Procedure: unset_threshold_DB -- Description: Unsets metric threshold for specific metric on specific -- instance. Private DB deployment-specific implementation -- called by public unset_threshold. -- Arguments: instance_name_in - instance name for metric -- metric_id_in - 10g metric_id for metric -------------------------------------------------------------------------- procedure unset_threshold_DB (instance_name_in in mgmt_bsln_datasources.instance_name%type ,metric_id_in in mgmt_bsln_datasources.metric_id%type) is myname module_name_t := 'UNSET_THRESHOLD_DB'; l_instance_name mgmt_bsln_datasources.instance_name%type; begin assert(instance_name_in is not null, myname||':instance_name_in not null'); assert(metric_id_in is not null, myname||':metric_id_in not null'); -- this procedure a no-op in Grid Control null; end unset_threshold_DB; ------------------------------------------------------------------------- -- Procedure: unset_threshold_EM -- Description: Placeholder for future EM-specific threshold-level -- interaction with agent. Private EM deployment-specific -- implementation called by public unset_threshold. -- Arguments: ds_guid_in - datasource guid for metric unset threshold -------------------------------------------------------------------------- procedure unset_threshold_EM (target_guid_in in guid_t ,metric_guid_in in guid_t ,key_value_in in key_value_t := MGMT_BSLN.K_DEFAULT_KEY_VALUE) is myname module_name_t := 'UNSET_THRESHOLD_EM'; begin assert(target_guid_in is not null,myname||':target_guid_in not null'); assert(metric_guid_in is not null,myname||':metric_guid_in not null'); assert(key_value_in is not null,myname||':key_value_in not null'); ------------------------------------------------------------------- -- currently metric-level set/unset not available in EM deployment ------------------------------------------------------------------- RAISE_APPLICATION_ERROR(mgmt_bsln.X_NOT_SUPPORTED,myname||':NOT SUPPORTED'); end unset_threshold_EM; ------------------------------------------------------------------------- -- Procedure: unset_threshold -- Description: Unsets alert threshold for specific datasource guid. -- Called by identical signature in mgmt_bsln for pass-thru -- to deployment-specific implementation. -- Arguments: ds_guid_in - datasource_guid for which to unset threshold -------------------------------------------------------------------------- procedure unset_threshold (ds_guid_in in guid_t) is myname module_name_t := 'UNSET_THRESHOLD'; l_dsrec mgmt_bsln_datasources%ROWTYPE; begin assert(ds_guid_in is not null, myname||':ds_guid_in not null'); ------------------------------------------- -- validate registered datasource ------------------------------------------- l_dsrec := MGMT_BSLN.DATASOURCE_REC(ds_guid_in); ------------------------------------------- -- call deployment-specific signature ------------------------------------------- if l_dsrec.source_type = MGMT_BSLN.K_SOURCE_DB then unset_threshold_DB(l_dsrec.instance_name, l_dsrec.metric_id); elsif l_dsrec.source_type = MGMT_BSLN.K_SOURCE_EM then unset_threshold_EM (l_dsrec.target_guid, l_dsrec.metric_guid, l_dsrec.key_value); else null; assert(FALSE, myname||':validate deployment:'||l_dsrec.source_type); end if; end unset_threshold; ------------------------------------------------------------------------- -- Procedure: new_threshold_values -- Description: Computes new values for warning and critical thresholds -- given an entire sysmetric thresholds record. -- Arguments: sysmetric_threshold_rec_in - record from sysmetric threshold -- cursor with all necessary info -- warning_value_out - new warning threshold level (OUT) -- critical_value_out - new critical threshold level (OUT) -- Notes: -------------------------------------------------------------------------- procedure new_threshold_values (BT_rec_in in BslnThr_rectype ,warning_value_inout in out varchar2 ,critical_value_inout in out varchar2 ) is myname module_name_t := 'NEW_THRESHOLD_VALUES'; THR_rec THR_rectype; begin --------------------------------------------------- -- assemble THR_rec to pass to new_threshold_value --------------------------------------------------- THR_rec.threshold_method := BT_rec_in.threshold_method; THR_rec.num_occurrences := BT_rec_in.num_occurrences; THR_rec.warning_param := BT_rec_in.warning_param; THR_rec.critical_param := BT_rec_in.critical_param; THR_rec.fail_action := BT_rec_in.fail_action; THR_rec.sample_count := BT_rec_in.sample_count; THR_rec.minval := BT_rec_in.minimum; THR_rec.maxval := BT_rec_in.maximum; THR_rec.pctile_95 := BT_rec_in.pctile_95; THR_rec.pctile_99 := BT_rec_in.pctile_99; THR_rec.pctile_999 := BT_rec_in.pctile_999; THR_rec.pctile_9999 := BT_rec_in.pctile_9999; THR_rec.est_fit_quality := BT_rec_in.est_fit_quality; THR_rec.est_sample_count := BT_rec_in.est_sample_count; ------------------------------------------------------ -- call new_threshold_value for warning and critical -- or set values to null if param is null ------------------------------------------------------ if THR_rec.warning_param is not null then MGMT_BSLN.new_threshold_value(THR_rec, THR_rec.warning_param, warning_value_inout); else warning_value_inout := null; end if; if THR_rec.critical_param is not null then MGMT_BSLN.new_threshold_value(THR_rec, THR_rec.critical_param, critical_value_inout); else critical_value_inout := null; end if; end new_threshold_values; ------------------------------------------------------------------------- -- Procedure: set_threshold_EM -- Description: Placeholder for future EM-specific threshold-level -- interaction with agent. Private EM deployment-specific -- implementation called by public unset_threshold. -- Arguments: target_guid_in - -- metric_guid_in - -- key_value_in - -- warning_threshold_in - -- critical_threshold_in - -- num_occurs_in - -------------------------------------------------------------------------- procedure set_threshold_EM (BT_rec_in in BslnThr_rectype) is myname module_name_t := 'SET_THRESHOLD_EM'; begin RAISE_APPLICATION_ERROR(mgmt_bsln.X_NOT_SUPPORTED,myname||':NOT SUPPORTED'); end set_threshold_EM; ------------------------------------------------------------------------- -- Procedure: set_threshold_DB -- Description: Sets threshold for DB-specific Oracle 10g server generated -- alerts. DB-specific implementation called by public -- unset_threshold procedure. -- Arguments: BT_rec_in - record in shape of baseline_threshold_cur -- Notes: -------------------------------------------------------------------------- procedure set_threshold_DB (BT_rec_in in BslnThr_rectype) is myname module_name_t := 'SET_THRESHOLD_DB'; begin assert(FALSE,myname||':'||'should not be called in Grid Control'); -- this procedure a no-op in Grid Control null; end set_threshold_DB; ------------------------------------------------------------------------- -- Procedure: set_threshold -- Description: sets alert threshold for specific datasource guid. -- Arguments: BT_rec_in - record of baseline_thresholds_cur%ROWTYPE -------------------------------------------------------------------------- procedure set_threshold (BT_rec_in in BslnThr_rectype) is myname module_name_t := 'SET_THRESHOLD'; begin assert(BT_rec_in.datasource_guid is not null, myname||':BT_rec_in not null'); -------------------------------------------------------------- -- call deployment-specific module -------------------------------------------------------------- if BT_rec_in.source_type = MGMT_BSLN.K_SOURCE_DB then set_threshold_DB (BT_rec_in); elsif BT_rec_in.source_type = MGMT_BSLN.K_SOURCE_EM then set_threshold_EM (BT_rec_in); else null; assert(FALSE, myname||':valid deployment:'||BT_rec_in.source_type); end if; end set_threshold; ---------------------------------------------------------- -- Procedure: unset_threshold_parameters_EM -- Description: Remove metric from statistical thresholding. -- If baseline is active then set threshold_parm -- columns to NULL for propagation to agent, else just -- delete the row. -- Arguments: bsln_rec_in - baseline record -- ds_rec_in - atasource record -- Exceptions: TBD ---------------------------------------------------------- procedure unset_threshold_parameters_EM (bsln_rec_in in mgmt_bsln_baselines%rowtype ,ds_rec_in in mgmt_bsln_datasources%rowtype ) is myname module_name_t := 'UNSET_THRESHOLD_PARAMETERS_EM'; begin assert(ds_rec_in.source_type = MGMT_BSLN.K_SOURCE_EM,myname||':source type EM:'||ds_rec_in.source_type); --------------------------------------------------------------- -- if baseline is active, then: -- 1) set null threshold parm values in threshold parms table -- for propagation to agent by hourly job -- 2) unset "has active baseline" flag for metric --------------------------------------------------------------- if MGMT_BSLN.baseline_is_active(bsln_rec_in.bsln_guid) then update mgmt_bsln_threshold_parms set warning_param = null ,critical_param = null -- ,num_occurrences = null ,fail_action = null -- ,threshold_method = null where bsln_guid = bsln_rec_in.bsln_guid and datasource_guid = ds_rec_in.datasource_guid; set_metric_bsln_flag (target_guid_in => ds_rec_in.target_guid ,metric_guid_in => ds_rec_in.metric_guid ,key_value_in => ds_rec_in.key_value ,flag_value_in => MGMT_BSLN.K_FALSE); else --------------------------------------------------------------- -- if baseline is inactive, simply delete threshold parms row --------------------------------------------------------------- delete from mgmt_bsln_threshold_parms where bsln_guid = bsln_rec_in.bsln_guid and datasource_guid = ds_rec_in.datasource_guid; end if; ---------------------------------------- -- leave commit to callers ---------------------------------------- end unset_threshold_parameters_EM; ---------------------------------------------------------- -- Procedure: unset_threshold_parameters_DB -- Description: Remove metric from statistical thresholding -- by particular baseline -- Arguments: bsln_rec_in - baseline record -- ds_rec_in - atasource record -- Exceptions: TBD ---------------------------------------------------------- procedure unset_threshold_parameters_DB (bsln_rec_in in mgmt_bsln_baselines%rowtype ,ds_rec_in in mgmt_bsln_datasources%rowtype ) is myname module_name_t := 'UNSET_THRESHOLD_PARAMETERS_DB'; begin assert(ds_rec_in.source_type = MGMT_BSLN.K_SOURCE_DB,myname||':source type DB:'||ds_rec_in.source_type); -- this procedure a no-op in Grid Control null; end unset_threshold_parameters_DB; ---------------------------------------------------------- -- Procedure: unset_threshold_parameters -- Description: Remove metric from statistical thresholding -- Arguments: bsln_guid_in - unique ID for baseline -- ds_guid_in - unique ID for datasource -- Exceptions: TBD ---------------------------------------------------------- procedure unset_threshold_parameters (bsln_guid_in in guid_t ,ds_guid_in in guid_t ) is myname module_name_t := 'UNSET_THRESHOLD_PARAMETERS'; l_dsrec mgmt_bsln_datasources%ROWTYPE; l_bslnrec mgmt_bsln_baselines%ROWTYPE; begin assert(bsln_guid_in is not null, myname||':bsln_guid_in not null'); assert(ds_guid_in is not null, myname||':ds_guid_in not null'); ------------------------------------------------------------ -- fetch/validate datasource row ------------------------------------------------------------ l_dsrec := MGMT_BSLN.datasource_rec(ds_guid_in); ------------------------------------------------------------ -- fetch/validate baseline row ------------------------------------------------------------ l_bslnrec := MGMT_BSLN.baseline_rec(bsln_guid_in); ------------------------------------------------------------ -- call deployment-specific logic ------------------------------------------------------------ if l_dsrec.source_type = MGMT_BSLN.K_SOURCE_EM then unset_threshold_parameters_EM(l_bslnrec,l_dsrec); elsif l_dsrec.source_type = MGMT_BSLN.K_SOURCE_DB then unset_threshold_parameters_DB(l_bslnrec,l_dsrec); else null; assert(FALSE, myname||':validate deployment:'||l_dsrec.source_type); end if; end unset_threshold_parameters; ---------------------------------------------------------- -- Procedure: set_threshold_parameters_DB -- Description: Set threshold parameters for a metric in a baseline. -- Arguments: bsln_rec_in - baseline record -- ds_rec_in - datasource record -- Exceptions: TBD ---------------------------------------------------------- procedure set_threshold_parameters_DB (bsln_rec_in in mgmt_bsln_baselines%rowtype ,ds_rec_in in mgmt_bsln_datasources%rowtype ) is myname module_name_t := 'SET_THRESHOLD_PARAMETERS_DB'; begin assert(ds_rec_in.source_type = MGMT_BSLN.K_SOURCE_DB,myname|| ':source type is DB:'||ds_rec_in.source_type); assert(bsln_rec_in.target_uid = ds_rec_in.target_uid,myname|| ':ds tguid = bs tguid'); -- this procedure a no-op in Grid Control null; end set_threshold_parameters_DB; ---------------------------------------------------------- -- Procedure: set_threshold_parameters_EM -- Description: Set threshold parameters for a metric in a baseline. -- Arguments: bsln_rec_in - baseline record -- ds_rec_in - atasource record -- Exceptions: TBD ---------------------------------------------------------- procedure set_threshold_parameters_EM (bsln_rec_in in mgmt_bsln_baselines%rowtype ,ds_rec_in in mgmt_bsln_datasources%rowtype ) is myname module_name_t := 'SET_THRESHOLD_PARAMETERS_EM'; begin assert(ds_rec_in.source_type = MGMT_BSLN.K_SOURCE_EM,myname||':source type EM:'||ds_rec_in.source_type); ----------------------------------------------------------------------- -- if baseline is active, set "has active baseline" flag TRUE in core ----------------------------------------------------------------------- if MGMT_BSLN.baseline_is_active(bsln_rec_in.bsln_guid) then set_metric_bsln_flag (target_guid_in => ds_rec_in.target_guid ,metric_guid_in => ds_rec_in.metric_guid ,key_value_in => ds_rec_in.key_value ,flag_value_in => MGMT_BSLN.K_TRUE); end if; end set_threshold_parameters_EM; ---------------------------------------------------------- -- Procedure: set_threshold_parameters -- Description: Set threshold parameters for a metric in a baseline. -- Arguments: bsln_guid_in - baseline unique identifier -- ds_guid_in - datasource unique identifier -- threshold_method_in - threshold method -- warning_param_in - warning parameter -- critical_param_in - critical_parameter -- num_occurs_in - number occurrences parameter -- Exceptions: TBD ---------------------------------------------------------- procedure set_threshold_parameters (bsln_guid_in in guid_t ,ds_guid_in in guid_t ,threshold_method_in in threshold_method_t ,warning_param_in in param_value_t ,critical_param_in in param_value_t ,num_occurs_in in integer := mgmt_bsln.K_DEFAULT_NUM_OCCURS ,fail_action_in in fail_action_t := mgmt_bsln.K_FAIL_ACTION_UNSET ) is myname module_name_t := 'SET_THRESHOLD_PARAMETERS'; l_num_occurs integer; l_bsln_rec mgmt_bsln_baselines%ROWTYPE; l_dsrec mgmt_bsln_datasources%ROWTYPE; begin assert(bsln_guid_in is not null, myname||':bsln_guid_in not null'); assert(ds_guid_in is not null, myname||':datasource_guid_in not null'); assert(valid_threshold_method(threshold_method_in), myname||':threshold_method_in validate'); assert(NVL(warning_param_in,1)>0, myname||':warning_param_in null or >0'); assert(NVL(critical_param_in,1)>0, myname||':critical_param_in null or >0'); assert(NVL(num_occurs_in,1)>0, myname||':num_occurs_in >0'); assert(valid_fail_action(fail_action_in), myname||':fail_action_in validate'); ------------------------------------- -- validate SIGLVL threshold params ------------------------------------- if threshold_method_in = mgmt_bsln.K_METHOD_SIGLVL then null; assert(valid_siglvl_param(warning_param_in),myname||':SIGLVL warning_param_in valid'); null; assert(valid_siglvl_param(critical_param_in),myname||':SIGLVL critical_param_in valid'); end if; ------------------------------------- -- Supply the default num occurs ------------------------------------- l_num_occurs := NVL(num_occurs_in, mgmt_bsln.K_DEFAULT_NUM_OCCURS); ------------------------------------------------ -- validate datasource guid and fetch attributes ------------------------------------------------ l_dsrec := mgmt_bsln.datasource_rec(ds_guid_in); ------------------------------------------------ -- validate baseline guid and fetch attributes ------------------------------------------------ l_bsln_rec := mgmt_bsln.baseline_rec(bsln_guid_in); -------------------------------- -- unset if both parms are null -------------------------------- if (warning_param_in is null and critical_param_in is null) then unset_threshold_parameters (bsln_guid_in => l_bsln_rec.bsln_guid ,ds_guid_in => l_dsrec.datasource_guid ); else ------------------------------------- -- params => threshold method required ------------------------------------- if (threshold_method_in is null) then RAISE_APPLICATION_ERROR (mgmt_bsln.X_INVALID_THRESHOLD_METHOD,'Invalid threshold method: '||threshold_method_in); else --------------------------------------------- -- assume update as that will be most frequent --------------------------------------------- update mgmt_bsln_threshold_parms set threshold_method = threshold_method_in ,warning_param = warning_param_in ,critical_param = critical_param_in ,num_occurrences = l_num_occurs ,fail_action = fail_action_in where bsln_guid = l_bsln_rec.bsln_guid and datasource_guid = l_dsrec.datasource_guid; if (SQL%ROWCOUNT = 0) then -------------------------------- -- There is no setting, insert -------------------------------- insert into mgmt_bsln_threshold_parms (bsln_guid ,datasource_guid ,threshold_method ,warning_param ,critical_param ,num_occurrences ,fail_action ) values (l_bsln_rec.bsln_guid ,l_dsrec.datasource_guid ,threshold_method_in ,warning_param_in ,critical_param_in ,l_num_occurs ,fail_action_in ); end if; end if; ------------------------------------------------------------ -- call deployment-specific logic ------------------------------------------------------------ if l_dsrec.source_type = MGMT_BSLN.K_SOURCE_EM then set_threshold_parameters_EM(l_bsln_rec,l_dsrec); elsif l_dsrec.source_type = MGMT_BSLN.K_SOURCE_DB then set_threshold_parameters_DB (bsln_rec_in => l_bsln_rec ,ds_rec_in => l_dsrec); else null; assert(FALSE, myname||':validate deployment:'||l_dsrec.source_type); end if; end if; ------------------------------------------- -- leave commit to caller ------------------------------------------- end set_threshold_parameters; ------------------------------------------------------------------------ -- Procedure: set_all_thresholds_DB -- Description: Sets statistical metric thresholds for [all active or -- specified] baseline(s) using parameters set in -- mgmt_bsln_threshold_parms. Will unset or preserve -- thresholds for bad model fit or insufficient data. -- -- Arguments: bsln_guid_in - (optional) specific baseline to set -- thresholds for (must be active or no-op) -- date_in - (optional) as-of date for thresholds -- determines statistics by subinterval_code -- NOTE: This module is specific to DB deployment mode -------------------------------------------------------------------------- procedure set_all_thresholds_DB (bsln_guid_in in guid_t := null ,date_in in date := SYSDATE) is myname module_name_t := 'SET_ALL_THRESHOLDS_DB'; l_date date; l_BT_cvar BT_cvtype; l_BT_rec BslnThr_rectype; l_bsln_rec mgmt_bsln_baselines%ROWTYPE; begin -- this procedure a no-op in Grid Control null; end set_all_thresholds_DB; ------------------------------------------------------------------------ -- Procedure: set_all_thresholds_EM -- Description: Sets statistical metric thresholds for [all active or -- specified] baseline(s) using parameters set in -- mgmt_bsln_threshold_parms. Will unset or preserve -- thresholds for bad model fit or insufficient data. -- -- Arguments: bsln_guid_in - (optional) specific baseline to set -- thresholds for (must be active or no-op) -- date_in - (optional) as-of date for thresholds -- determines statistics by subinterval_code -- NOTE: This module is specific to EM deployment mode -------------------------------------------------------------------------- procedure set_all_thresholds_EM (bsln_guid_in in guid_t := null ,date_in in date := SYSDATE ,unset_TF integer := 0 ) is myname module_name_t := 'SET_ALL_THRESHOLDS_EM'; l_operator_GT number := MGMT_GLOBAL.G_THRESHOLD_GT; l_operator_GE number := MGMT_GLOBAL.G_THRESHOLD_GE; l_ref_date date; ---------------------------------------------------------------------------- -- bulk containers for old and new threshold object tables ---------------------------------------------------------------------------- prv_metrics_thr_arr MGMT_METRIC_THR_OBJ_ARRAY := MGMT_METRIC_THR_OBJ_ARRAY(); new_metrics_thr_arr MGMT_METRIC_THR_OBJ_ARRAY := MGMT_METRIC_THR_OBJ_ARRAY(); chg_metrics_thr_arr MGMT_METRIC_THR_OBJ_ARRAY := MGMT_METRIC_THR_OBJ_ARRAY(); ---------------------------------------------------------------------------- -- CURSOR: bsln_targets_cur -- cursor to select list of targets that have thresholds to set. If bsln_guid_in -- is non-null then target associated with that baseline is selected, otherwise -- all targets with ACTIVE baselines AND datasources (metrics) with threshold -- parameter settings are selected. ---------------------------------------------------------------------------- cursor bsln_targets_cur(in_bsln_guid guid_t ) is select DISTINCT DS.target_guid as target_guid ,BL.bsln_guid as bsln_guid from mgmt_bsln_baselines BL ,mgmt_bsln_datasources DS ,mgmt_bsln_threshold_parms TP where BL.status = DECODE(in_bsln_guid,null,'ACTIVE',BL.status) and BL.bsln_guid = NVL(in_bsln_guid,BL.bsln_guid) and DS.source_type = 'EM' and TP.bsln_guid = BL.bsln_guid and TP.datasource_guid = DS.datasource_guid; ---------------------------------------------------------------------------- -- CURSOR: all_metrics_cur -- cursor to fetch all current metric thresholds for a target as instances -- of MGMT_METRIC_THR_OBJ type. ---------------------------------------------------------------------------- cursor all_metrics_cur(in_target_guid VARCHAR2 /* guid_t */ ) is SELECT MGMT_METRIC_THR_OBJ (M.metric_name ,M.metric_column ,M.metric_type ,M.key_column ,MT.coll_name ,MT.key_value ,MT.eval_order ,MT.num_occurences ,MT.fixit_job ,MT.warning_threshold ,MT.critical_threshold ,MT.warning_operator ,MT.critical_operator ,DECODE(TRIM(MT.key_value),null,0,1) ,MT.metric_guid ,MT.target_guid ,M.num_keys ,CK.key_part1_value ,CK.key_part2_value ,CK.key_part3_value ,CK.key_part4_value ,CK.key_part5_value ) FROM mgmt_metrics M ,mgmt_targets T ,mgmt_metric_thresholds MT ,mgmt_metrics_composite_keys CK WHERE T.target_guid = HEXTORAW(in_target_guid) AND M.target_type = T.target_type AND M.type_meta_ver = T.type_meta_ver AND(M.category_prop_1 = T.category_prop_1 OR M.category_prop_1 = ' ') AND(M.category_prop_2 = T.category_prop_2 OR M.category_prop_2 = ' ') AND(M.category_prop_3 = T.category_prop_3 OR M.category_prop_3 = ' ') AND(M.category_prop_4 = T.category_prop_4 OR M.category_prop_4 = ' ') AND(M.category_prop_5 = T.category_prop_5 OR M.category_prop_5 = ' ') AND MT.target_guid = T.target_guid AND MT.metric_guid = M.metric_guid AND MT.target_guid = CK.target_guid(+) AND MT.key_value = CK.composite_key(+) ORDER BY MT.target_guid ,MT.coll_name ,MT.metric_guid; ---------------------------------------------------------------------------- -- CURSOR: new_thr_cur -- cursor to join prv_metrics_thr_arr to mgmt_bsln_statistics and -- mgmt_bsln_threshold_parms for discovering new threshold values ---------------------------------------------------------------------------- cursor new_thr_cur(in_date date ,in_bsln_guid guid_t ,in_target_guid guid_t ) is select VALUE(TBL) as PRV_THR ,STPP.* from TABLE(CAST(prv_metrics_thr_arr AS MGMT_METRIC_THR_OBJ_ARRAY)) TBL ,(select PP.target_guid ,PP.metric_guid ,PP.key_value ,PP.threshold_method ,PP.num_occurrences ,PP.warning_param ,PP.critical_param ,PP.fail_action ,ST.sample_count ,ST.pctile_95 ,ST.est_pctile_99 ,ST.est_pctile_999 ,ST.est_pctile_9999 ,ST.est_fit_quality ,ST.est_sample_count ,ST.minimum ,ST.maximum from (select DS.datasource_guid ,BL.bsln_guid ,DS.target_guid ,DS.metric_guid ,DS.key_value ,TP.threshold_method ,NVL(TP.num_occurrences,1) as num_occurrences ,DECODE(unset_TF,0,TP.warning_param,null) as warning_param ,DECODE(unset_TF,0,TP.critical_param,null) as critical_param ,TP.fail_action ,mgmt_bsln.subinterval_code(BL.subinterval_key,in_date) as subinterval_code from mgmt_bsln_baselines BL ,mgmt_bsln_datasources DS ,mgmt_bsln_threshold_parms TP where DS.source_type = 'EM' and DS.target_guid = in_target_guid and BL.bsln_guid = in_bsln_guid and TP.bsln_guid = in_bsln_guid and TP.bsln_guid = BL.bsln_guid and TP.datasource_guid = DS.datasource_guid ) PP ,mgmt_bsln_statistics ST where PP.bsln_guid = ST.bsln_guid(+) and PP.datasource_guid = ST.datasource_guid(+) and PP.subinterval_code = ST.subinterval_code(+) ) STPP where STPP.target_guid = TBL.target_guid and STPP.metric_guid = TBL.metric_guid and STPP.key_value = TBL.key_value; --------------------------------------------------- -- THR_rec and new_thr_obj used when -- calling MGMT_BSLN.new_threshold_values --------------------------------------------------- THR_rec MGMT_BSLN.THR_rectype; new_thr_obj MGMT_METRIC_THR_OBJ; -- counter of changed thresholds l_changed_count integer; --------------------------------------------------- -- target name and type needed for update API --------------------------------------------------- l_target_name MGMT_TARGETS.target_name%TYPE; l_target_type MGMT_TARGETS.target_type%TYPE; -------------------------------- -- local debugging procedure -------------------------------- procedure debug(msg_in varchar2) is begin if EMDW_LOG.P_IS_DEBUG_SET then EMDW_LOG.DEBUG(msg_in,PKGNAME_C||':'||myname); --dbms_output.put_line(msg_in); end if; end debug; ----------------------------------- -- main procedure begins here ----------------------------------- begin assert(unset_TF in (mgmt_bsln.K_TRUE,mgmt_bsln.K_FALSE), myname|| ':unset_TF in K_TRUE,K_FALSE:'||unset_TF); ------------------------------------------------- -- initialize reference date to HOUR of date_in ------------------------------------------------- l_ref_date := TRUNC(NVL(date_in,SYSDATE),'HH24'); ---------------------------------------------------------------- -- open list of targets for which to set thresholds ---------------------------------------------------------------- for bsln_targets_rec in bsln_targets_cur(bsln_guid_in) loop ------------------------------------------------------ -- initialize metric threshold containers ------------------------------------------------------ prv_metrics_thr_arr := MGMT_METRIC_THR_OBJ_ARRAY(); new_metrics_thr_arr := MGMT_METRIC_THR_OBJ_ARRAY(); chg_metrics_thr_arr := MGMT_METRIC_THR_OBJ_ARRAY(); ------------------------------------------------------------- -- load all current metric thresholds for target ------------------------------------------------------------- open all_metrics_cur(bsln_targets_rec.target_guid); fetch all_metrics_cur bulk collect into prv_metrics_thr_arr; close all_metrics_cur; debug('target_guid: '||RAWTOHEX(bsln_targets_rec.target_guid)); debug('prv_metrics_thr_arr.COUNT: '||prv_metrics_thr_arr.COUNT); ------------------------------------------------------ -- grab target_name and target_type ------------------------------------------------------ select target_name, target_type into l_target_name, l_target_type from mgmt_targets where target_guid = bsln_targets_rec.target_guid; --------------------------------------------------------------- -- Compute new thresholds using previous thresholds, statistics, -- and threshold parms. Load these into new_metrics_thr_arr. --------------------------------------------------------------- for new_thr_rec in new_thr_cur(l_ref_date ,bsln_targets_rec.bsln_guid ,bsln_targets_rec.target_guid ) loop debug('metric_name,coll_name: '||new_thr_rec.PRV_THR.metric_name|| ','||new_thr_rec.PRV_THR.coll_name); ----------------------------------------------------- -- initialize new_metrics_thr_obj with prev ----------------------------------------------------- new_thr_obj := new_thr_rec.PRV_THR; ----------------------------------------------------- -- assemble THR_rec to pass to new_threshold_value ----------------------------------------------------- THR_rec.threshold_method := new_thr_rec.threshold_method; THR_rec.num_occurrences := new_thr_rec.num_occurrences; THR_rec.warning_param := new_thr_rec.warning_param; THR_rec.critical_param := new_thr_rec.critical_param; THR_rec.fail_action := new_thr_rec.fail_action; THR_rec.sample_count := new_thr_rec.sample_count; THR_rec.minval := new_thr_rec.minimum; THR_rec.maxval := new_thr_rec.maximum; THR_rec.pctile_95 := new_thr_rec.pctile_95; THR_rec.pctile_99 := new_thr_rec.est_pctile_99; THR_rec.pctile_999 := new_thr_rec.est_pctile_999; THR_rec.pctile_9999 := new_thr_rec.est_pctile_9999; THR_rec.est_fit_quality := new_thr_rec.est_fit_quality; THR_rec.est_sample_count := new_thr_rec.est_sample_count; ------------------------------------------------------------ -- call new_threshold_value for warning and critical -- or set values to null if param is null -- NOTE: here we enforce also the current requirement that -- only metrics with GT or GE alert semantics are allowed ------------------------------------------------------------ if new_thr_obj.warning_operator in (l_operator_GT, l_operator_GE) then if THR_rec.warning_param is not null then MGMT_BSLN.new_threshold_value (THR_rec, THR_rec.warning_param, new_thr_obj.warning_threshold); else new_thr_obj.warning_threshold := null; end if; end if; if new_thr_obj.critical_operator in (l_operator_GT, l_operator_GE) then if THR_rec.critical_param is not null then MGMT_BSLN.new_threshold_value (THR_rec, THR_rec.critical_param, new_thr_obj.critical_threshold); else new_thr_obj.critical_threshold := null; end if; end if; ----------------------------------------------------------------- -- in Grid Control null thresholds are really ' ' (single blank) ----------------------------------------------------------------- new_thr_obj.warning_threshold := NVL(new_thr_obj.warning_threshold,' '); new_thr_obj.critical_threshold := NVL(new_thr_obj.critical_threshold,' '); --------------------------------------------------------- -- extend new_metrics_thr_arr and add new_thr_obj to it --------------------------------------------------------- new_metrics_thr_arr.EXTEND; new_metrics_thr_arr(new_metrics_thr_arr.LAST) := new_thr_obj; end loop; -- new_thr_cur ----------------------------------------------------------------- -- isolate the real changed thresholds in chg_metrics_thr_arr ----------------------------------------------------------------- select MGMT_METRIC_THR_OBJ( M.METRIC_NAME ,M.METRIC_COLUMN ,M.METRIC_TYPE ,M.KEY_COLUMN ,M.COLL_NAME ,M.KEY_VALUE ,M.EVAL_ORDER ,M.NUM_OCCURENCES ,M.FIXIT_JOB ,M.WARNING_THRESHOLD ,M.CRITICAL_THRESHOLD ,M.WARNING_OPERATOR ,M.CRITICAL_OPERATOR ,M.HAS_KEY_COLUMN ,M.METRIC_GUID ,M.TARGET_GUID ,M.NUM_KEYS ,M.KEY_PART1_VALUE ,M.KEY_PART2_VALUE ,M.KEY_PART3_VALUE ,M.KEY_PART4_VALUE ,M.KEY_PART5_VALUE ) BULK COLLECT INTO chg_metrics_thr_arr from (select * from TABLE(new_metrics_thr_arr) X minus select * from TABLE(prv_metrics_thr_arr) Y ) M; debug('chg_metrics_thr_arr.COUNT: '||chg_metrics_thr_arr.COUNT); ----------------------------------------------------------------- -- build collections with truly changed thresholds and call -- the update_metric_thresholds API ----------------------------------------------------------------- if chg_metrics_thr_arr.COUNT > 0 -- any work to do? then --------------------------------- -- reuse the "new" container --------------------------------- new_metrics_thr_arr := MGMT_METRIC_THR_OBJ_ARRAY(); -------------------------------------------------------------- -- first load new_metrics_thr_arr with thresholds from prev -- table that belong to collections with changes but are not -- the changed metrics -------------------------------------------------------------- select value(P) bulk collect into new_metrics_thr_arr from TABLE(prv_metrics_thr_arr) P where P.coll_name in (select distinct C1.coll_name from TABLE(chg_metrics_thr_arr) C1 ) and (P.target_guid,P.metric_guid,P.key_value,P.coll_name) NOT IN (select C2.target_guid,C2.metric_guid,C2.key_value,C2.coll_name from TABLE(chg_metrics_thr_arr) C2 ); debug('new_metrics_thr_arr.COUNT1: '||new_metrics_thr_arr.COUNT); --------------------------------------------------------------- -- append the elements of chg_metrics_thr_arr to get the final -- list of metric thresholds with which to call update API --------------------------------------------------------------- for i in chg_metrics_thr_arr.FIRST..chg_metrics_thr_arr.LAST loop new_metrics_thr_arr.EXTEND; new_metrics_thr_arr(new_metrics_thr_arr.LAST) := chg_metrics_thr_arr(i); debug('coll_name,new warn,new crit: '||chg_metrics_thr_arr(i).coll_name||','|| chg_metrics_thr_arr(i).warning_threshold||','|| chg_metrics_thr_arr(i).critical_threshold); end loop; debug('new_metrics_thr_arr.COUNT2: '||new_metrics_thr_arr.COUNT); begin MGMT_TARGET_UPDATE.UPDATE_METRIC_THRESHOLDS (P_TARGET_NAME => l_target_name ,P_TARGET_TYPE => l_target_type ,P_SOURCE_TARGET_NAME => l_target_name ,P_SOURCE_TARGET_TYPE => l_target_type ,P_DATA_SET => new_metrics_thr_arr ); exception when others then debug(SQLERRM); RAISE; end; end if; commit work; -- each batch is a target job end loop; -- bsln_targets_cur ------------------------------------------------------------------ -- 5) delete from threshold parms tbl where critical and warning -- parameter values are null, this is how unset_threshold_parameter -- eventually clears a baseline-generated threshold ------------------------------------------------------------------ delete from mgmt_bsln_threshold_parms TP where TP.critical_param is null and TP.warning_param is null and TP.bsln_guid = NVL(bsln_guid_in,TP.bsln_guid); debug('delete count:'||SQL%ROWCOUNT); ------------------------------------------------------------------ -- commit work, not a client procedure ------------------------------------------------------------------ commit work; end set_all_thresholds_EM; ------------------------------------------------------------------------- -- Procedure: set_all_thresholds -- Description: Sets statistical metric thresholds for all active -- baseline(s) as-of current date by calling deployment-specific -- threshold setting module located in this package. This is -- a common stub for mgmt_bsln.set_all_thresholds to call the -- deployment-specific implementation. -- Arguments: date_in - as/of date for computing threshold values -- NOTE: -------------------------------------------------------------------------- procedure set_all_thresholds (bsln_guid_in in guid_t := null) is begin MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_BSLN_THRESHOLDS_NAME); -- TODO:lock set_all_thresholds_EM(bsln_guid_in => bsln_guid_in , date_in => SYSDATE); -- TODO:unlock end set_all_thresholds; ------------------------------------------------------------------------- -- Procedure: unset_all_thresholds_DB -- Description: Unsets metric thresholds for specified baseline. Only -- operates on metrics that have settings defined for the -- baseline. -- Arguments: bsln_guid_in - Unique identifier of the baseline. -------------------------------------------------------------------------- procedure unset_all_thresholds_DB (bsln_guid_in in guid_t) is myname module_name_t := 'UNSET_ALL_THRESHOLDS_DB'; begin assert(bsln_guid_in is not null, myname||':bsln_guid_in not null'); -- this procedure a no-op in Grid Control null; end unset_all_thresholds_DB; ------------------------------------------------------------------------- -- Procedure: unset_all_thresholds_EM -- Description: Unsets metric thresholds for specified baseline. Only -- operates on metrics that have settings defined for the -- baseline. -- Arguments: bsln_guid_in - Unique identifier of the baseline. -------------------------------------------------------------------------- procedure unset_all_thresholds_EM (bsln_guid_in in guid_t) is myname module_name_t := 'UNSET_ALL_THRESHOLDS_EM'; begin assert(bsln_guid_in is not null, myname||':bsln_guid_in not null'); ----------------------------------------------------------------------- -- call set_all_thresholds for this baseline and pass unset_TF as TRUE ----------------------------------------------------------------------- set_all_thresholds_EM (bsln_guid_in => bsln_guid_in ,date_in => SYSDATE ,unset_TF => MGMT_BSLN.K_TRUE); end unset_all_thresholds_EM; ------------------------------------------------------------------------- -- Procedure: unset_all_thresholds -- Description: Unsets metric thresholds for specified baseline. Only -- operates on metrics that have settings defined for the -- baseline. -- Arguments: bsln_guid_in - Unique identifier of the baseline. -------------------------------------------------------------------------- procedure unset_all_thresholds (bsln_guid_in in guid_t) is myname module_name_t := 'UNSET_ALL_THRESHOLDS'; l_bsln_rec mgmt_bsln_baselines%ROWTYPE; begin assert(bsln_guid_in is not null, myname||':bsln_guid_in not null'); ------------------------------------- -- validate/retrieve bsln row ------------------------------------- l_bsln_rec := MGMT_BSLN.BASELINE_REC(bsln_guid_in); ------------------------------------- -- call deployment specific code ------------------------------------- if MGMT_BSLN.TARGET_SOURCE_TYPE(l_bsln_rec.target_uid) = MGMT_BSLN.K_SOURCE_DB then -- TODO:lock unset_all_thresholds_DB(bsln_guid_in); elsif MGMT_BSLN.TARGET_SOURCE_TYPE(l_bsln_rec.target_uid) = MGMT_BSLN.K_SOURCE_EM then -- TODO:lock unset_all_thresholds_EM(bsln_guid_in); else -- invalid source_type assert(null,myname||':source type in (EM,DB)'); end if; -- TODO:unlock end unset_all_thresholds; ------------------------------------------------------------------------- -- Procedure: submit_bsln_jobs -- Description: Submits baseline compute and threshold set jobs to -- either job queue or scheduler. -- Arguments: none -------------------------------------------------------------------------- procedure submit_bsln_jobs is myname module_name_t := 'SUBMIT_BSLN_JOBS'; jobno integer; begin ------------------------------------- -- delete and re-submit ------------------------------------- delete_bsln_jobs; ---------------------------------------------------------------- -- schedule compute daily at 11:30pm -- NOTE: logic for next compute is the following: -- * current execution is before 12:00pm, execute next tonight at 11:30pm -- * current execution is after 12:00pm, execute next tomorrow at 11:30pm ---------------------------------------------------------------- dbms_job.submit (job => jobno ,what => K_COMPUTE_STATS_JOB ,next_date => trunc(sysdate+1) - 1/48 ,interval => 'trunc(sysdate+1/2) + 47/48' ); ------------------------------------- -- schedule set thresholds every hour ------------------------------------- dbms_job.submit (job => jobno ,what => K_SET_THRESHOLDS_JOB ,next_date => trunc(sysdate,'HH24') ,interval => 'trunc(sysdate,''HH24'')+1/24' ); end submit_bsln_jobs; ------------------------------------------------------------------------- -- Procedure: delete_bsln_jobs -- Description: Removes baseline compute and threshold set jobs from -- either job queue or scheduler. -- Arguments: none -------------------------------------------------------------------------- procedure delete_bsln_jobs is myname module_name_t := 'DELETE_BSLN_JOBS'; begin for jrec in (select job from user_jobs where UPPER(what) = K_SET_THRESHOLDS_JOB or UPPER(what) = K_COMPUTE_STATS_JOB ) loop dbms_job.remove(jrec.job); end loop; end delete_bsln_jobs; --------------------------------------------------------------------------- -- Function: is_enabled -- Description: Deployment-specific function that returns K_TRUE if -- baslining is enabled on this target (instance). -- K_TRUE iff (_flush set AND both jobs defined AND at least 1 -- registered metric for target) -- Arguments: none --------------------------------------------------------------------------- function is_enabled return integer is myname module_name_t := 'IS_ENABLED'; l_compute_job_exists integer := 0; l_thresh_job_exists integer := 0; begin select count(1) into l_compute_job_exists from user_jobs where what = K_COMPUTE_STATS_JOB; select count(1) into l_thresh_job_exists from user_jobs where what = K_SET_THRESHOLDS_JOB; if (l_compute_job_exists > 0 AND l_thresh_job_exists > 0) then return MGMT_BSLN.K_TRUE; else return MGMT_BSLN.K_FALSE; end if; end is_enabled; ----------------------------------------------------------------------------- -- -- SLPA implementation code -- ----------------------------------------------------------------------------- PROCEDURE assert (bool_IN IN BOOLEAN ,msg_IN IN VARCHAR2 := null) IS BEGIN IF NOT NVL(bool_IN,FALSE) -- fail on null input THEN RAISE_APPLICATION_ERROR ( ASSERTFAIL_C, 'ASSERTFAIL:'|| PKGNAME_C||':'||SUBSTR(msg_IN,1,200) ) ; END IF; END assert; ------------------------------------------------------------------------- -- Function: rawdata_retention -- Description: Grid Control specific component to return current rawdata -- rawdata retention setting. -- Arguments: none ------------------------------------------------------------------------- function rawdata_retention return integer is myname module_name_t := 'RAWDATA_RETENTION'; begin return G_RAWDATA_RETENTION; end rawdata_retention; ------------------------------------------------------------------------- -- Procedure: set_rawdata_retention -- Description: Grid Control specific component to set rawdata retention -- for use by data purge. -- Arguments: duration_in - integer number of days to keep data ------------------------------------------------------------------------- procedure set_rawdata_retention(retention_in in integer) is myname module_name_t := 'SET_RAWDATA_RETENTION'; begin assert(retention_in >=7,myname||':retention >=7'); G_RAWDATA_RETENTION := retention_in; end set_rawdata_retention; PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON (p_value IN BOOLEAN, p_num IN NUMBER) IS BEGIN IF p_num = 1 THEN MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_BSLN_STAT_NAME, p_value); ELSE MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_BSLN_THRESHOLDS_NAME, p_value); END IF; END DBMSJOB_EXTENDED_SQL_TRACE_ON; ------------------------------------------------------------------------- -- Procedure: purge_rawdata -- Description: Grid Control specific component to delete all data from -- MGMT_BSLN_RAWDATA older than RAWDATA_RETENTION for either -- all datasources or all datasources for a target. ------------------------------------------------------------------------- procedure purge_rawdata (target_guid_in in mgmt_bsln.guid_t := null) is myname module_name_t := 'PURGE_RAWDATA'; l_retention integer; begin -------------------------------------------------- -- initialize local retention -------------------------------------------------- l_retention := rawdata_retention; -------------------------------------------------- -- delete expired data in one fell swoop either -- by target or for all datasources -------------------------------------------------- if target_guid_in is null then --------------------------- -- all targets case --------------------------- delete from mgmt_bsln_rawdata where obs_time < (TRUNC(SYSDATE) - l_retention); else ----------------------------------- -- single target case ----------------------------------- delete from mgmt_bsln_rawdata where datasource_guid in (select datasource_guid from mgmt_bsln_datasources where target_guid = target_guid_in) and obs_time < (TRUNC(SYSDATE) - l_retention); end if; ------------------------------ -- commit all purged data txn ------------------------------ commit; end purge_rawdata; ------------------------------------------------------------------------- -- Procedure: extract_rawdata -- Description: Grid Control specific component to pull new data for -- registered datasources from MGMT_METRICS_RAW into -- MGMT_BSLN_RAWDATA. -- Arguments: target_guid_in - identify target to pull data, -- null = all targets -- window_hrs_in - size of time window for raw data cursor ------------------------------------------------------------------------- procedure extract_rawdata (target_guid_in in mgmt_bsln.guid_t := null ,window_hrs_in in integer := 24) is myname module_name_t := 'EXTRACT_RAWDATA'; -- list of datasources to extract data for l_dsguid_array MGMT_GUID_ARRAY := MGMT_GUID_ARRAY(); -- these variables control cursor l_begin_time date; l_end_time date; l_window_hrs integer; ------------------------------------------------------- -- Cursor to extract data from window of partitions -- in MGMT_METRICS_RAW for datasources referenced in -- the local array of datasources. ------------------------------------------------------- CURSOR MRdata_cur(in_begin_time date, in_end_time date) is select DS.datasource_guid, MR.collection_timestamp, MR.value from mgmt_metrics_raw MR ,(select D.datasource_guid, D.target_guid, D.metric_guid, D.key_value from mgmt_bsln_datasources D ,TABLE(CAST(l_dsguid_array AS MGMT_GUID_ARRAY)) L where L.guid = D.datasource_guid and D.source_type = 'EM' and L.name is null ) DS where MR.target_guid = DS.target_guid and MR.metric_guid = DS.metric_guid and MR.key_value = DS.key_value and MR.collection_timestamp >= in_begin_time and MR.collection_timestamp < in_end_time order by DS.datasource_guid ,MR.collection_timestamp DESC; -- record to fetch into from cursor MRdata_rec MRdata_cur%ROWTYPE; ------------------------------------------------- -- local procedure to mark a datasource_guid -- for exclusion from list of those to process ------------------------------------------------- l_last_dsguid mgmt_bsln.guid_t; -- bypass procedure del_dsguid(dsguid_in in mgmt_bsln.guid_t) is begin ------------------------------------ -- return if same as last deleted ------------------------------------ if dsguid_in = l_last_dsguid then RETURN; end if; if l_dsguid_array.COUNT = 0 then return; else ------------------------------------------ -- find and mark the ds guid ------------------------------------------ for i in l_dsguid_array.FIRST..l_dsguid_array.LAST loop if l_dsguid_array(i).guid = dsguid_in then l_dsguid_array(i).name := 'X'; l_last_dsguid := dsguid_in; RETURN; end if; end loop; end if; end del_dsguid; ------------------------------------- -- begin MAIN procedure ------------------------------------ begin ------------------------------ -- job tracing support ------------------------------ MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_BSLN_STAT_NAME); ------------------------------------------------------------------ -- initialize local variables -- NOTE: initial begin-end window is large to account for -- possibly of targets in timezones up to 24 hrs ahead of -- repository. ------------------------------------------------------------------ l_window_hrs := NVL(window_hrs_in,24); l_end_time := TRUNC(SYSDATE+25/24,'HH24'); l_begin_time := TRUNC(SYSDATE,'HH24') - l_window_hrs/24; ------------------------------------------------------ -- initialize local array of datasource guids for -- target_guid_in (or all targets when null). ------------------------------------------------------ select MGMT_GUID_OBJ(D.datasource_guid,null) bulk collect into l_dsguid_array from mgmt_bsln_datasources D where D.source_type = 'EM' and D.target_guid = NVL(target_guid_in,D.target_guid); ---------------------------------------------------------------- -- Extraction process uses loop-within-loop structure. -- Outer loop opens cursor of time window into mgmt_metrics_raw -- over list of datasources to process. -- Inner loop fetches data and inserts into mgmt_bsln_rawdata -- flagging datasources for exclusion when duplicate data found. -- Since outer loop time windows proceed backward in time eventually -- either no data or no datasources will be left to process. ---------------------------------------------------------------- loop -- window loop open MRdata_cur(l_begin_time,l_end_time); fetch MRdata_cur into MRdata_rec; ----------------------------------------------------- -- exit window loop when no data on first fetch -- this is truly the end of data to process ----------------------------------------------------- exit when MRdata_cur%NOTFOUND; loop -- data loop ------------------------------------- -- insert data into mgmt_bsln_rawdata -- if dup val then remove datasource ------------------------------------- begin insert into mgmt_bsln_rawdata (datasource_guid ,obs_time ,obs_value) values (MRdata_rec.datasource_guid ,MRdata_rec.collection_timestamp ,MRdata_rec.value); exception when DUP_VAL_ON_INDEX then del_dsguid(MRdata_rec.datasource_guid); end; ------------------------------------------- -- re-fetch from cursor ------------------------------------------- fetch MRdata_cur into MRdata_rec; ------------------------------------------- -- if no more data initialize next window, -- close cursor, and exit to window loop ------------------------------------------- if MRdata_cur%NOTFOUND then l_end_time := l_begin_time; l_begin_time := l_end_time - l_window_hrs/24; close MRdata_cur; exit; -- exit data loop end if; end loop; -- data loop commit work; -- commit whole window (not using batch_size) l_last_dsguid := NULL; -- reset the bypass guid end loop; -- window loop close MRdata_cur; end extract_rawdata; ------------------------------------------------------------------------- -- Procedure: purge_extract_rawdata -- Description: Grid Control specific component to pull new data for -- registered datasources from MGMT_METRICS_RAW into -- MGMT_BSLN_RAWDATA. Also purges any data older than -- retention period. -- Arguments: target_guid_in - identify target to pull data, -- null = all targets -- window_hrs_in - size of time window for raw data cursor ------------------------------------------------------------------------- procedure purge_extract_rawdata (target_guid_in in mgmt_bsln.guid_t := null ,window_hrs_in in integer := 24) as myname module_name_t := 'PURGE_EXTRACT_RAWDATA'; begin purge_rawdata(target_guid_in); extract_rawdata(target_guid_in, window_hrs_in); exception when others then mgmt_log.log_error('COMPUTE METRIC BASELINE STATISTICS', sqlcode, 'purge_extract_rawdata:'|| SUBSTR(SQLERRM, 1, 1000) ); end purge_extract_rawdata; ----------------------------------------------------------------------------- -- Function: metric_guid_by_name -- Description: Grid Control specific module that fetches metric guid by -- name and target_type. -- Arguments: target_type_in - target type of metric -- metric_name_in - EM metric name -- metric_column_in - EM metric column name -- Returns: metric_guid for the metric, or NULL. ----------------------------------------------------------------------------- function metric_guid_by_name (target_type_in in mgmt_metrics.target_type%TYPE ,metric_name_in in mgmt_metrics.metric_name%TYPE ,metric_column_in in mgmt_metrics.metric_column%TYPE) return guid_t is myname module_name_t := 'METRIC_GUID_BY_NAME'; l_metric_guid guid_t; begin assert(target_type_in is not null,myname||':target_type not null'); assert(metric_name_in is not null,myname||':metric_name_in not null'); assert(metric_column_in is not null,myname||':metric_column_in not null'); ----------------------------------------------------------- -- Fetch the guid, use distinct in case multiple rows -- for same guid (category properties diffs?) ----------------------------------------------------------- select DISTINCT metric_guid into l_metric_guid from mgmt_metrics where target_type = target_type_in and metric_name = metric_name_in and metric_column = metric_column_in; return l_metric_guid; end metric_guid_by_name; ----------------------------------------------------------------------------- -- Procedure: set_metric_bsln_flag -- Description: Grid Control specific module that sets the value of -- MGMT_POLICY_ASSOC_CFG.HAS_ACTIVE_BASELINE flag on or off. -- Also updates PREVENT_OVERRIDE flag to same value. -- Arguments: target_guid_in - target identifier -- metric_guid_in - metric identifier -- key_value_in - metric id qualifier -- flag_value_in - must be TRUE (1) or FALSE (0) ----------------------------------------------------------------------------- procedure set_metric_bsln_flag (target_guid_in in guid_t ,metric_guid_in in guid_t ,key_value_in in key_value_t ,flag_value_in in integer) is myname module_name_t := 'SET_METRIC_BSLN_FLAG'; begin assert(target_guid_in is not null,myname||':target_guid not null'); assert(metric_guid_in is not null,myname||':metric_guid not null'); assert(key_value_in is not null,myname||':key_value_in not null'); assert(flag_value_in in (mgmt_bsln.K_TRUE,mgmt_bsln.K_FALSE),myname||':flag_value_in in (0,1)'); ------------------------------------------------- -- NOTE: flag updated across all collections ------------------------------------------------- update mgmt_policy_assoc_cfg CFG set has_active_baseline = flag_value_in ,prevent_override = flag_value_in where CFG.policy_guid = metric_guid_in and CFG.object_guid = target_guid_in and CFG.key_value = key_value_in; end set_metric_bsln_flag; ----------------------------------------------------------------------------- -- Function: test_metrics_EM -- Description: Grid Control specific module that tests fitness of metrics -- for metric baseline registration. -- Arguments: metric_array_in - array of metric identifiers to test -- window_days_in - defines length of data window into -- into table MGMT_METRICS_RAW for testing -- end_date_in - end date of data window for metric testing ----------------------------------------------------------------------------- function test_metrics_EM (metric_array_in IN MGMT_METRIC_DETAILS_ARRAY ,window_days_in IN number := 3 ,end_date_in IN date := TRUNC(SYSDATE)+1 ) return bsln_statistics_set is myname module_name_t := 'TEST_METRICS_EM'; --------------------------------------------------- l_target_guid MGMT_BSLN.guid_t; l_bsln_guid MGMT_BSLN.guid_t; l_statistics_set bsln_statistics_set := bsln_statistics_set(); l_begin_date date; l_end_date date; l_window_days number; --------------------------------------------------- -- NOTE: 1 > l_P1 > l_P2 > l_P3 > 0.7 --------------------------------------------------- l_P1 number := 0.99; -- formula l_P2 number := 0.95; -- default l_P3 number := 0.90; -- values begin ----------------------------------- -- precondition assertions ----------------------------------- assert(window_days_in > 0, myname||':window_days_in >0'); assert(end_date_in is not null, myname||':end_date_in not null'); assert(metric_array_in is not null, myname||':metric_array_in not null'); assert(metric_array_in.COUNT >0, myname||':metric_array_in.COUNT >0'); -------------------------------------------------- -- local variable initialization -------------------------------------------------- l_window_days := NVL(window_days_in,3); l_end_date := NVL(end_date_in,TRUNC(SYSDATE)+1); l_begin_date := l_end_date - l_window_days; -------------------------------------------------------------- -- get placeholder bsln_guid using shared constant name -- NOTE: this code does not create a persisted baseline of that -- name, just the component attributes required to assemble -- bsln_statistics_t objects. -------------------------------------------------------------- l_target_guid := metric_array_in(1).target_guid; l_bsln_guid := MGMT_BSLN.baseline_guid (target_uid_in => MGMT_BSLN.target_uid(l_target_guid) ,name_in => K_TESTBSLN_NAME); ------------------------------------------------- -- produce min stats, fitfcn and fit-ln ratio -- for all registered datasources during window ------------------------------------------------- select BSLN_STATISTICS_T (l_bsln_guid ,datasource_guid ,SYSDATE ,TimeGroupCode ,sample_count ,average ,minimum ,maximum ,sdev ,pctile_25 ,pctile_50 ,pctile_75 ,pctile_90 ,pctile_95 ,est_sample_count ,FitFcn01 ,LnFcn01 ,CASE WHEN FitFcn01 <=0 THEN FitFcn01 WHEN (FitFcn01 / LnFcn01) BETWEEN 0.4 AND 50 THEN (FitFcn01 / LnFcn01) ELSE 0 END ,est_pctile_99 ,TO_NUMBER(null) ,TO_NUMBER(null) ) bulk collect into l_statistics_set from -------------------------------------------------- -- query to produce fit function and ln function -- NOTE: 1 > P1 > P2 > P3 > 0 -------------------------------------------------- (select TimeGroupCode ,datasource_guid ,sample_count ,est_sample_count ,CASE WHEN sample_count < 300 THEN -1 WHEN est_sample_count < 50 THEN -1 WHEN pctileP2_disc = pctileP3_disc THEN 0 WHEN pctileP1_disc = pctileP2_disc THEN 0 ELSE (pctileP1_cont - pctileP2_cont) / (pctileP2_cont - pctileP3_cont) END as FitFcn01 ,CASE WHEN pctileP2_cont <> pctileP3_cont AND pctileP2_disc <> pctileP1_disc AND est_sample_count >= 50 THEN LN( (1-l_P1) / (1-l_P2) ) / LN( (1-l_P2) / (1-l_P3) ) ELSE TO_NUMBER(null) END as LnFcn01 ,maximum ,minimum ,average ,sdev ,pctile_25 ,pctile_50 ,pctile_75 ,pctile_90 ,pctile_95 ,est_pctile_99 from -------------------------------------------------- -- query to compute aggregates over time series -------------------------------------------------- (select TimeGroupCode ,datasource_guid ,COUNT(*) as sample_count ,COUNT(DISTINCT value) as est_sample_count ,PERCENTILE_DISC(l_P3) WITHIN GROUP (ORDER BY value ASC) as pctileP3_disc ,PERCENTILE_CONT(l_P3) WITHIN GROUP (ORDER BY value ASC) as pctileP3_cont ,PERCENTILE_DISC(l_P2) WITHIN GROUP (ORDER BY value ASC) as pctileP2_disc ,PERCENTILE_CONT(l_P2) WITHIN GROUP (ORDER BY value ASC) as pctileP2_cont ,PERCENTILE_DISC(l_P1) WITHIN GROUP (ORDER BY value ASC) as pctileP1_disc ,PERCENTILE_CONT(l_P1) WITHIN GROUP (ORDER BY value ASC) as pctileP1_cont ,MAX(value) as maximum ,MIN(value) as minimum ,AVG(value) as average ,STDDEV(value) as sdev ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value ASC) as pctile_25 ,PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY value ASC) as pctile_50 ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY value ASC) as pctile_75 ,PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY value ASC) as pctile_90 ,PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value ASC) as pctile_95 ,PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY value ASC) as est_pctile_99 from ------------------------------------------------- -- query to create TimeGroup based time series ------------------------------------------------- (select MGMT_BSLN.SUBINTERVAL_CODE('XX',MR.collection_timestamp) as TimeGroupCode ,MGMT_BSLN.DATASOURCE_GUID(MR.target_guid ,MR.metric_guid ,MR.key_value) as datasource_guid ,value from sysman.mgmt_metrics_raw MR ,TABLE(metric_array_in) MA where MR.target_guid = MA.target_guid and MR.metric_guid = MA.metric_guid and MR.key_value = MA.key_value and MR.collection_timestamp BETWEEN l_begin_date AND l_end_date ) RawData group by Datasource_Guid, TimeGroupCode ) StatsData ) FcnData; RETURN l_statistics_set; end test_metrics_EM; ----------------------------------------------------------------------------- -- Function: test_metrics_EM -- Description: Grid Control specific module that tests fitness of metrics -- for metric baseline registration. -- NOTE: new overloaded version for UI convenience, calls -- original version after signature conversion. -- Arguments: metric_array_in - array of metric identifiers to test -- window_days_in - defines length of data window into -- into table MGMT_METRICS_RAW for testing -- end_date_in - end date of data window for metric testing ----------------------------------------------------------------------------- function test_metrics_EM (metric_array_in IN MGMT_METRIC_INSTANCE_ARRAY ,window_days_in IN number := 3 ,end_date_in IN date := TRUNC(SYSDATE)+1 ) return bsln_statistics_set is myname module_name_t := 'TEST_METRICS_EM2'; l_statistics_set bsln_statistics_set := bsln_statistics_set(); l_metric_details_array MGMT_METRIC_DETAILS_ARRAY := MGMT_METRIC_DETAILS_ARRAY(); -- container for new output api results l_statistics_set_newoutapi bsln_statistics_set := bsln_statistics_set(); begin assert(metric_array_in is not null, myname||':metric_array_in not null'); assert(metric_array_in.COUNT >0, myname||':metric_array_in.COUNT >0'); ---------------------------------------------------------------- -- assemble MGMT_METRIC_DETAILS_ARRAY collection used by old API -- from the MGMT_METRIC_INSTANCE_ARRAY collection in this API ---------------------------------------------------------------- select MGMT_METRIC_DETAILS (MGMT_TARGET.GET_TARGET_GUID(MA.target_name,MA.target_type) ,MGMT_METRIC.GET_METRIC_GUID(MA.target_type ,MA.metric_name ,MA.metric_column) ,MA.key_value ,NULL ,NULL) bulk collect into l_metric_details_array from TABLE(CAST(metric_array_in AS MGMT_METRIC_INSTANCE_ARRAY) ) MA; assert(l_metric_details_array.COUNT>0, myname||':metric_details_array.COUNT>0'); --------------------------------------------------- -- call original API using newly built collection --------------------------------------------------- --l_statistics_set := test_metrics_EM -- (l_metric_details_array, window_days_in, end_date_in); ---------------------------------------------------- -- NEW output api returns row for every input row -- implementation uses outer join and case ---------------------------------------------------- select CASE when S.datasource_guid is not null then VALUE(S) else -- no stats -- assemble null stat object from pieces in l_metric_details_array BSLN_STATISTICS_T ( /*BSLN_GUID*/ MGMT_BSLN.BASELINE_GUID(MGMT_BSLN.TARGET_UID(M.target_guid) ,K_TESTBSLN_NAME) /*DATASOURCE_GUID*/ ,M.DATASOURCE_GUID /*COMPUTE_DATE*/ ,SYSDATE /* SUBINTERVAL_CODE*/ ,MGMT_BSLN.SUBINTERVAL_CODE('XX',SYSDATE) /*SAMPLE_COUNT*/ ,0 /*other stats null, all number datatype*/ ,null,null,null,null ,null,null,null,null ,null,null,null,null ,null,null,null,null /*,AVERAGE,MINIMUM,MAXIMUM,SDEV ,PCTILE_25,PCTILE_50,PCTILE_75,PCTILE_90 ,PCTILE_95,EST_SAMPLE_COUNT,EST_SLOPE,EST_INTERCEPT ,EST_FIT_QUALITY,EST_PCTILE_99,EST_PCTILE_999,EST_PCTILE_9999 */ ) end --CASE bulk collect into l_statistics_set_newoutapi from TABLE(test_metrics_EM (l_metric_details_array,window_days_in,end_date_in) ) S ,(select MGMT_BSLN.DATASOURCE_GUID (MA.target_guid,MA.metric_guid,MA.key_value) as datasource_guid ,MA.target_guid from TABLE(CAST(l_metric_details_array as MGMT_METRIC_DETAILS_ARRAY) ) MA ) M where S.datasource_guid(+) = M.datasource_guid; ---------------------------------------------- -- assert a known postcondition and return ---------------------------------------------- assert(l_statistics_set_newoutapi.COUNT = metric_array_in.COUNT ,myname||':newoutapi.count = metrics_array_in.count'); RETURN l_statistics_set_newoutapi; end test_metrics_EM; ----------------------------------------------------------------------------- -- Function: test_registered_metrics_EM -- Description: Grid Control specific module that tests fitness of metrics -- already registered for metric baselines based on recent data. -- Arguments: target_guid_in - target whose registered metrics are tested -- window_days_in - defines length of data window into -- into table MGMT_METRICS_RAW for testing -- end_date_in - end date of data window for metric testing ----------------------------------------------------------------------------- function test_registered_metrics_EM (target_guid_in IN varchar2 ,window_days_in IN number := 3 ,end_date_in IN date := TRUNC(SYSDATE)+1 ) return bsln_statistics_set is myname module_name_t := 'TEST_REGISTERED_METRICS_EM'; --------------------------------------------------- l_target_guid MGMT_BSLN.guid_t; l_statistics_set bsln_statistics_set := bsln_statistics_set(); l_metric_array MGMT_METRIC_INSTANCE_ARRAY := MGMT_METRIC_INSTANCE_ARRAY(); l_window_days number; l_end_date date; l_target_name MGMT_TARGETS.target_name%TYPE; l_target_type MGMT_TARGETS.target_type%TYPE; begin ----------------------------------- -- precondition assertions ----------------------------------- assert(LENGTH(target_guid_in) = 32, myname||':target_guid_in length=32'); l_target_guid := HEXTORAW(target_guid_in); l_window_days := NVL(window_days_in,3); l_end_date := NVL(end_date_in,TRUNC(SYSDATE)+1); -------------------------------------------------------------- -- initialize target name and type or assertfail -------------------------------------------------------------- begin select target_name, target_type into l_target_name, l_target_type from mgmt_targets T where T.target_guid = l_target_guid; exception when NO_DATA_FOUND then assert(null,myname||':target not found for:'||RAWTOHEX(l_target_guid)); end; -------------------------------------------------------------- -- load l_metric_array with registered datasources -- NOTE:this procedure now calling new test_metrics_EM api -- i.e. l_metrics_array input datatype is changed -------------------------------------------------------------- select MGMT_METRIC_INSTANCE (l_target_name,l_target_type ,M.metric_name,M.metric_column ,DS.key_value) bulk collect into l_metric_array from MGMT_BSLN_DATASOURCES DS ,(select DISTINCT metric_name, metric_column, metric_guid from MGMT_METRICS where target_type = l_target_type ) M where DS.source_type = 'EM' and DS.target_guid = l_target_guid and DS.metric_guid = M.metric_guid; -------------------------------------------------------------- -- call new test_metrics_EM signature on non-empty array -------------------------------------------------------------- if l_metric_array.COUNT > 0 then l_statistics_set := test_metrics_EM (metric_array_in => l_metric_array ,window_days_in => l_window_days ,end_date_in => l_end_date ); end if; RETURN l_statistics_set; end test_registered_metrics_EM; ----------------------------------------------------------------------------- -- Procedure: target_delete_callback -- Description: Grid Control specific module that deletes baseline subsystem -- data when target is deleted. Called asynchronously from target -- deletion since rawdata may be large and delete slow. -- Arguments: target_name_in - target name to delete -- target_type_in - type of target -- target_guid_in - unique identifier of target ----------------------------------------------------------------------------- procedure target_delete_callback (target_name_in in varchar2 ,target_type_in in varchar2 ,target_guid_in in RAW) is myname module_name_t := 'TARGET_DELETE_CALLBACK'; begin assert(target_guid_in is not null,myname||':target_guid not null'); assert(target_type_in is not null,myname||':target_type not null'); assert(target_name_in is not null,myname||':target_name not null'); --------------------------------------------- -- first delete from rawdata in batch loop --------------------------------------------- loop delete from mgmt_bsln_rawdata where datasource_guid in (select datasource_guid from mgmt_bsln_datasources where target_guid = target_guid_in) and ROWNUM < 1001; exit when SQL%NOTFOUND; commit work; end loop; commit work; ----------------------------------------------- -- next delete from: -- mgmt_bsln_datasources -- this will cascade deletes to: -- mgmt_bsln_statistics -- mgmt_bsln_threshold_parms ------------------------------------------------ delete from mgmt_bsln_datasources where target_guid = target_guid_in; ------------------------------------------------ -- finally delete from: -- mgmt_bsln_baselines -- this will cascade delete to: -- mgmt_bsln_intervals ------------------------------------------------ delete from mgmt_bsln_baselines where target_uid = target_guid_in; commit work; end target_delete_callback; ----------------------------------------------------------------------------- -- Procedure: beacon_keyval_del_callback -- Description: Grid Control specific module that deletes baseline subsystem -- data when beacon keyvalue is deleted. -- Arguments: p_target_guids - list of target guids with key value -- p_metric_guid - metric guid associated with key value -- p_key_value - the key value being deleted ----------------------------------------------------------------------------- procedure beacon_keyval_del_callback (p_target_guid MGMT_BSLN.guid_t ,p_metric_guid MGMT_BSLN.guid_t ,p_key_value MGMT_BSLN.key_value_t ) is myname module_name_t := 'BEACON_KEYVAL_DEL_CALLBACK'; l_datasource_guid MGMT_BSLN.guid_t; begin assert(p_target_guid is not null,myname||':p_target_guid not null'); assert(p_metric_guid is not null,myname||':p_metric_guid not null'); assert(p_key_value is not null,myname||':p_key_value not null'); --------------------------------------------- -- get affected datasource or return --------------------------------------------- begin select datasource_guid into l_datasource_guid from mgmt_bsln_datasources DS where source_type = 'EM' and target_guid = p_target_guid and metric_guid = p_metric_guid and key_value = p_key_value; exception -- if this key value not a registered datasource then just return -- (this should be the common case) when NO_DATA_FOUND then RETURN; end; --------------------------------------------- -- delete from rawdata in batch loop --------------------------------------------- loop delete from mgmt_bsln_rawdata where datasource_guid = l_datasource_guid and ROWNUM < 1001; exit when SQL%NOTFOUND; commit work; end loop; ----------------------------------------------- -- delete from mgmt_bsln_datasources -- this will cascade deletes to: -- mgmt_bsln_statistics -- mgmt_bsln_threshold_parms ------------------------------------------------ delete from mgmt_bsln_datasources where datasource_guid = l_datasource_guid; commit work; end beacon_keyval_del_callback; ---------------------------------------------------------- -- package initialization ---------------------------------------------------------- begin null; end mgmt_bsln_internal; / show errors