Rem Rem $Header: bsln_pkgbody.sql 09-dec-2005.02:32:10 jsadras Exp $ Rem Rem bsln_pkgbody.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem bsln_pkgbody.sql - Baseline package bodies (creation). Rem Rem DESCRIPTION Rem This script contains the logic implementing the packages declared in Rem bsln_pkgdef.sql. Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem jsadras 12/09/05 - Bug:4691227, handle exception in procedure Rem called from dbms_jobs Rem jberesni 09/09/05 - fix 4598112 Rem jberesni 08/10/05 - fix 4532738 Rem jberesni 08/02/05 - fix 4522779 Rem jberesni 05/12/05 - fix 4353079 Rem jberesni 04/16/05 - merge from DB control Rem jberesni 03/05/05 - sync with DB control Rem jberesni 11/19/04 - sho err Rem jberesni 11/18/04 - refactor Rem jberesni 10/18/04 - update thresholds 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 --------------------------------- -- Grid Control deployment 0 --------------------------------- as ----------------------------------------------------------------------------- -- -- package-private subtypes -- ----------------------------------------------------------------------------- subtype module_name_t is varchar2(30); ----------------------------------------------------------------------------- -- -- package-private constants -- ----------------------------------------------------------------------------- K_MOVING_WINDOW_NAME constant varchar2(64) := 'Trailing N Days'; ----------------------------------------------------------------------------- -- -- subinterval key cache types and table -- ----------------------------------------------------------------------------- subtype codehex_t is varchar2(64); type code_set is table of mgmt_bsln.subinterval_code_t index by binary_integer; type keycode_set is table of code_set index by subinterval_key_t; keycodes_cache_tbl keycode_set; ------------------------------------------------- -- private globals for stdhh24 function cache ------------------------------------------------- P_SATURDAY_OFFSET pls_integer; type dayoff_set is table of pls_integer index by binary_integer; P_DAYOFFSET dayoff_set; ----------------------------------------------------------------------------- -- -- package-private implementation and forward declarations -- ---------------------------------------------------------------------------- procedure deactivate_baseline (name_in in mgmt_bsln_baselines.name%type ,target_uid_in in guid_t ,commit_in in boolean ); procedure assert (bool_IN IN BOOLEAN ,msg_IN IN VARCHAR2 := null); function bslnthr_count(bsln_guid_in in guid_t) return integer; ----------------------------------------------------------------------------- -- -- utility routines -- ----------------------------------------------------------------------------- ------------------------------------------------------------------- -- Procedure: load_P_DAYOFFSET -- Description: Loads mapping of day-of-week in session territory to -- standardized Saturday=0 day of week. ------------------------------------------------------------------- procedure load_P_DAYOFFSET is l_doidx binary_integer; begin ---------------------------------------------------------------- -- establish offset based on known saturday -- (NOTE: assumes constant nls_territory for life of session) ---------------------------------------------------------------- if P_SATURDAY_OFFSET is null then P_SATURDAY_OFFSET := to_number(to_char(to_date(2453190,'J'),'D')); end if; ----------------------------------------------- -- load P_DAYOFFSET for a week of days ----------------------------------------------- for d in 1..7 loop l_doidx := MOD(d+7-P_SATURDAY_OFFSET,7); P_DAYOFFSET(d) := l_doidx; end loop; end load_P_DAYOFFSET; ----------------------------------------------------- -- encoding of hour of week using DHH24 format model -- and standardized week day (0-6) with Saturday=0 -- (nls_territory-independent day numbering) ----------------------------------------------------- function stdhh24 (date_in in date) return binary_integer is myname module_name_t := 'STDHH24'; l_dhh24 binary_integer; l_doidx binary_integer; begin --assert(date_in is not null, myname||':date_in not null'); --------------------------------------------------- -- reload in case of package global memory reset --------------------------------------------------- if P_DAYOFFSET.COUNT < 7 then load_P_DAYOFFSET; end if; --------------------------------------------------- -- get standardized day of week from territory day --------------------------------------------------- l_doidx := P_DAYOFFSET(to_number(to_char(date_in,'D'))); -------------------------------------------------- -- stdday*100+hh24 = encoding -------------------------------------------------- l_dhh24 := l_doidx * 100 + to_number(to_char(date_in,'HH24')); return l_dhh24; end stdhh24; ----------------------------------------------------- -- cache-driven version of subinterval code function ----------------------------------------------------- function cached_subinterval_code (subinterval_key_in in subinterval_key_t ,time_in in date) return subinterval_code_t is myname module_name_t := 'CACHED_SUBINTERVAL_CODE'; l_dhh24 binary_integer; l_code subinterval_code_t; begin --assert(subinterval_key_in is not null, myname||':subinterval_key_in not null'); --assert(time_in is not null, myname||':time_in not null'); --assert(mgmt_bsln.validkey(subinterval_key_in, myname||':subinterval_key_in valid'); l_dhh24 := stdhh24(time_in); begin l_code := keycodes_cache_tbl(subinterval_key_in)(l_dhh24); exception when NO_DATA_FOUND then ----------------------------------------------------- -- cache miss, get value and load into cache ----------------------------------------------------- l_code := subinterval_code(subinterval_key_in, time_in); keycodes_cache_tbl(subinterval_key_in)(l_dhh24) := l_code; end; return l_code; end cached_subinterval_code; ------------------------------------------------- -- helper function to validate subinterval keys ------------------------------------------------- function valid_key (subinterval_key_in subinterval_key_t) return boolean is begin return (subinterval_key_in IN (K_BSLN_XX, K_BSLN_HX, K_BSLN_XD ,K_BSLN_HD, K_BSLN_XW, K_BSLN_HW ,K_BSLN_NW, K_BSLN_ND, K_BSLN_NX) ); end valid_key; ---------------------------------------------------------- -- Procedure: AssertX -- Description: Call assert under new name -- Arguments: bool_IN - boolean condition to test -- msg_IN - string appended to ASSERTFAIL message ---------------------------------------------------------- procedure assertX (bool_IN IN BOOLEAN ,msg_IN IN VARCHAR2 := null) is begin null; assert(bool_IN,msg_IN); end assertX; ---------------------------------------------------------- -- Function: target_uid -- Description: Encode a target uid. -- Arguments: target_guid_in - the EM target guid -- Returns: A target uid for this target ---------------------------------------------------------- function target_uid (target_guid_in in guid_t) return guid_t DETERMINISTIC is myname module_name_t := 'TARGET_UID'; begin assert(target_guid_in is not null, myname||':target_guid_in not null'); assert(UTL_RAW.LENGTH(target_guid_in)=16, myname||':length(target_guid_in)=16'); return target_guid_in; end target_uid; ---------------------------------------------------------- -- Function: target_uid -- Description: Encode a target uid -- Arguments: dbid_in - the dbid for the database -- instance_num_in - the instance's number -- Returns: A target uid for the supplied database instance -- defined by RAW(NUMBER(dbid.inst_num)) ---------------------------------------------------------- function target_uid (dbid_in in mgmt_bsln_datasources.dbid%type ,instance_num_in in mgmt_bsln_datasources.instance_num%type) return guid_t DETERMINISTIC is myname module_name_t := 'TARGET_UID'; l_guid_out guid_t; begin assert(dbid_in > 0, myname||':dbid_in>0'); assert(instance_num_in > 0, myname||':instance_num_in>0'); l_guid_out := UTL_RAW.CAST_FROM_NUMBER ( to_number ( to_char(dbid_in)||'.'||to_char(instance_num_in)||'9' ) ); assert(UTL_RAW.LENGTH(l_guid_out)<16, myname||':length(return)<16'); return l_guid_out; end target_uid; ---------------------------------------------------------- -- Function: this_target_uid -- Description: Produce this database instance's target uid. -- Arguments: (none) -- Returns: A target uid for this database instance ---------------------------------------------------------- function this_target_uid return guid_t is myname module_name_t := 'THIS_TARGET_UID'; l_target_uid guid_t; begin select target_uid(d.dbid, i.instance_number) into l_target_uid from sys.v_$database d, sys.v_$instance i; assert(l_target_uid is not null, myname||':return guid not null'); return l_target_uid; end this_target_uid; ---------------------------------------------------------- -- Function: metric_uid -- Description: Encode a metric uid. -- Arguments: metric_guid_in - an EM metric guid -- Returns: A metric uid for this metric -- -- TODO: should we validate against mgmt_metrics? ---------------------------------------------------------- function metric_uid (metric_guid_in in guid_t) return guid_t DETERMINISTIC is myname module_name_t := 'METRIC_UID'; begin assert(metric_guid_in is not null, myname||':metric_guid_in not null'); assert(UTL_RAW.LENGTH(metric_guid_in)=16, myname||':length(metric_guid_in)=16'); return metric_guid_in; end metric_uid; ---------------------------------------------------------- -- Function: metric_uid -- Description: Encode a metric uid. -- Arguments: metric_id_in - a database metric id -- Returns: A metric uid for a database metric ---------------------------------------------------------- function metric_uid (metric_id_in in mgmt_bsln_datasources.metric_id%type) return guid_t DETERMINISTIC is myname module_name_t := 'METRIC_UID'; l_uid_out guid_t; begin assert(metric_id_in is not null, myname||':metric_id_in not null'); assert(metric_id_in > 0, myname||':metric_id_in>0'); l_uid_out := UTL_RAW.CAST_FROM_NUMBER(metric_id_in); return l_uid_out; end metric_uid; ---------------------------------------------------------- -- Function: datasource_guid -- Description: Encode a datasource guid. -- Arguments: target_uid_in - the target uid -- metric_uid_in - the metric uid -- key_value_in - the key value -- Returns: A guid representing this triplet ---------------------------------------------------------- function datasource_guid (target_uid_in in guid_t ,metric_uid_in in guid_t ,key_value_in in key_value_t := K_DEFAULT_KEY_VALUE) return guid_t DETERMINISTIC is myname module_name_t := 'DATASOURCE_GUID'; l_guid_out guid_t; l_key_value key_value_t; begin assert(target_uid_in is not null, myname||':target_uid_in not null'); assert(metric_uid_in is not null, myname||':metric_uid_in not null'); -------------------------------- -- use default key_value if null -------------------------------- l_key_value := NVL(key_value_in, K_DEFAULT_KEY_VALUE); begin -------------------------------- -- generate new guid -------------------------------- l_guid_out := DBMS_OBFUSCATION_TOOLKIT.MD5 (input => UTL_RAW.CAST_TO_RAW(rawtohex(target_uid_in) || '.' || rawtohex(metric_uid_in) || '.' || l_key_value) ); end; return l_guid_out; end datasource_guid; ---------------------------------------------------------- -- Function: baseline_guid -- Description: Fetch or produce a baseline_guid for target uid and -- baseline name -- Arguments: target_uid_in - the target uid -- name_in - the baseline name -- Returns: A guid for this baseline ---------------------------------------------------------- function baseline_guid (target_uid_in in guid_t ,name_in in mgmt_bsln_baselines.name%type) return guid_t DETERMINISTIC is myname module_name_t := 'BASELINE_GUID'; l_guid_out guid_t; begin assert(target_uid_in is not null, myname||':target_uid_in not null'); assert(name_in is not null, myname||':name_in not null'); begin ---------------------------------- -- generate new guid ---------------------------------- l_guid_out := DBMS_OBFUSCATION_TOOLKIT.MD5 (input => UTL_RAW.CAST_TO_RAW(TO_CHAR(rawtohex(target_uid_in)|| '.'||name_in)) ); end; return l_guid_out; end baseline_guid; ---------------------------------------------------------- -- Function: target_source_type -- Description: Determines if target is EM or DB type based -- on registered datasources -- Arguments: target_uid_in - the target uid -- Returns: Source type: K_SOURCE_DB or K_SOURCE_EM ---------------------------------------------------------- function target_source_type (target_uid_in in mgmt_bsln.guid_t) return varchar2 is myname module_name_t := 'TARGET_SOURCE_TYPE'; l_source_type mgmt_bsln_datasources.source_type%TYPE; begin assert(target_uid_in is not null,myname||':target_uid_in not null'); begin select distinct source_type into l_source_type from mgmt_bsln_datasources where target_uid = target_uid_in; exception when TOO_MANY_ROWS then RAISE_APPLICATION_ERROR (X_SOURCE_CONFLICT,'Target source type conflict'); when NO_DATA_FOUND then RAISE_APPLICATION_ERROR (X_DATASOURCE_NOT_FOUND,'No registered datasources for target'); end; assert(l_source_type in (K_SOURCE_DB,K_SOURCE_EM), myname||':source type EM or DB'); return l_source_type; end target_source_type; ---------------------------------------------------------- -- Function: datasource_rec -- Description: Returns datasource row by guid -- Arguments: ds_guid_in - datasource guid -- Returns: Row of datasource or exception if not found ---------------------------------------------------------- function datasource_rec(ds_guid_in in guid_t) RETURN mgmt_bsln_datasources%ROWTYPE is myname module_name_t := 'DATASOURCE_REC'; l_datasource_rec mgmt_bsln_datasources%ROWTYPE; begin assert(ds_guid_in is not null, myname||':ds_guid_in not null'); select * into l_datasource_rec from mgmt_bsln_datasources where datasource_guid = ds_guid_in; RETURN l_datasource_rec; exception when NO_DATA_FOUND then RAISE_APPLICATION_ERROR(X_DATASOURCE_NOT_FOUND,'Datasource not found'); end datasource_rec; ---------------------------------------------------------- -- Function: baseline_rec -- Description: Returns baseline row by guid -- Arguments: bsln_guid_in - baseline guid -- Returns: Row of baseline table or exception if not found ---------------------------------------------------------- function baseline_rec(bsln_guid_in in guid_t) RETURN mgmt_bsln_baselines%ROWTYPE is myname module_name_t := 'BASELINE_REC'; l_bsln_rec mgmt_bsln_baselines%ROWTYPE; begin assert(bsln_guid_in is not null, myname||':bsln_guid_in not null'); select * into l_bsln_rec from mgmt_bsln_baselines where bsln_guid = bsln_guid_in; RETURN l_bsln_rec; exception when NO_DATA_FOUND then RAISE_APPLICATION_ERROR(X_BASELINE_NOT_FOUND,'Baseline not found'); end; ----------------------------------------------------------------------------- -- -- administration routines -- ----------------------------------------------------------------------------- ---------------------------------------------------------- -- Procedure: create_baseline_prvt -- Description: Private procedure to create baselines, -- called by publicly exposed procedures. ---------------------------------------------------------- procedure create_baseline_prvt (bsln_type_in in mgmt_bsln_baselines.type%type ,name_in in mgmt_bsln_baselines.name%type ,interval_begin_in in date := null ,interval_end_in in date := null ,interval_days_in in number := null ,subinterval_key_in in subinterval_key_t ,target_uid_in in guid_t := null ,compute_stats_TF in boolean := FALSE ) is myname module_name_t := 'CREATE_BASELINE_PRVT'; l_target_uid guid_t; l_bsln_guid guid_t; l_statistics_set bsln_statistics_set; valid_static_interval boolean := (bsln_type_in = K_BSLN_STATIC and (interval_begin_in is not null and interval_end_in is not null) ); valid_rolling_interval boolean := (bsln_type_in = K_BSLN_ROLLING and interval_days_in is not null); begin assert(name_in is not null, myname||':name_in not null'); assert(valid_key(subinterval_key_in), myname||':valid_key('||subinterval_key_in||')'); assert(bsln_type_in in (K_BSLN_STATIC,K_BSLN_ROLLING), myname||':bsln_type_in validate:'||bsln_type_in); assert(valid_static_interval or valid_rolling_interval, myname||':validate interval'); -------------------------------- -- Translate null target_uid to the local target. -------------------------------- l_target_uid := NVL(target_uid_in,this_target_uid); --------------------------------- -- get baseline identifier --------------------------------- l_bsln_guid := baseline_guid(l_target_uid, name_in); -------------------------------- -- Add baseline row -------------------------------- insert into mgmt_bsln_baselines (bsln_guid ,target_uid ,name ,type ,subinterval_key ,status ) values (l_bsln_guid ,l_target_uid ,name_in ,bsln_type_in ,subinterval_key_in ,K_STATUS_INACTIVE ); --------------------------------------- -- Add interval row for this baseline --------------------------------------- insert into mgmt_bsln_intervals (bsln_guid ,interval_begin ,interval_end ,interval_days ) values (l_bsln_guid ,decode(bsln_type_in, K_BSLN_STATIC, interval_begin_in, to_date(null)) ,decode(bsln_type_in, K_BSLN_STATIC, interval_end_in, to_date(null)) ,decode(bsln_type_in, K_BSLN_ROLLING, interval_days_in, to_number(null)) ); ------------------------------------------------------------- -- Compute stats if requested (static only? not necessarily) ------------------------------------------------------------- if NVL(compute_stats_TF,FALSE) then mgmt_bsln_internal.compute_load_stats (compute_date_in => TRUNC(sysdate) ,bsln_guid_in => l_bsln_guid ); end if; -- commit; removed and responsibility handed to callers exception when DUP_VAL_ON_INDEX then rollback; RAISE_APPLICATION_ERROR(X_INVALID_BASELINE, 'Duplicate baseline name: '||name_in); end create_baseline_prvt; ---------------------------------------------------------- -- Procedure: create_baseline_static -- Description: Create a static baseline. -- Arguments: name_in - baseline name -- subinterval_key - key to time partitioning method -- interval_begin_in - single fixed interval begin time -- interval_end_in - single fixed interval end time -- target_uid_in - target uid -- Exceptions: -- TODO: work out externalized exceptions ---------------------------------------------------------- procedure create_baseline_static (name_in in mgmt_bsln_baselines.name%type ,interval_begin_in in date ,interval_end_in in date ,subinterval_key_in in subinterval_key_t ,target_uid_in in guid_t := null ) is myname module_name_t := 'CREATE_BASELINE_STATIC'; begin assert(name_in is not null, myname||':name_in not null'); assert(interval_begin_in is not null, myname||':interval_begin_in not null'); assert(interval_end_in is not null, myname||':interval_end_in not null'); assert(interval_end_in - interval_begin_in >=7, myname||':interval >=7 days'); assert(subinterval_key_in is not null, myname||':subinterval_key_in not null'); ------------------------------------ -- call common private procedure ------------------------------------ create_baseline_prvt (bsln_type_in => K_BSLN_STATIC ,name_in => name_in ,interval_begin_in => interval_begin_in ,interval_end_in => interval_end_in ,subinterval_key_in => subinterval_key_in ,target_uid_in => target_uid_in ,compute_stats_TF => TRUE ); ------------------------------------------------ -- NOTE: commit/rollback removed from shared prvt module ------------------------------------------------ commit; exception when others then rollback; raise; end create_baseline_static; ---------------------------------------------------------- -- Procedure: create_baseline_rolling -- Description: Create a rolling baseline. -- Arguments: name_in - baseline name -- subdivision_in - subdivision method -- interval_days_in - size of rolling interval, in days -- target_uid_in - target uid -- Exceptions: TBD ---------------------------------------------------------- procedure create_baseline_rolling (name_in in mgmt_bsln_baselines.name%type ,subinterval_key_in in subinterval_key_t ,interval_days_in in number ,target_uid_in in guid_t := null ) is myname module_name_t := 'CREATE_BASELINE_ROLLING'; begin assert(name_in is not null, myname||':name_in not null'); assert(subinterval_key_in is not null, myname||':subinterval_key_in not null'); assert(interval_days_in >= 7, myname||':interval_days('||interval_days_in||') >=7'); ------------------------------------ -- call common private procedure ------------------------------------ create_baseline_prvt (bsln_type_in => K_BSLN_ROLLING ,name_in => name_in ,interval_days_in => interval_days_in ,subinterval_key_in => subinterval_key_in ,target_uid_in => target_uid_in ,compute_stats_TF => FALSE ); ------------------------------------------------ -- NOTE: commit/rollback removed from shared prvt module ------------------------------------------------ commit; exception when others then rollback; raise; end create_baseline_rolling; ---------------------------------------------------------- -- Procedure: update_moving_window -- Description: Update (or create) THE moving window baseline. -- Arguments: interval_days_in - size of the window in days -- subinterval_key_in - the code for time partitioning -- target_uid_in - target uid -- Exceptions: TBD ---------------------------------------------------------- procedure update_moving_window (interval_days_in in number ,subinterval_key_in in subinterval_key_t ,target_uid_in in guid_t := null ) is myname module_name_t := 'UPDATE_MOVING_WINDOW'; l_target_uid guid_t; l_bsln_rec mgmt_bsln_baselines%ROWTYPE; l_bsln_guid guid_t; l_interval_days integer; begin assert(subinterval_key_in is not null, myname||':subinterval_key_in not null'); assert(interval_days_in >= 7, myname||':interval_days('||interval_days_in||') >=7'); assert(valid_key(subinterval_key_in), myname||':valid_key('||subinterval_key_in||')'); ---------------------------------------------------- -- default to local target ---------------------------------------------------- l_target_uid := NVL(target_uid_in, this_target_uid); ---------------------------------------------------- -- get baseline_guid for THE moving window ---------------------------------------------------- l_bsln_guid := baseline_guid (target_uid_in => l_target_uid ,name_in => K_MOVING_WINDOW_NAME ); ---------------------------------------------------- -- fetch current row for the moving window, -- if not found call local create procedure -- but do not load stats there -- note: re-fetch in exception handler ---------------------------------------------------- begin l_bsln_rec := baseline_rec(l_bsln_guid); EXCEPTION when BASELINE_NOT_FOUND then create_baseline_prvt (bsln_type_in => K_BSLN_ROLLING ,name_in => K_MOVING_WINDOW_NAME ,interval_days_in => interval_days_in ,subinterval_key_in => subinterval_key_in ,target_uid_in => l_target_uid ,compute_stats_TF => FALSE ); l_bsln_rec := baseline_rec(l_bsln_guid); end; ----------------------------------------------------- -- check in case there is static baseline with name -- collision on K_MOVING_WINDOW_NAME ----------------------------------------------------- assert(l_bsln_rec.type = K_BSLN_ROLLING, myname||':baseline '||K_MOVING_WINDOW_NAME|| ' must be of type rolling ('||K_BSLN_ROLLING||')' ); ----------------------------------------------------- -- fetch the interval days for this baseline -- this will assertfail or too_many_rows fail -- unless there is exactly one interval ----------------------------------------------------- begin select interval_days into l_interval_days from mgmt_bsln_intervals BI where BI.bsln_guid = l_bsln_guid; assert(l_interval_days > 0, myname||':baseline interval_days>0'); end; ----------------------------------------------------- -- update THE moving window baseline if properties -- changed ----------------------------------------------------- if l_interval_days <> interval_days_in OR l_bsln_rec.subinterval_key <> subinterval_key_in then update mgmt_bsln_baselines set subinterval_key = subinterval_key_in where bsln_guid = l_bsln_guid; update mgmt_bsln_intervals set interval_days = interval_days_in where bsln_guid = l_bsln_guid; -------------------------------------------------- -- compute and re-load stats if properties changed -- AND this baseline is ACTIVE -------------------------------------------------- if l_bsln_rec.status = K_STATUS_ACTIVE then mgmt_bsln_internal.compute_load_stats (compute_date_in => TRUNC(sysdate) ,bsln_guid_in => l_bsln_guid ); end if; end if; ----------------------------------------------------- -- commit work here ----------------------------------------------------- commit; exception when others then rollback; raise; end update_moving_window; ---------------------------------------------------------- -- Procedure: drop_baseline -- Description: Drop an inactive baseline. -- Active baseline not dropped as delete cascade -- might cause unintended data loss. -- Arguments: name_in - the baseline name -- target_uid_in - the target uid -- Exceptions: TBD ---------------------------------------------------------- procedure drop_baseline (name_in in mgmt_bsln_baselines.name%type ,target_uid_in in guid_t := null ) is myname module_name_t := 'DROP_BASELINE'; l_target_uid guid_t; l_bsln_guid guid_t; l_bsln_status mgmt_bsln_baselines.status%TYPE; begin assert(name_in is not null, myname||':name_in not null'); -------------------------------- -- Translate null target_uid to the local target -------------------------------- l_target_uid := NVL(target_uid_in,this_target_uid); -------------------------------- -- Get guid and status of baseline -------------------------------- select bsln_guid ,status into l_bsln_guid ,l_bsln_status from mgmt_bsln_baselines where name = name_in and target_uid = l_target_uid; if (l_bsln_status = K_STATUS_INACTIVE) then -------------------------------- -- Delete the baseline only if it is inactive -------------------------------- delete from mgmt_bsln_baselines where bsln_guid = l_bsln_guid; else RAISE_APPLICATION_ERROR (X_INVALID_BASELINE, 'Cannot drop active baseline '''||name_in||''''); end if; commit; exception when NO_DATA_FOUND then rollback; RAISE_APPLICATION_ERROR (X_INVALID_BASELINE, 'Baseline '''||name_in||''' not found'); when others then rollback; raise; end drop_baseline; ---------------------------------------------------------- -- Procedure: register_datasource_prvt -- Description: Private datasource registration. -- Exceptions: TBD ---------------------------------------------------------- procedure register_datasource_prvt (datasource_guid_in in guid_t ,source_type_in in mgmt_bsln_datasources.source_type%type ,target_uid_in in guid_t ,metric_uid_in in guid_t ,target_guid_in in guid_t ,metric_guid_in in guid_t ,key_value_in in key_value_t ,dbid_in in mgmt_bsln_datasources.dbid%type ,instance_num_in in mgmt_bsln_datasources.instance_num%type ,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 := 'REGISTER_DATASOURCE_PRVT'; begin assert(datasource_guid_in is not null, myname||':datasource_guid_in not null'); assert(source_type_in is not null, myname||':source_type_in not null'); assert(target_uid_in is not null, myname||':target_uid_in not null'); assert(metric_uid_in is not null, myname||':metric_uid_in not null'); assert(source_type_in in (K_SOURCE_EM, K_SOURCE_DB), myname||':source_type_in validation'); assert(key_value_in is not null, myname||':key_value_in not null'); -------------------------------- -- Record the data source -------------------------------- begin insert into mgmt_bsln_datasources (datasource_guid ,source_type ,target_uid ,metric_uid ,target_guid ,metric_guid ,key_value ,dbid ,instance_num ,instance_name ,metric_id ) select datasource_guid_in ,source_type_in ,target_uid_in ,metric_uid_in ,target_guid_in ,metric_guid_in ,key_value_in ,dbid_in ,instance_num_in ,instance_name_in ,metric_id_in from mgmt_bsln_metrics m where m.metric_uid = metric_uid_in; if (SQL%ROWCOUNT = 0) then RAISE_APPLICATION_ERROR (X_INVALID_METRIC,'Metric UID not baseline eligible'); end if; commit; --------------------------------- -- ignore duplicate registration --------------------------------- exception when DUP_VAL_ON_INDEX then rollback; end; end register_datasource_prvt; ---------------------------------------------------------- -- Procedure: register_datasource -- Description: Register EM datasource for baselines. -- Arguments: target_guid_in - EM's target guid -- metric_guid_in - EM's metric guid -- key_value_in - key value -- Exceptions: TBD ---------------------------------------------------------- procedure register_datasource (target_guid_in in guid_t ,metric_guid_in in guid_t ,key_value_in in key_value_t := K_DEFAULT_KEY_VALUE ) is myname module_name_t := 'REGISTER_DATASOURCE'; l_target_uid guid_t; l_metric_uid guid_t; l_datasource_guid guid_t; l_key_value key_value_t; 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'); --------------------------------------------------- -- Create target, metric and datasource identifiers --------------------------------------------------- l_target_uid := target_uid(target_guid_in); l_metric_uid := metric_uid(metric_guid_in); ------------------------------------- -- Default key_value if null input ------------------------------------- l_key_value := NVL(key_value_in, K_DEFAULT_KEY_VALUE); ------------------------------------- -- Obtain datasource guid ------------------------------------- l_datasource_guid := datasource_guid(target_uid_in => l_target_uid ,metric_uid_in => l_metric_uid ,key_value_in => l_key_value); --------------------------------------------------- -- Call common private registry procedure --------------------------------------------------- register_datasource_prvt (datasource_guid_in => l_datasource_guid ,source_type_in => K_SOURCE_EM ,target_uid_in => l_target_uid ,metric_uid_in => l_metric_uid ,target_guid_in => target_guid_in ,metric_guid_in => metric_guid_in ,key_value_in => l_key_value ,dbid_in => null ,instance_num_in => null ,instance_name_in => null ,metric_id_in => null ); ----------------------------------------------- -- commit/rollback logic in shared prvt module ----------------------------------------------- end register_datasource; ---------------------------------------------------------- -- Procedure: register_datasource -- Description: Register a datasource for inclusion in baselines. -- Arguments: dbid_in - DB's dbid -- instance_number_in - DB instance's number -- metric_id_in - (sys)metric id -- Exceptions: TBD -- TODO: What about instance name? ---------------------------------------------------------- procedure register_datasource (dbid_in in mgmt_bsln_datasources.dbid%type ,instance_num_in in mgmt_bsln_datasources.instance_num%type ,metric_id_in in mgmt_bsln_datasources.metric_id%type ) is myname module_name_t := 'REGISTER_DATASOURCE'; l_target_uid guid_t; l_metric_uid guid_t; l_datasource_guid guid_t; l_key_value key_value_t; l_instance_name varchar2(64); begin assert(dbid_in > 0, myname||':dbid_in('||dbid_in||') >0' ); assert(instance_num_in > 0, myname||':instance_num_in('||instance_num_in||') >0' ); assert(metric_id_in > 0, myname||':metric_id_in('||metric_id_in||') >0' ); --------------------------------------------------- -- Create target, metric and datasource identifiers --------------------------------------------------- l_target_uid := target_uid(dbid_in, instance_num_in); l_metric_uid := metric_uid(metric_id_in); ------------------------------------- -- Use default key_value ------------------------------------- l_key_value := K_DEFAULT_KEY_VALUE; ------------------------------------- -- get instance name ------------------------------------- select instance_name into l_instance_name from sys.gv_$instance where instance_number = instance_num_in; ------------------------------------- -- Obtain datasource guid ------------------------------------- l_datasource_guid := datasource_guid(target_uid_in => l_target_uid ,metric_uid_in => l_metric_uid ,key_value_in => l_key_value); --------------------------------------------------- -- Call common private registry procedure --------------------------------------------------- register_datasource_prvt (datasource_guid_in => l_datasource_guid ,source_type_in => K_SOURCE_DB ,target_uid_in => l_target_uid ,metric_uid_in => l_metric_uid ,target_guid_in => null ,metric_guid_in => null ,key_value_in => l_key_value ,dbid_in => dbid_in ,instance_num_in => instance_num_in ,instance_name_in => l_instance_name ,metric_id_in => metric_id_in ); ----------------------------------------------- -- commit/rollback logic in shared prvt module ----------------------------------------------- end register_datasource; ---------------------------------------------------------- -- Function: registered_ds_guid_prvt -- Description: Lookup a datasource registration. -- Arguments: target_uid_in - universal target id -- metric_uid_in - universal metric id -- key_value_in - key value -- Returns: Looked-up datasource guid for either EM or -- DB sourcing. ---------------------------------------------------------- function registered_ds_guid_prvt (target_uid_in in guid_t ,metric_uid_in in guid_t ,key_value_in in key_value_t ,source_type_in in mgmt_bsln_datasources.source_type%type) return guid_t is myname module_name_t := 'REGISTERED_DS_GUID_PRVT'; l_ds_guid guid_t; begin assert(target_uid_in is not null, myname||':target_uid_in not null'); assert(metric_uid_in is not null, myname||':metric_uid_in not null'); assert(key_value_in is not null, myname||':key_value_in not null'); assert(source_type_in in (K_SOURCE_EM, K_SOURCE_DB), myname||':source_type_in valid'); select datasource_guid into l_ds_guid from mgmt_bsln_datasources where source_type = source_type_in and target_uid = target_uid_in and metric_uid = metric_uid_in and key_value = key_value_in; return l_ds_guid; exception when NO_DATA_FOUND then RAISE_APPLICATION_ERROR (X_DATASOURCE_NOT_FOUND,'Datasource not found'); end registered_ds_guid_prvt; ---------------------------------------------------------- -- Function: registered_ds_guid -- Description: Lookup a datasource registration. -- Arguments: target_guid_in - EM's target guid -- metric_guid_in - EM's metric guid -- key_value_in - key value -- Returns: Looked-up datasource guid ---------------------------------------------------------- function registered_ds_guid (target_guid_in in guid_t ,metric_guid_in in guid_t ,key_value_in in key_value_t := K_DEFAULT_KEY_VALUE) return guid_t is myname module_name_t := 'REGISTERED_DS_GUID'; l_ds_guid guid_t; l_key_value key_value_t; 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'); ------------------------------------- -- Default key_value if null input ------------------------------------- l_key_value := NVL(key_value_in, K_DEFAULT_KEY_VALUE); ------------------------------------- -- call common private function ------------------------------------- l_ds_guid := registered_ds_guid_prvt (target_uid_in => target_uid(target_guid_in) ,metric_uid_in => metric_uid(metric_guid_in) ,key_value_in => l_key_value ,source_type_in => K_SOURCE_EM ); return l_ds_guid; end registered_ds_guid; ---------------------------------------------------------- -- Function: registered_ds_guid -- Description: Lookup a DB registered datasource. -- Arguments: dbid_in - DB's id -- instance_number_in - DB's instance number -- metric_id_in - (sys)metric id -- Returns: Looked-up datasource registration ---------------------------------------------------------- function registered_ds_guid (dbid_in in mgmt_bsln_datasources.dbid%type ,instance_num_in in mgmt_bsln_datasources.instance_num%type ,metric_id_in in mgmt_bsln_datasources.metric_id%type) return guid_t is myname module_name_t := 'REGISTERED_DS_GUID'; l_ds_guid guid_t; l_key_value key_value_t; begin assert(dbid_in > 0, myname||':dbid_in('||dbid_in||') >0'); assert(instance_num_in > 0, myname||':instance_num_in('||instance_num_in||') >0'); assert(metric_id_in > 0, myname||':metric_id_in('||metric_id_in||') >0'); ------------------------------------- -- Use default key_value ------------------------------------- l_key_value := K_DEFAULT_KEY_VALUE; ------------------------------------- -- call common private function ------------------------------------- l_ds_guid := registered_ds_guid_prvt (target_uid_in => target_uid(dbid_in, instance_num_in) ,metric_uid_in => metric_uid(metric_id_in) ,key_value_in => l_key_value ,source_type_in => K_SOURCE_DB ); return l_ds_guid; end registered_ds_guid; ---------------------------------------------------------- -- Procedure: deregister_datasource -- Description: Remove an EM datasource registration. -- Arguments: target_guid_in - EM's target guid -- metric_guid_in - EM's metric guid -- key_value_in - key value -- Exceptions: TBD ---------------------------------------------------------- procedure deregister_datasource (target_guid_in in guid_t ,metric_guid_in in guid_t ,key_value_in in key_value_t := K_DEFAULT_KEY_VALUE) is myname module_name_t := 'DEREGISTER_DATASOURCE'; l_ds_guid guid_t; l_key_value key_value_t; 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'); ------------------------------------- -- Default key_value if null input ------------------------------------- l_key_value := NVL(key_value_in, K_DEFAULT_KEY_VALUE); -------------------------------------------- -- get datasource_guid or raise exception -------------------------------------------- l_ds_guid := registered_ds_guid (target_guid_in => target_guid_in ,metric_guid_in => metric_guid_in ,key_value_in => l_key_value); ------------------------------- -- delete datasource ------------------------------- delete from mgmt_bsln_datasources where datasource_guid = l_ds_guid; commit; end deregister_datasource; ---------------------------------------------------------- -- Procedure: deregister_datasource -- Description: Remove a DB datasource registration -- Arguments: dbid_in - DB's id -- instance_number_in - DB instance's number -- metric_id_in - (sys)metric id -- Exceptions: TBD ---------------------------------------------------------- procedure deregister_datasource (dbid_in in mgmt_bsln_datasources.dbid%type ,instance_num_in in mgmt_bsln_datasources.instance_num%type ,metric_id_in in mgmt_bsln_datasources.metric_id%type) is myname module_name_t := 'DEREGISTER_DATASOURCE'; l_ds_guid guid_t; begin assert(dbid_in > 0, myname||':dbid_in not null'); assert(instance_num_in > 0, myname||':instance_num_in not null'); assert(metric_id_in > 0, myname||':metric_guid_in not null'); -------------------------------------------- -- get datasource_guid or raise exception -------------------------------------------- l_ds_guid := registered_ds_guid (dbid_in => dbid_in ,instance_num_in => instance_num_in ,metric_id_in => metric_id_in); ------------------------------- -- delete datasource ------------------------------- delete from mgmt_bsln_datasources where datasource_guid = l_ds_guid; commit; end deregister_datasource; ---------------------------------------------------------- -- Function: bslnthr_count -- Description: counts number of threshold parms set for -- baseline. -- Arguments: bsln_guid_in - baseline identifier -- Return: integer count of number of threshold parms -- set for baseline ---------------------------------------------------------- function bslnthr_count(bsln_guid_in in guid_t) return integer is myname module_name_t := 'BSLNTHR_COUNT'; l_bslnthr_count integer := 0; begin assert(bsln_guid_in is not null,myname||':bsln_guid_in not null'); select count(*) into l_bslnthr_count from mgmt_bsln_threshold_parms where bsln_guid = bsln_guid_in; RETURN l_bslnthr_count; end bslnthr_count; ---------------------------------------------------------- -- Procedure: activate_baseline -- Description: Activate a baseline (and deactivate any other active -- baseline). -- Arguments: name_in - baseline name -- target_uid_in - target_uid -- Exceptions: TBD ---------------------------------------------------------- procedure activate_baseline (name_in in mgmt_bsln_baselines.name%type ,target_uid_in in guid_t := null ) is myname module_name_t := 'ACTIVATE_BASELINE'; l_target_uid guid_t; l_bsln_guid guid_t; l_bsln_type mgmt_bsln_baselines.type%type; begin assert(name_in is not null, myname||'name_in not null'); -------------------------------- -- Translate null target_uid to the local target -------------------------------- l_target_uid := NVL(target_uid_in,this_target_uid); --------------------------------------------- -- Deactivate ANY active baselines on target --------------------------------------------- for bsln_rec in (select name from mgmt_bsln_baselines where target_uid = l_target_uid and status = K_STATUS_ACTIVE) loop deactivate_baseline (name_in => bsln_rec.name ,target_uid_in => l_target_uid ,commit_in => FALSE ); end loop; ----------------------------------------------- -- Set the status of THIS baseline to active ----------------------------------------------- update mgmt_bsln_baselines set status = K_STATUS_ACTIVE where target_uid = l_target_uid and name = name_in returning bsln_guid, type into l_bsln_guid, l_bsln_type; if (SQL%ROWCOUNT = 0) then -------------------------------- -- Baseline not found -------------------------------- rollback; RAISE_APPLICATION_ERROR (X_INVALID_BASELINE, 'Baseline '''||name_in||''' not found for target'); else ---------------------------------------------- -- if ROLLING then compute current stats now ---------------------------------------------- if l_bsln_type = K_BSLN_ROLLING then -------------------------------------------------- -- call compute_load_stats to get current stats -- (NOTE: compute_load_stats does a commit) -------------------------------------------------- mgmt_bsln_internal.compute_load_stats (compute_date_in => TRUNC(SYSDATE) ,bsln_guid_in => l_bsln_guid ); end if; -- rolling baseline -------------------------------------------------- -- update thresholds immediately (if necessary) -------------------------------------------------- if bslnthr_count(l_bsln_guid) > 0 then mgmt_bsln_internal.set_all_thresholds(l_bsln_guid); end if; end if; -- baseline found commit; exception when others then rollback; raise; end activate_baseline; ---------------------------------------------------------- -- Procedure: deactivate_baseline -- Description: Deactivate an active baseline. -- Arguments: name_in - baseline name -- target_uid_in - target uid -- commit_in - conditional txn control boolean -- Exceptions: TBD -- NOTE: package internal version with txn control ---------------------------------------------------------- procedure deactivate_baseline (name_in in mgmt_bsln_baselines.name%type ,target_uid_in in guid_t ,commit_in in boolean ) is myname module_name_t := 'DEACTIVATE_BASELINE'; l_target_uid guid_t; l_bsln_rec mgmt_bsln_baselines%ROWTYPE; begin assert(name_in is not null, myname||'name_in not null'); -------------------------------------------------- -- Translate null target_uid to the local target ------------------------------------------------- l_target_uid := NVL(target_uid_in,this_target_uid); ---------------------------------------------------- -- get current baseline rec using target_uid,name ---------------------------------------------------- l_bsln_rec := baseline_rec(baseline_guid(l_target_uid,name_in)); -------------------------------------------------- -- update status and unset thresholds if this is -- ACTIVE baseline -------------------------------------------------- if l_bsln_rec.status = K_STATUS_ACTIVE then ---------------------------------- -- sub-block controls transaction ---------------------------------- BEGIN ---------------------------- -- set status inactive ---------------------------- update mgmt_bsln_baselines set status = K_STATUS_INACTIVE where name = name_in and target_uid = l_target_uid; ------------------------------------------------ -- unset thresholds controlled by this baseline ------------------------------------------------ if bslnthr_count(l_bsln_rec.bsln_guid) > 0 then mgmt_bsln_internal.unset_all_thresholds(l_bsln_rec.bsln_guid); end if; ------------------------------- -- conditional txn control ------------------------------- if commit_in then COMMIT; end if; EXCEPTION when others then if commit_in then ROLLBACK; end if; raise; END; end if; end deactivate_baseline; ---------------------------------------------------------- -- Procedure: deactivate_baseline -- Description: Deactivate an active baseline. -- Arguments: name_in - baseline name -- target_uid_in - target uid -- Exceptions: TBD -- NOTE: overloaded version exposed externally ---------------------------------------------------------- procedure deactivate_baseline (name_in in mgmt_bsln_baselines.name%type ,target_uid_in in guid_t := null ) is begin ----------------------------------------------- -- call overloaded version with commit_in TRUE ----------------------------------------------- deactivate_baseline (name_in => name_in ,target_uid_in => target_uid_in ,commit_in => TRUE ); end deactivate_baseline; ---------------------------------------------------------- -- Function: baseline_is_active -- Description: Tests if specified baseline has status ACTIVE. -- Arguments: bsln_guid_in - unique ID for baseline -- Returns: TRUE if bsln_guid_in has status ACTIVE ---------------------------------------------------------- function baseline_is_active (bsln_guid_in in guid_t) return boolean is l_bsln_guid guid_t; begin --------------------------------- -- find it or not as active --------------------------------- select bsln_guid into l_bsln_guid from mgmt_bsln_baselines where bsln_guid = bsln_guid_in and status = K_STATUS_ACTIVE; ----------------------- -- found = return true ----------------------- return TRUE; exception ----------------------------------------------- -- not found = not active (i.e. return false) ----------------------------------------------- when NO_DATA_FOUND then return FALSE; end baseline_is_active; ---------------------------------------------------------- -- 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 begin -- pass-thru to internal deployment specific code mgmt_bsln_internal.unset_threshold_parameters (bsln_guid_in => bsln_guid_in ,ds_guid_in => ds_guid_in); commit; exception when others then rollback; raise; end unset_threshold_parameters; ---------------------------------------------------------- -- 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 mgmt_bsln_threshold_parms.threshold_method%type ,warning_param_in in mgmt_bsln_threshold_parms.warning_param%type ,critical_param_in in mgmt_bsln_threshold_parms.critical_param%type ,num_occurs_in in integer := K_DEFAULT_NUM_OCCURS ,fail_action_in in fail_action_t := K_FAIL_ACTION_UNSET ) is myname module_name_t := 'SET_THRESHOLD_PARAMETERS'; begin ------------------------------------------------------- -- pass-thru to internal (deployment-specific) version ------------------------------------------------------- MGMT_BSLN_INTERNAL.set_threshold_parameters (bsln_guid_in => bsln_guid_in ,ds_guid_in => ds_guid_in ,threshold_method_in => threshold_method_in ,warning_param_in => warning_param_in ,critical_param_in => critical_param_in ,num_occurs_in => num_occurs_in ,fail_action_in => fail_action_in ); commit; -- exception -- when others -- then rollback; raise; end set_threshold_parameters; ----------------------------------------------------------------------------- -- -- operational routines -- ----------------------------------------------------------------------------- --------------------------------------------------------------------------- -- Procedure: set_all_thresholds -- Description: Set thresholds using current active baseline statistics -- on all datasources with threshold parameters. -- Calls the internal implementation as a pass-thru. -- Arguments: none -- NOTE: This procedure is intended to run as an hourly job in either -- DBMS_JOB or DBMS_SCHEDULER, not client invoked. --------------------------------------------------------------------------- procedure set_all_thresholds is begin mgmt_bsln_internal.set_all_thresholds; commit; exception when others then rollback; mgmt_log.log_error('SET ADAPTIVE THRESHOLDS', sqlcode, substr(sqlerrm, 1, 1000) ); end set_all_thresholds; ------------------------------------------------------------------------- -- Procedure: submit_bsln_jobs -- Description: Pass-thru to mgmt_bsln_internal procedure to -- submit compute and threshold set jobs. -- Arguments: none -------------------------------------------------------------------------- procedure submit_bsln_jobs is begin mgmt_bsln_internal.submit_bsln_jobs; -- commit; -- bug 4532738 interferes with savepoint end submit_bsln_jobs; ------------------------------------------------------------------------- -- Procedure: delete_bsln_jobs -- Description: Pass-thru to mgmt_bsln_internal procedure to remove -- baseline compute and threshold set jobs. -- Arguments: none -------------------------------------------------------------------------- procedure delete_bsln_jobs is begin mgmt_bsln_internal.delete_bsln_jobs; --commit; -- bug 4532738 interferes with savepoint end delete_bsln_jobs; ------------------------------------------------------------------------- -- Function: valid_source_type -- Description: Helper function to assert valid source types -- Arguments: source_type_in - must be K_SOURCE_EM or K_SOURCE_DB ------------------------------------------------------------------------- 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: subinterval_code -- Description: Choose the subinterval code for this time in the -- context of the supplied baseline. -- Arguments: subinterval_key_in - the subinterval method -- time_in - the reference time -- bsln_guid_in - the baseline (=>territory) -- context -- Returns: The subinterval encoding for the set of subintervals -- that include the reference time -- ---------------------------------------------------------- function subinterval_code (subinterval_key_in in subinterval_key_t ,time_in in date) return subinterval_code_t is myname module_name_t := 'SUBINTERVAL_CODE'; l_HH24 char(2); l_D char(2); l_W char(2); l_N char(2); l_char_code char(5); l_code_out subinterval_code_t; l_dy binary_integer; begin assert(time_in is not null, myname||':time_in not null'); --assert(subinterval_key_in is not null, myname||':subinterval_key_in not null'); assert(mgmt_bsln.valid_key(subinterval_key_in), myname||':valid_key('||subinterval_key_in||')'); ----------------------------------------- -- make sure day mapping is loaded ----------------------------------------- if P_DAYOFFSET.COUNT < 7 then load_P_DAYOFFSET; end if; --------------------------------------------------- -- get standardized day of week from territory day --------------------------------------------------- l_dy := P_DAYOFFSET(to_number(to_char(time_in,'D'))); --------------------------------- -- build code components --------------------------------- l_D := LPAD(l_dy,2,'0'); l_HH24 := to_char(time_in,'HH24'); if l_D in ('00','01') -- Saturday+Sunday=weekend (first implementation) then l_W := 'WE'; else l_W := 'WD'; end if; if to_number(l_HH24) between 7 and 18 -- day = 7am-7pm (first implementation) then l_N := 'DY'; else l_N := 'NT'; end if; ---------------------------------- -- assemble codes ---------------------------------- if subinterval_key_in = mgmt_bsln.K_BSLN_NW -- day/night and weekend/weekday then l_char_code := l_N||':'||l_W; elsif subinterval_key_in = mgmt_bsln.K_BSLN_ND -- day/night and day of week then l_char_code := l_N||':'||l_D; elsif subinterval_key_in = mgmt_bsln.K_BSLN_NX -- day/night only then l_char_code := l_N||':XX'; elsif subinterval_key_in = mgmt_bsln.K_BSLN_HW -- hour and weekend/weekday then l_char_code := l_HH24||':'||l_W; elsif subinterval_key_in = mgmt_bsln.K_BSLN_HX -- hour only then l_char_code := l_HH24||':XX'; elsif subinterval_key_in = mgmt_bsln.K_BSLN_HD -- hour and day then l_char_code := l_HH24||':'||l_D; elsif subinterval_key_in = mgmt_bsln.K_BSLN_XW -- weekend/weekday then l_char_code := 'XX:'||l_W; elsif subinterval_key_in = mgmt_bsln.K_BSLN_XD -- day only then l_char_code := 'XX:'||l_D; elsif subinterval_key_in = mgmt_bsln.K_BSLN_XX -- single subinterval then l_char_code := 'XX:XX'; else ----------------------- -- signal error ----------------------- null; assert(FALSE, myname||':subinterval_code:subinterval_key_in unknown'); end if; l_code_out := UTL_RAW.CAST_TO_RAW(l_char_code); return l_code_out; end subinterval_code; function target_is_DB (target_uid_in in mgmt_bsln.guid_t) return boolean is begin return mgmt_bsln.K_SOURCE_DB = mgmt_bsln.target_source_type(target_uid_in); end target_is_DB; function target_is_EM (target_uid_in in mgmt_bsln.guid_t) return boolean is begin return mgmt_bsln.K_SOURCE_EM = mgmt_bsln.target_source_type(target_uid_in); end target_is_EM; procedure compute_load_stats_AR (compute_date_in in date ,bsln_guid_in in guid_t := null) is myname module_name_t := 'COMPUTE_LOAD_STATS'; --stats_rec bsln_statistics_t; stats_rec mgmt_bsln_statistics%ROWTYPE; begin assert(compute_date_in is not null, myname||':compute_date_in not null'); -------------------------------- -- close cursor if open -------------------------------- if MGMT_BSLN_INTERNAL.compute_load_stats_ARcur%ISOPEN then close MGMT_BSLN_INTERNAL.compute_load_stats_ARcur; end if; open MGMT_BSLN_INTERNAL.compute_load_stats_ARcur(compute_date_in, bsln_guid_in); loop fetch MGMT_BSLN_INTERNAL.compute_load_stats_ARcur into stats_rec; exit when MGMT_BSLN_INTERNAL.compute_load_stats_ARcur%NOTFOUND; ------------------------------------------- -- try update first, not keeping history ------------------------------------------- begin update mgmt_bsln_statistics S set compute_date = stats_rec.compute_date ,sample_count = stats_rec.sample_count ,average = stats_rec.average ,minimum = stats_rec.minimum ,maximum = stats_rec.maximum ,sdev = stats_rec.sdev ,pctile_25 = stats_rec.pctile_25 ,pctile_50 = stats_rec.pctile_50 ,pctile_75 = stats_rec.pctile_75 ,pctile_90 = stats_rec.pctile_90 ,pctile_95 = stats_rec.pctile_95 ,est_sample_count = stats_rec.est_sample_count ,est_slope = stats_rec.est_slope ,est_intercept = stats_rec.est_intercept ,est_fit_quality = stats_rec.est_fit_quality ,est_pctile_99 = stats_rec.est_pctile_99 ,est_pctile_999 = stats_rec.est_pctile_999 ,est_pctile_9999 = stats_rec.est_pctile_9999 where S.bsln_guid = stats_rec.bsln_guid and S.datasource_guid = stats_rec.datasource_guid and S.subinterval_code = stats_rec.subinterval_code; if SQL%ROWCOUNT = 0 then ----------------------------------------------------- -- NOTE: insert depends on mgmt_bsln_statistics -- and bsln_statistics_t attribute-equivalence ----------------------------------------------------- insert into mgmt_bsln_statistics values stats_rec; end if; end; end loop; close MGMT_BSLN_INTERNAL.compute_load_stats_ARcur; commit work; exception when others then rollback; if MGMT_BSLN_INTERNAL.compute_load_stats_ARcur%ISOPEN then close MGMT_BSLN_INTERNAL.compute_load_stats_ARcur; end if; raise; end compute_load_stats_AR; --------------------------------------------------------------------------- -- Procedure: compute_all_statistics -- Description: Compute and load current statistics for all active -- rolling baselines. Determines compute date as the closest -- 12am (midnight) to current time. -- Arguments: none -- NOTE: This procedure is intended to run as a daily job in either -- DBMS_JOB or DBMS_SCHEDULER, not client invoked. --------------------------------------------------------------------------- procedure compute_all_statistics is myname module_name_t := 'COMPUTE_ALL_STATISTICS'; l_compute_date date; begin if (SYSDATE - TRUNC(SYSDATE) ) > 1/2 then ------------------------------------ -- compute for today if before noon ------------------------------------ l_compute_date := TRUNC(SYSDATE); else ------------------------------------ -- else compute for tomorrow ------------------------------------ l_compute_date := TRUNC(SYSDATE+1); end if; --------------------------------------------------------- -- call compute and load procedure -- NOTE: called procedure handles commit/rollback --------------------------------------------------------- compute_load_stats_AR (compute_date_in => l_compute_date ,bsln_guid_in => null); exception when others then mgmt_log.log_error('COMPUTE METRIC BASELINE STATISTICS', sqlcode, 'compute_all_statistics:'|| SUBSTR(SQLERRM, 1, 1000) ); end compute_all_statistics; ------------------------------------------------------------ -- ------------------------------------------------------------ function extract_compute_stats (extract_cv in extract_cvtype ,compute_date_in in date := SYSDATE) return bsln_statistics_set PIPELINED CLUSTER extract_cv BY (datasource_guid) PARALLEL_ENABLE (PARTITION extract_cv BY HASH(datasource_guid)) is myname module_name_t := 'EXTRACT_COMPUTE_STATS'; l_obs_set bsln_observation_set := bsln_observation_set(); l_curr_rec extract_rectype; l_statistics_set bsln_statistics_set; l_compute_date date; l_subinterval_code mgmt_bsln.subinterval_code_t; begin --------------------------------- -- default null compute_date_in --------------------------------- l_compute_date := NVL(compute_date_in,SYSDATE); loop ---------------------------------------- -- fetch all cursor rows ---------------------------------------- fetch extract_cv into l_curr_rec; exit when extract_cv%NOTFOUND; -------------------------------------------------- -- NOTE: may need filtering logic here in future -------------------------------------------------- -------------------------------------------------- -- NOTE: hack below because deterministic function -- caching not working for subinterval_code function -------------------------------------------------- l_subinterval_code := cached_subinterval_code(l_curr_rec.subinterval_key ,l_curr_rec.obs_time); -------------------------------------------------- -- if empty batch or same batch as last row added -- then add to current observation_set -------------------------------------------------- if l_obs_set.COUNT = 0 or ( l_curr_rec.datasource_guid = l_obs_set(l_obs_set.LAST).datasource_guid ) then l_obs_set.EXTEND; l_obs_set(l_obs_set.LAST) := bsln_observation_t (l_curr_rec.datasource_guid ,l_curr_rec.bsln_guid ,l_subinterval_code ,l_curr_rec.obs_time ,l_curr_rec.obs_value); else assert(l_obs_set(l_obs_set.FIRST).datasource_guid = l_obs_set(l_obs_set.LAST).datasource_guid, myname||':BAD BUNCH'); ------------------------------------------------- -- full batch: compute stats and pipe out rows -- NOTE: see and maintain duplicate logic below ------------------------------------------------- l_statistics_set := exptail_stats(l_obs_set); if l_statistics_set.COUNT > 0 then for i in l_statistics_set.FIRST..l_statistics_set.LAST loop l_statistics_set(i).compute_date := l_compute_date; pipe row(l_statistics_set(i)); end loop; end if; ----------------------------------------- -- reinitialize batch ----------------------------------------- l_obs_set := bsln_observation_set(); l_obs_set.EXTEND; l_obs_set(l_obs_set.LAST) := bsln_observation_t (l_curr_rec.datasource_guid ,l_curr_rec.bsln_guid ,l_subinterval_code ,l_curr_rec.obs_time ,l_curr_rec.obs_value); end if; end loop; ------------------------------------------------ -- Compute and pipe rows for last batch -- NOTE: see and maintain duplicate logic above ------------------------------------------------ if l_obs_set.COUNT > 0 then l_statistics_set := exptail_stats(l_obs_set); if l_statistics_set.COUNT > 0 then for i in l_statistics_set.FIRST..l_statistics_set.LAST loop l_statistics_set(i).compute_date := l_compute_date; pipe row(l_statistics_set(i)); end loop; end if; l_obs_set := bsln_observation_set(); end if; ---------------------------- -- close cursor and return ---------------------------- close extract_cv; return; end extract_compute_stats; function exptail_stats01 (observation_set_in bsln_observation_set) return bsln_statistics_set is myname module_name_t := 'EXPTAIL_STATS'; l_statistics_set bsln_statistics_set; tail_low_pctile number := 0.95; tail_high_pctile number := 0.99; begin assert(observation_set_in.COUNT > 0, myname||':observation_set_in.COUNT>0'); select bsln_statistics_t (bsln_guid ,datasource_guid ,to_date(null) --compute_date ,subinterval_code ,sample_count ,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 ,(LN( 1000) - est_intercept) / est_slope ,(LN(10000) - est_intercept) / est_slope ) bulk collect into l_statistics_set from (select datasource_guid ,bsln_guid ,subinterval_code ,REGR_SLOPE( -LN(1-(rrank-1)/N), obs_value ) as est_slope ,REGR_INTERCEPT( -LN(1-(rrank-1)/N), obs_value ) as est_intercept ,ROUND(100*REGR_R2( -LN(1-(rrank-1)/N), obs_value ) ,1) as est_fit_quality ,REGR_COUNT( -LN(1-(rrank-1)/N), obs_value ) as est_sample_count ,MAX(N) as sample_count ,MAX(max_val) as maximum ,MAX(min_val) as minimum ,MAX(avg_val) as average ,MAX(sdev_val) as sdev ,MAX(pctile_25) as pctile_25 ,MAX(pctile_50) as pctile_50 ,MAX(pctile_75) as pctile_75 ,MAX(pctile_90) as pctile_90 ,MAX(pctile_95) as pctile_95 ,MAX(est_pctile_99) as est_pctile_99 from (select datasource_guid, bsln_guid, subinterval_code, obs_value ,rrank, N, max_val, min_val, avg_val, sdev_val ,pctile_25, pctile_50, pctile_75, pctile_90, pctile_95, est_pctile_99 from (select datasource_guid ,bsln_guid ,subinterval_code ,obs_value as obs_value ,CUME_DIST () OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ORDER BY obs_value ) as cume_dist ,COUNT(1) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as N ,ROW_NUMBER () OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ORDER BY obs_value) as rrank ,MAX(obs_value) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as max_val ,MIN(obs_value) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as min_val ,AVG(obs_value) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as avg_val ,STDDEV(obs_value) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as sdev_val ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_25 ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_50 ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_75 ,PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_90 ,PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_95 ,PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as est_pctile_99 from TABLE(observation_set_in) D ) X where X.cume_dist >= tail_low_pctile and X.cume_dist <= tail_high_pctile ) group by datasource_guid, bsln_guid, subinterval_code ); return l_statistics_set; end exptail_stats01; function exptail_stats (observation_set_in bsln_observation_set) return bsln_statistics_set is myname module_name_t := 'EXPTAIL_STATS'; l_statistics_set bsln_statistics_set; tail_low_pctile number := 0.95; tail_high_pctile number := 0.99; tail_mid_pctile number := (tail_low_pctile+tail_high_pctile)/2; begin assert(observation_set_in.COUNT > 0, myname||':observation_set_in.COUNT>0'); select bsln_statistics_t (bsln_guid ,datasource_guid ,to_date(null) --compute_date ,subinterval_code ,sample_count ,average ,minimum ,maximum ,sdev ,pctile_25 ,pctile_50 ,pctile_75 ,pctile_90 ,pctile_95 ,est_sample_count ,est_slope ,est_intercept ,CASE when est_slope = 0 -- need to check with Amir on this then 0 else GREATEST(0,NVL(100-(25*POWER((1-est_mu1/est_slope), 2)*(est_sample_count-1) ),0)) -- est_fit_quality end ,est_pctile_99 ,LN( 1000) * est_slope + est_intercept ,LN(10000) * est_slope + est_intercept ) bulk collect into l_statistics_set from (select datasource_guid ,bsln_guid ,subinterval_code ,est_mu as est_slope ,est_mu * LN(alpha) + X_M as est_intercept ,to_number(NULL) as est_fit_quality ,CASE when count_below_X_J > 0 then (sum_below_X_J + (N-M+1)*(X_J-X_M))/count_below_X_J - X_J else to_number(null) end as est_mu1 ,est_sample_count ,N as sample_count ,average ,minimum ,maximum ,sdev ,pctile_25 ,pctile_50 ,pctile_75 ,pctile_90 ,pctile_95 ,est_pctile_99 from (select datasource_guid ,bsln_guid ,subinterval_code ,MAX(N) as N ,COUNT(rrank) as est_sample_count ,CASE when COUNT(rrank) > 3 then ( SUM(obs_value) + ( MAX(N) - MAX(rrank) ) * MAX(obs_value) - (MAX(N) - MIN(rrank) + 1) * MIN(obs_value) ) / (COUNT(rrank)-1) else to_number(null) end as est_mu ,(MAX(N) - MIN(rrank) + 1) / (MAX(N) + 1) as alpha ,MIN(obs_value) as X_M ,MAX(obs_value) as X_L ,MAX(rrank) as L ,MIN(rrank) as M ,MAX(mid_tail_value) as X_J ,SUM(CASE when obs_value < mid_tail_value then obs_value else 0 end ) as sum_below_X_J ,SUM(CASE when cume_dist < tail_mid_pctile then 1 else 0 end ) as count_below_X_J ,MAX(max_val) as maximum ,MAX(min_val) as minimum ,MAX(avg_val) as average ,MAX(sdev_val) as sdev ,MAX(pctile_25) as pctile_25 ,MAX(pctile_50) as pctile_50 ,MAX(pctile_75) as pctile_75 ,MAX(pctile_90) as pctile_90 ,MAX(pctile_95) as pctile_95 ,MAX(est_pctile_99) as est_pctile_99 from (select datasource_guid ,bsln_guid ,subinterval_code ,obs_value as obs_value ,CUME_DIST () OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ORDER BY obs_value ) as cume_dist ,COUNT(1) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as N ,ROW_NUMBER () OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ORDER BY obs_value) as rrank ,PERCENTILE_DISC(tail_mid_pctile) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as mid_tail_value ,MAX(obs_value) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as max_val ,MIN(obs_value) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as min_val ,AVG(obs_value) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as avg_val ,STDDEV(obs_value) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code ) as sdev_val ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_25 ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_50 ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_75 ,PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_90 ,PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as pctile_95 ,PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY obs_value ASC) OVER (PARTITION BY datasource_guid, bsln_guid, subinterval_code) as est_pctile_99 from TABLE(observation_set_in) D ) X where X.cume_dist >= tail_low_pctile and X.cume_dist <= tail_high_pctile group by datasource_guid ,bsln_guid ,subinterval_code ) ); return l_statistics_set; end exptail_stats; ---------------------------------------------------------- -- load statistics table (insert/update merging) ---------------------------------------------------------- procedure load_statistics (statistics_set_in in bsln_statistics_set ,replace_flag_in in boolean := TRUE) is myname module_name_t := 'LOAD_STATISTICS'; begin if NVL(replace_flag_in,TRUE) then ----------------------------------------- -- NOTE: delete ignores compute_date ----------------------------------------- delete from mgmt_bsln_statistics where (datasource_guid, bsln_guid, subinterval_code) in (select datasource_guid, bsln_guid, subinterval_code from TABLE(CAST(statistics_set_in AS bsln_statistics_set) ) X ); end if; ---------------------------------------------------------------- -- NOTE: insert depends on attribute-equality of statistics -- table and object type ---------------------------------------------------------------- insert into mgmt_bsln_statistics select X.* from TABLE(CAST(statistics_set_in AS bsln_statistics_set) ) X; commit; exception when others then rollback; raise; end load_statistics; ---------------------------------------------------------- -- Function: compute_statistics -- Description: Compute and return baseline statistics for a target -- using supplied baseline parameters. -- Arguments: name_in - baseline name -- subinterval_key - key to time partitioning method -- interval_begin_in - single fixed interval begin time -- interval_end_in - single fixed interval end time -- target_uid_in - target uid -- Returns: The set of baseline statistics for this target's registered -- datasources over the time interval using the subinterval_key ---------------------------------------------------------- function compute_statistics (bsln_name_in in mgmt_bsln_baselines.name%type ,interval_begin_in in date ,interval_end_in in date ,subinterval_key_in in subinterval_key_t ,target_uid_in in guid_t := null ) return bsln_statistics_set is myname module_name_t := 'COMPUTE_STATISTICS'; l_statistics_set bsln_statistics_set; l_target_uid guid_t; l_bsln_guid guid_t; begin assert(bsln_name_in is not null, myname||':bsln_name_in not null'); assert(interval_begin_in is not null, myname||':interval_begin_in not null'); assert(interval_end_in is not null, myname||':interval_end_in not null'); assert(subinterval_key_in is not null, myname||':subinterval_key_in not null'); assert(mgmt_bsln.valid_key(subinterval_key_in), myname||':valid_key('||subinterval_key_in||')' ); -------------------------------- -- Translate null target_uid to the local target -------------------------------- l_target_uid := NVL(target_uid_in,mgmt_bsln.this_target_uid); ------------------------------ -- construct bsln_guid ------------------------------ l_bsln_guid := mgmt_bsln.baseline_guid (l_target_uid, bsln_name_in); ------------------------------------ -- open and fetch from stats cursor -- NOTE: close if already open ------------------------------------ if MGMT_BSLN_INTERNAL.compute_statistics_cur%ISOPEN then close MGMT_BSLN_INTERNAL.compute_statistics_cur; end if; open MGMT_BSLN_INTERNAL.compute_statistics_cur (l_bsln_guid ,l_target_uid ,subinterval_key_in ,interval_begin_in ,interval_end_in); fetch MGMT_BSLN_INTERNAL.compute_statistics_cur bulk collect into l_statistics_set; close MGMT_BSLN_INTERNAL.compute_statistics_cur; return l_statistics_set; exception when others then if MGMT_BSLN_INTERNAL.compute_statistics_cur%ISOPEN then close MGMT_BSLN_INTERNAL.compute_statistics_cur; end if; raise; end compute_statistics; ------------------------------------------------------------------------- -- Function: data_and_model_OK -- Description: Encapsulates the logic for determining whether threshold -- can be usefully set or if failure action should be taken. -- Arguments: threshold_method_in - PCTMAX or SIGLVL -- threshold_param_in - indicates relative threshold level -- sample_count_in - cardinality of statistical data sample -- fit_quality_in - goodness of fit metric for sample -- est_sample_count_in - cardinality of sample used for tail estimate -- Returns: Integer mgmt_bsln.K_TRUE if both data and model fit are sufficient -- for threshold setting. -- NOTE: This logic is specific to the exptail_stats function -- and needs to be modified when that changes. Also note that -- signature is changed over original version. -------------------------------------------------------------------------- function data_and_model_OK (threshold_method_in in varchar2 ,threshold_param_in in number ,sample_count_in in number ,fit_quality_in in number ) return integer is myname module_name_t := 'DATA_AND_MODEL_OK'; l_return integer; begin assert(threshold_method_in is not null, myname||':threshold_method_in not null'); assert(threshold_param_in is not null, myname||':threshold_param_in not null'); assert(threshold_method_in in (mgmt_bsln.K_METHOD_SIGLVL, mgmt_bsln.K_METHOD_PCTMAX), myname||':threshold_method_in valid'); ---------------------------------------------------- -- initialize return to FALSE ---------------------------------------------------- l_return := mgmt_bsln.K_FALSE; ---------------------------------------------------- -- if no stats then return FALSE immediately ---------------------------------------------------- if sample_count_in IS NULL OR fit_quality_in IS NULL then RETURN mgmt_bsln.K_FALSE; end if; --------------------------------------------------- -- logic to test and set l_return to TRUE for -- conditions that meet model and data sufficiency -- requirements --------------------------------------------------- if threshold_method_in = mgmt_bsln.K_METHOD_PCTMAX then ------------------------------------- -- for PCTMAX simply need 100 points ------------------------------------- if sample_count_in >= 100 then l_return := mgmt_bsln.K_TRUE; end if; elsif threshold_method_in = mgmt_bsln.K_METHOD_SIGLVL ------------------------------------------ -- for SIGLVL it depends on more factors ------------------------------------------ then case when threshold_param_in = mgmt_bsln.K_SIGLVL_95 then if sample_count_in >= 100 then l_return := mgmt_bsln.K_TRUE; end if; when threshold_param_in = mgmt_bsln.K_SIGLVL_99 then if sample_count_in >= 500 then l_return := mgmt_bsln.K_TRUE; end if; ---------------------------------------------------------- -- both 3 9's and 4 9's thresholds share same criterion ---------------------------------------------------------- when threshold_param_in IN (mgmt_bsln.K_SIGLVL_999, mgmt_bsln.K_SIGLVL_9999) then if sample_count_in >= 700 and fit_quality_in >= 30 then l_return := mgmt_bsln.K_TRUE; end if; else ----------------------------------------------- -- invalid threshold parm (should not get here) ----------------------------------------------- null; assert(null,myname||':threshold_param_in valid'); end case; else ----------------------------------------------------- -- invalid threshold_type_in (should not get here) ----------------------------------------------------- null; assert(null,myname||':threshold_type valid'); end if; assert(l_return is not null,myname||':l_return not null'); return l_return; end data_and_model_OK; ------------------------------------------------------------------------- -- Procedure: new_threshold_value -- Description: Encapsulates the logic for determining next threshold -- value given current value, relevant baseline statistics, -- threshold parameter settings -- Arguments: THR_rec_in - record of threshold parms/datasource/statistics -- param_in - parameter value for threshold method -- value_inout - variable holding current threshold value -------------------------------------------------------------------------- procedure new_threshold_value (THR_rec_in THR_rectype ,param_in mgmt_bsln_threshold_parms.warning_param%TYPE ,value_inout in out alert_threshold_t) is myname module_name_t := 'NEW_THRESHOLD_VALUE'; l_value number; -- container for new value of threshold begin assert(param_in is not null, myname||':param_in not null'); --------------------------------------------------------------------- -- adjust value_inout for grid control NULL indicator (single blank) --------------------------------------------------------------------- if value_inout = ' ' then value_inout := null; end if; ------------------------------------ -- check data and model sufficiency ------------------------------------ if mgmt_bsln.K_TRUE = MGMT_BSLN.data_and_model_OK (threshold_method_in => THR_rec_in.threshold_method ,threshold_param_in => param_in ,sample_count_in => THR_rec_in.sample_count ,fit_quality_in => THR_rec_in.est_fit_quality ) then if THR_rec_in.threshold_method = mgmt_bsln.K_METHOD_PCTMAX then -------------------------------------- -- use trimmed max not absolute max -------------------------------------- l_value := (param_in /100 * THR_rec_in.pctile_99); elsif THR_rec_in.threshold_method = mgmt_bsln.K_METHOD_SIGLVL then case param_in when mgmt_bsln.K_SIGLVL_95 then l_value := THR_rec_in.pctile_95; when mgmt_bsln.K_SIGLVL_99 then l_value := THR_rec_in.pctile_99; when mgmt_bsln.K_SIGLVL_999 then l_value := THR_rec_in.pctile_999; when mgmt_bsln.K_SIGLVL_9999 then l_value := THR_rec_in.pctile_9999; else null; assert(null,myname||':new_value:siglvl valid'); end case; else null; assert(null,myname||':new_value:threshold_method valid'); end if; else --------------------------------------------------------- -- bad model fit or insufficient data => take fail action --------------------------------------------------------- case THR_rec_in.fail_action when mgmt_bsln.K_FAIL_ACTION_UNSET then l_value := to_number(null); when mgmt_bsln.K_FAIL_ACTION_PRESERVE then l_value := to_number(value_inout); else null; assert(null,myname||':new_value:fail_action valid'); end case; end if; ------------------------------------------------------------------------ -- NOTE: Rounding new threshold values to 5 places ------------------------------------------------------------------------ value_inout := to_char(ROUND(l_value,5)); end new_threshold_value; --------------------------------------------------------------------------- -- Function: is_enabled -- Description: Pass-thru to deployment-specific function that returns -- K_TRUE if baslining is enabled on this target. -- Arguments: none --------------------------------------------------------------------------- function is_enabled return integer is begin return MGMT_BSLN_INTERNAL.is_enabled; 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; ----------------------------------------------------------------------------- -- -- Package initialization code -- ----------------------------------------------------------------------------- begin load_P_DAYOFFSET; null; end mgmt_bsln; / show errors