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