Rem Rem $Header: self_monitor_init.sql 01-sep-2007.18:51:35 rrawat Exp $ Rem Rem self_monitor_init.sql Rem Rem Copyright (c) 2005, 2007, Oracle. All rights reserved. Rem Rem NAME Rem self_monitor_init.sql - Rem Rem DESCRIPTION Rem generates the emrep repository metrics Rem Rem NOTES Rem called for both upgrade and install. Follows execution of self_monitor_pkgdefs and Rem self_monitor_pkgbodys Rem Rem MODIFIED (MM/DD/YY) Rem rrawat 09/01/07 - Rem tsubrama 04/19/07 - Backport tsubrama_fix5464559 from main Rem tsubrama 04/11/07 - fix for 5464559 Rem aholser 08/24/05 - Rem jsadras 06/08/05 - add task class to performance metric Rem aholser 06/07/05 - aholser_bug-4256118 Rem aholser 05/11/05 - Created Rem set serveroutput on size 20000 set ECHO ON -- stop collections and clean up any existing metrics and thresholds BEGIN DECLARE err NUMBER; l_guid RAW(16); l_ver_num VARCHAR2(8); l_metric_name VARCHAR2(64); l_metric_name_array SMP_EMD_STRING_ARRAY; BEGIN FOR crec IN (SELECT target_name, target_guid, type_meta_ver FROM mgmt_targets WHERE target_type=MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE) LOOP l_metric_name_array := SMP_EMD_STRING_ARRAY(); SELECT coll_name BULK COLLECT INTO l_metric_name_array FROM mgmt_collections WHERE object_guid = crec.target_guid; FOR i IN 1..l_metric_name_array.count LOOP BEGIN mgmt_collection.delete_collection(MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, crec.target_name, l_metric_name_array(i)); EXCEPTION WHEN OTHERS THEN err := SQLCODE; dbms_output.put_line('error '||to_char(err)||' adding deleting metric '||l_metric_name_array(i)); END; END LOOP; l_metric_name_array := SMP_EMD_STRING_ARRAY(); SELECT DISTINCT metric_name BULK COLLECT INTO l_metric_name_array FROM mgmt_metrics WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND type_meta_ver = crec.type_meta_ver; FOR i IN 1..l_metric_name_array.count LOOP BEGIN mgmt_metric.delete_metric(MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, l_metric_name_array(i), crec.type_meta_ver); EXCEPTION WHEN OTHERS THEN err := SQLCODE; dbms_output.put_line('error '||to_char(err)||' adding deleting metric '||l_metric_name_array(i)); END; END LOOP; END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END; COMMIT; END; / show errors; -- -- create the metric to track repo collection performance -- DECLARE l_metric_columns mgmt_metric_column_array:= mgmt_metric_column_array() ; l_is_key NUMBER ; l_metric_type NUMBER ; BEGIN l_metric_columns.extend(6) ; FOR i IN 1..mgmt_admin_data.repo_performance_columns.COUNT LOOP IF i=1 THEN l_is_key := 1 ; l_metric_type := MGMT_GLOBAL.G_METRIC_TYPE_STRING ; ELSE l_is_key := 0 ; l_metric_type := MGMT_GLOBAL.G_METRIC_TYPE_NUMBER ; END IF ; l_metric_columns(i) := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>mgmt_admin_data.repo_performance_columns(i), p_column_label=>mgmt_admin_data.repo_performance_column_labels(i), p_column_label_nlsid=>mgmt_admin_data.repo_performance_column_nlsids(i), p_column_type=>l_metric_type, p_is_key=>l_is_key) ; END LOOP ; MGMT_METRIC.create_metric( p_target_type => MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER, p_metric_name => MGMT_ADMIN_DATA.REPO_PERFORMANCE_METRIC, p_metric_label => MGMT_ADMIN_DATA.REPO_PERFORMANCE_METRIC_LABEL, p_metric_label_nlsid => MGMT_ADMIN_DATA.REPO_PERFORMANCE_METRIC, p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_PLSQL, p_source => 'mgmt_admin_data.eval_repo_performance', p_metric_column_list => l_metric_columns, p_is_repository=>MGMT_GLOBAL.G_TRUE ); END ; / show errors; -- Create the metadata here - it remains when the target is deleted. BEGIN DECLARE l_target_guid RAW(16); l_metric_columns mgmt_metric_column_array; l_metric_columns_types SMP_EMD_INTEGER_ARRAY_ARRAY; l_metric_columns_keys SMP_EMD_INTEGER_ARRAY_ARRAY; l_metric_column_len SMP_EMD_INTEGER_ARRAY; err NUMBER; BEGIN l_metric_columns := mgmt_metric_column_array() ; l_metric_columns_types := SMP_EMD_INTEGER_ARRAY_ARRAY(); l_metric_columns_keys := SMP_EMD_INTEGER_ARRAY_ARRAY(); l_metric_column_len := SMP_EMD_INTEGER_ARRAY(); -- initialize the arrays for the metric column data l_metric_columns_types.extend(MGMT_ADMIN_DATA.METRIC_COUNT); l_metric_columns_keys.extend(MGMT_ADMIN_DATA.METRIC_COUNT); l_metric_column_len.extend(MGMT_ADMIN_DATA.METRIC_COUNT); -- The number of columns for each of the metrics FOR i IN 1..MGMT_ADMIN_DATA.METRIC_COUNT LOOP l_metric_column_len(i) := MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(i).COUNT; END LOOP; -- initialize the column arrays FOR i in 1..MGMT_ADMIN_DATA.METRIC_COUNT LOOP l_metric_columns_types(i) := SMP_EMD_INTEGER_ARRAY(); l_metric_columns_types(i).extend(l_metric_column_len(i)); l_metric_columns_keys(i) := SMP_EMD_INTEGER_ARRAY(); l_metric_columns_keys(i).extend(l_metric_column_len(i)); -- initialize column default values where appropriate -- type defaults to number, key defaults to false and collection defaults to no collection FOR j IN 1..l_metric_column_len(i) LOOP l_metric_columns_types(i)(j) := MGMT_GLOBAL.G_METRIC_TYPE_NUMBER; l_metric_columns_keys(i)(j) := MGMT_GLOBAL.G_METRIC_KEY_FALSE; END LOOP; END LOOP; ------------------------------------------ -- define the column non-default values -- ------------------------------------------ ------------------------------- -- Response -- -- This metric is currently not being registered -- ------------------------------- l_metric_columns_types(MGMT_ADMIN_DATA.RESPONSE_INDEX)(2) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; ------------------------------- -- Management_Servlet_Status -- ------------------------------- l_metric_columns_types(MGMT_ADMIN_DATA.SERVLET_INDEX)(1) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; l_metric_columns_types(MGMT_ADMIN_DATA.SERVLET_INDEX)(2) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; l_metric_columns_types(MGMT_ADMIN_DATA.SERVLET_INDEX)(4) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; -- the first column is a key, the others are not l_metric_columns_keys(MGMT_ADMIN_DATA.SERVLET_INDEX)(1) := MGMT_GLOBAL.G_METRIC_KEY_TRUE; ----------------------------- -- DBMS_Job_Status -- ----------------------------- l_metric_columns_types(MGMT_ADMIN_DATA.DBMS_INDEX)(1) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; l_metric_columns_types(MGMT_ADMIN_DATA.DBMS_INDEX)(2) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; l_metric_columns_types(MGMT_ADMIN_DATA.DBMS_INDEX)(3) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; l_metric_columns_keys(MGMT_ADMIN_DATA.DBMS_INDEX)(1) := MGMT_GLOBAL.G_METRIC_KEY_TRUE; ------------------------------ -- Notification_Performance -- ------------------------------ l_metric_columns_types(MGMT_ADMIN_DATA.NOTIFICATION_INDEX)(1) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; -------------------------------- -- Job_Dispatcher_Performance -- -------------------------------- l_metric_columns_types(MGMT_ADMIN_DATA.JOB_DISPATCHER_INDEX)(1) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; l_metric_columns_keys(MGMT_ADMIN_DATA.JOB_DISPATCHER_INDEX)(1) := MGMT_GLOBAL.G_METRIC_KEY_TRUE; ----------------------------- -- Configuration -- ----------------------------- -- all use defaults ----------------------------- -- Session count -- ----------------------------- l_metric_columns_types(MGMT_ADMIN_DATA.SESSIONS_INDEX)(1) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; l_metric_columns_keys(MGMT_ADMIN_DATA.SESSIONS_INDEX)(1) := MGMT_GLOBAL.G_METRIC_KEY_TRUE; ----------------------------- -- Duplicate targets -- ----------------------------- -- all use defaults ----------------------------- -- Loader Status -- ----------------------------- l_metric_columns_types(MGMT_ADMIN_DATA.LOADER_STATUS_INDEX)(1) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; l_metric_columns_keys(MGMT_ADMIN_DATA.LOADER_STATUS_INDEX)(1) := MGMT_GLOBAL.G_METRIC_KEY_TRUE; -------------------------------- -- Repository Job_Dispatcher -- -------------------------------- -- all use defaults -------------------------------- -- No Agents -- -------------------------------- -- all use defaults -------------------------------- -- Agent Status -- -------------------------------- l_metric_columns_keys(MGMT_ADMIN_DATA.AGENT_STATUS_INDEX)(1) := MGMT_GLOBAL.G_METRIC_KEY_TRUE; -------------------------------- -- Notification Methods -- -------------------------------- l_metric_columns_keys(MGMT_ADMIN_DATA.NOTIF_METHODS_INDEX)(1) := MGMT_GLOBAL.G_METRIC_KEY_TRUE; -- Start registering the metrics and columns -- Generate the target GUID, -- NOTE : Dont query from mgmt_targets as the VPD policy is set -- but not enforced and so any query on mgmt_targets would give no rows. l_target_guid := mgmt_target.generate_target_guid( MGMT_GLOBAL.G_MANAGEMENT_SYSTEM, MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE); -- -- Add the metrics -- Start at index 2 to bypass the Response metric -- FOR i in 2..MGMT_ADMIN_DATA.METRIC_COUNT LOOP BEGIN l_metric_columns := mgmt_metric_column_array() ; l_metric_columns.extend(l_metric_column_len(i)) ; FOR j IN 1..l_metric_column_len(i) LOOP l_metric_columns(j) := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(i)(j), p_column_label => MGMT_ADMIN_DATA.METRIC_COLUMN_LABELS(i)(j), p_column_label_nlsid => MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(i)(j), p_column_type => l_metric_columns_types(i)(j), p_is_key => l_metric_columns_keys(i)(j)); dbms_output.put_line( 'Added metric column ' ||MGMT_ADMIN_DATA.METRIC_NAMES(i)||' '||MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(i)(j)); END LOOP ; MGMT_METRIC.create_metric( p_target_type => MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER, p_metric_name => MGMT_ADMIN_DATA.METRIC_NAMES(i), p_metric_label => MGMT_ADMIN_DATA.METRIC_LABELS(i), p_metric_label_nlsid => MGMT_ADMIN_DATA.METRIC_NAMES(i), p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_OLD_PLSQL, p_source => 'MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS', p_metric_column_list => l_metric_columns, p_is_repository => MGMT_GLOBAL.G_TRUE); EXCEPTION WHEN OTHERS THEN err := SQLCODE; dbms_output.put_line( 'EXCEPTION for add metric ' ||MGMT_ADMIN_DATA.METRIC_NAMES(i)||' err '|| err ); END; END LOOP; COMMIT; END; END; / show errors; BEGIN DECLARE l_metric_columns mgmt_metric_column_array; err NUMBER; BEGIN l_metric_columns := mgmt_metric_column_array() ; BEGIN l_metric_columns.extend(mgmt_admin_data.agent_repo_metric_cols.COUNT); FOR i IN 1..mgmt_admin_data.agent_repo_metric_cols.COUNT LOOP l_metric_columns(i) := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>mgmt_admin_data.agent_repo_metric_cols(i), p_column_label=>mgmt_admin_data.agent_repo_metric_col_labels(i), p_column_label_nlsid=>mgmt_admin_data.agent_repo_metric_col_nlsids(i), p_column_type=>mgmt_global.G_METRIC_TYPE_NUMBER, p_is_key=>mgmt_global.G_FALSE) ; END LOOP ; BEGIN MGMT_METRIC.create_metric( p_target_type => MGMT_GLOBAL.G_AGENT_TARGET_TYPE, p_type_meta_ver=>'1.0', p_metric_name => MGMT_ADMIN_DATA.AGENT_REPO_METRIC, p_metric_label => MGMT_ADMIN_DATA.AGENT_REPO_METRIC_LABEL, p_metric_label_nlsid => MGMT_ADMIN_DATA.AGENT_REPO_METRIC_LABEL, p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_BULK_PLSQL, p_source => 'mgmt_admin_data.eval_agent_metrics', p_metric_column_list => l_metric_columns, p_is_repository=>MGMT_GLOBAL.G_TRUE, p_is_long_running=>1 ); EXCEPTION WHEN OTHERS THEN dbms_output.put_line( 'EXCEPTION for add metric ' ||MGMT_ADMIN_DATA.AGENT_REPO_METRIC||' err '|| SQLCODE ); END; END; END; END; / show errors;