Rem Rem $Header: chronos_rollup_pkgdef.sql 11-jul-2005.11:14:03 scgrover Exp $ Rem Rem chronos_rollup_pkgdef.sql Rem Rem Copyright (c) 2002, 2005, Oracle. All rights reserved. Rem Rem NAME Rem chronos_rollup_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem scgrover 07/07/05 - add extended sql trace Rem eporter 02/24/05 - Add rollups for incomplete and parts of pages Rem eporter 12/01/04 - Bug 3849061 - Rollup into new columns Rem eporter 09/09/04 - Add procedures for bootstrap tables Rem jhsy 08/13/04 - add database times Rem snakai 01/08/04 - add constant: number of rollup tables Rem mashukla 02/11/03 - cleanup extra fields Rem mashukla 02/03/03 - change dist type Rem mashukla 12/09/02 - Rem mashukla 12/01/02 - add to rollup types Rem rpinnama 09/18/02 - Remove authid current_user clause Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem CREATE OR REPLACE PACKAGE EMD_RT_ROLLUP AS -- -- ***** WARNING!! NON-JAVA DECLARATIONS MUST BE BEFORE JAVA !!! ***** -- -- List of Procedures ------------------------------------------------ PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN); -- The main rollup procedure PROCEDURE ROLLUP; -- The bootstrap rollup procedure PROCEDURE BOOTSTRAP_ROLLUP(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, result OUT NUMBER, err_desc OUT VARCHAR2 ); -- Hourly rollup procedures PROCEDURE EMD_URL_1HOUR_ROLLUP; PROCEDURE EMD_URL_DIST_1HOUR_ROLLUP; PROCEDURE EMD_DOM_1HOUR_ROLLUP; PROCEDURE EMD_DOM_DIST_1HOUR_ROLLUP; PROCEDURE EMD_IP_1HOUR_ROLLUP; PROCEDURE EMD_IP_DIST_1HOUR_ROLLUP; PROCEDURE EMD_PR_MAPPING_1HOUR_ROLLUP; PROCEDURE EMD_INC_LOADS_1HOUR_ROLLUP; -- Daily rollup procedures PROCEDURE EMD_URL_1DAY_ROLLUP; PROCEDURE EMD_URL_DIST_1DAY_ROLLUP; PROCEDURE EMD_DOM_1DAY_ROLLUP; PROCEDURE EMD_DOM_DIST_1DAY_ROLLUP; PROCEDURE EMD_IP_1DAY_ROLLUP; PROCEDURE EMD_IP_DIST_1DAY_ROLLUP; PROCEDURE EMD_PR_MAPPING_1DAY_ROLLUP; PROCEDURE EMD_INC_LOADS_1DAY_ROLLUP; -- Hourly purge procedures PROCEDURE EMD_URL_1HOUR_PURGE; PROCEDURE EMD_URL_DIST_1HOUR_PURGE; PROCEDURE EMD_DOM_1HOUR_PURGE; PROCEDURE EMD_DOM_DIST_1HOUR_PURGE; PROCEDURE EMD_IP_1HOUR_PURGE; PROCEDURE EMD_IP_DIST_1HOUR_PURGE; PROCEDURE EMD_PR_MAPPING_1HOUR_PURGE; PROCEDURE EMD_INC_LOADS_1HOUR_PURGE; -- Daily purge procedures PROCEDURE EMD_URL_1DAY_PURGE; PROCEDURE EMD_URL_DIST_1DAY_PURGE; PROCEDURE EMD_DOM_1DAY_PURGE; PROCEDURE EMD_DOM_DIST_1DAY_PURGE; PROCEDURE EMD_IP_1DAY_PURGE; PROCEDURE EMD_IP_DIST_1DAY_PURGE; PROCEDURE EMD_PR_MAPPING_1DAY_PURGE; PROCEDURE EMD_INC_LOADS_1DAY_PURGE; -- The metrics_raw purge procedure PROCEDURE EMD_RAW_PURGE; -- Misc procedures PROCEDURE EMD_INITIALIZE; PROCEDURE EMD_BOOTSTRAP_INITIALIZE; FUNCTION EMD_GET_TARGET_INFO(cur_target_guid IN RAW) RETURN BOOLEAN; PROCEDURE EMD_WRITE_LOG; -- Partial rollup cleanup routines FUNCTION EMD_URL_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_URL_DIST_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_DOM_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_DOM_DIST_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_IP_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_IP_DIST_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_URL_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_URL_DIST_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_DOM_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_DOM_DIST_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_IP_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_IP_DIST_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_PR_MAPPING_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_INC_LOADS_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_PR_MAPPING_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_INC_LOADS_1DAY_CLEANUP RETURN BOOLEAN; -- Type Definitions -------------------------------------------------- TYPE p_target_guid_list_type IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER; TYPE p_date_list_type IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE p_number_list_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE p_number_10_2_list_type IS TABLE OF NUMBER(10, 2) INDEX BY BINARY_INTEGER; TYPE p_rowid_list_type IS TABLE OF UROWID INDEX BY BINARY_INTEGER; TYPE p_varchar2_15_list_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER; TYPE p_varchar2_64_list_type IS TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER; TYPE p_varchar2_256_list_type IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER; TYPE p_varchar2_1024_list_type IS TABLE OF VARCHAR2(1024) INDEX BY BINARY_INTEGER; TYPE p_varchar2_1280_list_type IS TABLE OF VARCHAR2(1280) INDEX BY BINARY_INTEGER; TYPE p_hour_rollup_record_type IS RECORD ( rollup_time p_date_list_type, metric_name p_varchar2_64_list_type, key_val p_varchar2_1024_list_type, -- url/ip/domain url_base p_varchar2_256_list_type, -- url rollup url_link p_varchar2_1280_list_type, -- url rollup subnet p_varchar2_15_list_type, -- domain rollup subnet_num p_number_list_type, -- domain rollup hits p_number_list_type, avg_val p_number_list_type, min_val p_number_list_type, max_val p_number_list_type, var_val p_number_list_type, svr_avg_val p_number_list_type, svr_min_val p_number_list_type, svr_max_val p_number_list_type, svr_var_val p_number_list_type, db_avg_val p_number_list_type, db_min_val p_number_list_type, db_max_val p_number_list_type, db_var_val p_number_list_type ); TYPE p_day_rollup_record_type IS RECORD ( rollup_time p_date_list_type, metric_name p_varchar2_64_list_type, key_val p_varchar2_1024_list_type, -- url/ip/domain url_base p_varchar2_256_list_type, -- url rollup url_link p_varchar2_1280_list_type, -- url rollup subnet p_varchar2_15_list_type, -- domain rollup subnet_num p_number_list_type, -- domain rollup hits p_number_list_type, min_val p_number_list_type, max_val p_number_list_type, sumx_val p_number_list_type, sumx2_val p_number_list_type, svr_min_val p_number_list_type, svr_max_val p_number_list_type, svr_sumx_val p_number_list_type, svr_sumx2_val p_number_list_type, db_min_val p_number_list_type, db_max_val p_number_list_type, db_sumx_val p_number_list_type, db_sumx2_val p_number_list_type ); TYPE p_dist_rollup_record_type IS RECORD ( rollup_time p_date_list_type, metric_name p_varchar2_64_list_type, key_val p_varchar2_1024_list_type, -- url/ip/domain subnet p_varchar2_15_list_type, -- domain rollup subnet_num p_number_list_type, -- domain rollup hits p_number_list_type, dist_period p_number_list_type, dist_type p_number_list_type ); TYPE p_pr_map_rollup_record_type IS RECORD ( rollup_time p_date_list_type, page_url p_varchar2_1024_list_type, request_url p_varchar2_1024_list_type, cache_hits p_number_list_type, ch_svr_sum p_number_10_2_list_type, -- cached hits server total time non_cache_hits p_number_list_type, nch_svr_sum p_number_10_2_list_type -- non-cached hits server total time ); TYPE p_inc_loads_rollup_record_type IS RECORD ( rollup_time p_date_list_type, page_url p_varchar2_1024_list_type, inc_loads p_number_list_type, svr_avg p_number_10_2_list_type -- server avg time ); -- This information needs to be passed to all of the bootstrap funcitons TYPE p_bootstrap_record_type IS RECORD ( target_guid RAW(16), last_rollup_time DATE, cur_rollup_time DATE, max_response_time NUMBER ); -- This information needs to be passed to all bootstrap funcitons TYPE p_bootstrap_log_type IS RECORD ( rollup_start_time DATE, total_rc PLS_INTEGER, url_rollup_rc PLS_INTEGER, url_dist_rollup_rc PLS_INTEGER, dom_rollup_rc PLS_INTEGER, dom_dist_rollup_rc PLS_INTEGER, ip_rollup_rc PLS_INTEGER, ip_dist_rollup_rc PLS_INTEGER ); -- Bootstrap rollup procedures PROCEDURE EMD_URL_BOOTSTRAP_ROLLUP( bootstrap_record_in IN p_bootstrap_record_type, bootstrap_log_in IN OUT p_bootstrap_log_type); PROCEDURE EMD_URL_DIST_BOOTSTRAP_ROLLUP( bootstrap_record_in IN p_bootstrap_record_type, bootstrap_log_in IN OUT p_bootstrap_log_type); PROCEDURE EMD_DOM_BOOTSTRAP_ROLLUP( bootstrap_record_in IN p_bootstrap_record_type, bootstrap_log_in IN OUT p_bootstrap_log_type); PROCEDURE EMD_DOM_DIST_BOOTSTRAP_ROLLUP( bootstrap_record_in IN p_bootstrap_record_type, bootstrap_log_in IN OUT p_bootstrap_log_type); PROCEDURE EMD_IP_BOOTSTRAP_ROLLUP( bootstrap_record_in IN p_bootstrap_record_type, bootstrap_log_in IN OUT p_bootstrap_log_type); PROCEDURE EMD_IP_DIST_BOOTSTRAP_ROLLUP( bootstrap_record_in IN p_bootstrap_record_type, bootstrap_log_in IN OUT p_bootstrap_log_type); PROCEDURE EMD_WRITE_BOOTSTRAP_LOG( bootstrap_log_in IN p_bootstrap_log_type); -- Package Constants ------------------------------------------------- -- Rollup table names p_metrics_raw_table VARCHAR(30) := 'MGMT_RT_METRICS_RAW'; p_url_1hour_table VARCHAR(30) := 'MGMT_RT_URL_1HOUR'; p_url_1day_table VARCHAR(30) := 'MGMT_RT_URL_1DAY'; p_url_dist_1hour_table VARCHAR(30) := 'MGMT_RT_URL_DIST_1HOUR'; p_url_dist_1day_table VARCHAR(30) := 'MGMT_RT_URL_DIST_1DAY'; p_dom_1hour_table VARCHAR(30) := 'MGMT_RT_DOMAIN_1HOUR'; p_dom_1day_table VARCHAR(30) := 'MGMT_RT_DOMAIN_1DAY'; p_dom_dist_1hour_table VARCHAR(30) := 'MGMT_RT_DOMAIN_DIST_1HOUR'; p_dom_dist_1day_table VARCHAR(30) := 'MGMT_RT_DOMAIN_DIST_1DAY'; p_ip_1hour_table VARCHAR(30) := 'MGMT_RT_IP_1HOUR'; p_ip_1day_table VARCHAR(30) := 'MGMT_RT_IP_1DAY'; p_ip_dist_1hour_table VARCHAR(30) := 'MGMT_RT_IP_DIST_1HOUR'; p_ip_dist_1day_table VARCHAR(30) := 'MGMT_RT_IP_DIST_1DAY'; p_pr_mapping_1hour_table VARCHAR(30) := 'MGMT_RT_PR_MAPPING_1HOUR'; p_pr_mapping_1day_table VARCHAR(30) := 'MGMT_RT_PR_MAPPING_1DAY'; p_inc_loads_1hour_table VARCHAR(30) := 'MGMT_RT_INCOMPLETE_LOADS_1HOUR'; p_inc_loads_1day_table VARCHAR(30) := 'MGMT_RT_INCOMPLETE_LOADS_1DAY'; p_url_bootstrap_table VARCHAR(30) := 'MGMT_RT_URL_BOOTSTRAP'; p_url_dist_bootstrap_table VARCHAR(30) := 'MGMT_RT_URL_DIST_BOOTSTRAP'; p_dom_bootstrap_table VARCHAR(30) := 'MGMT_RT_DOMAIN_BOOTSTRAP'; p_dom_dist_bootstrap_table VARCHAR(30) := 'MGMT_RT_DOMAIN_DIST_BOOTSTRAP'; p_ip_bootstrap_table VARCHAR(30) := 'MGMT_RT_IP_BOOTSTRAP'; p_ip_dist_bootstrap_table VARCHAR(30) := 'MGMT_RT_IP_DIST_BOOTSTRAP'; -- Default index into the timestamp array -- If p_num_rollup_tables changes, it can break the EMD_GET_TARGET_INFO -- function. Make sure that in basic_loader_pkgbody you changed the added -- tables in the ADD_TARGET function. p_num_rollup_tables PLS_INTEGER := 16; p_dom_1day_idx PLS_INTEGER := 1; p_dom_1hour_idx PLS_INTEGER := 2; p_dom_dist_1day_idx PLS_INTEGER := 3; p_dom_dist_1hour_idx PLS_INTEGER := 4; p_ip_1day_idx PLS_INTEGER := 5; p_ip_1hour_idx PLS_INTEGER := 6; p_ip_dist_1day_idx PLS_INTEGER := 7; p_ip_dist_1hour_idx PLS_INTEGER := 8; p_url_1day_idx PLS_INTEGER := 9; p_url_1hour_idx PLS_INTEGER := 10; p_url_dist_1day_idx PLS_INTEGER := 11; p_url_dist_1hour_idx PLS_INTEGER := 12; p_pr_mapping_1day_idx PLS_INTEGER := 13; p_pr_mapping_1hour_idx PLS_INTEGER := 14; p_inc_loads_1day_idx PLS_INTEGER := 15; p_inc_loads_1hour_idx PLS_INTEGER := 16; -- Other constants p_raw_keep_param VARCHAR(32) := 'mgmt_rt_raw_keep_window'; p_hour_keep_param VARCHAR(32) := 'mgmt_rt_hour_keep_window'; p_dist_hour_keep_param VARCHAR(32) := 'mgmt_rt_dist_hour_keep_window'; p_day_keep_param VARCHAR(32) := 'mgmt_rt_day_keep_window'; p_dist_day_keep_param VARCHAR(32) := 'mgmt_rt_dist_day_keep_window'; p_elapsed_time_param VARCHAR(30) := 'mgmt_rt_max_elapsed_time'; p_subnet_mask CONSTANT NUMBER := TO_NUMBER('FFFFFF00', 'XXXXXXXX'); EST_RT_ROLLUP_NAME CONSTANT VARCHAR(32) := 'EST_RT_ROLLUP'; -- Default Values ---------------------------------------------------- -- The following values are constant and are the absolute defaults. -- They can be overriden at two levels, (1) globally for all targets -- (table MGMT_PARAMETERS) and (2) specifically for a given target -- (table MGMT_TARGET_PROPERTIES). p_default_raw_keep NUMBER := 24; -- in hours p_default_hour_keep NUMBER := 168; -- in hours (1 week) p_default_dist_hour_keep NUMBER := 24; -- in hours p_default_day_keep NUMBER := 31; -- in days p_default_dist_day_keep NUMBER := 31; -- in days p_default_max_response_time NUMBER := 60000; -- in ms (1 min) p_curdef_raw_keep NUMBER; p_curdef_hour_keep NUMBER; p_curdef_dist_hour_keep NUMBER; p_curdef_day_keep NUMBER; p_curdef_dist_day_keep NUMBER; p_curdef_max_response_time NUMBER; -- Other default values p_target_batchsize PLS_INTEGER := 500; p_rollup_batchsize PLS_INTEGER := 2000; -- rows/bulk insert p_purge_batchsize PLS_INTEGER := 2000; -- rows/bulk delete -- Other default values p_et_dist_type NUMBER := 0; -- elapsed time p_svt_dist_type NUMBER := 1; -- server time p_db_dist_type NUMBER := 2; -- db time -- Package Global Variables ------------------------------------------ -- Current target information p_cur_target_guid RAW(16); p_cur_rollup_timestamps p_date_list_type; p_cur_rollup_rowids p_rowid_list_type; p_cur_raw_keep NUMBER; p_cur_hour_keep NUMBER; p_cur_dist_hour_keep NUMBER; p_cur_day_keep NUMBER; p_cur_dist_day_keep NUMBER; p_cur_max_response_time NUMBER; -- Current target's rollup/purge time boundaries p_cur_hour_rollup DATE; p_cur_day_rollup DATE; p_cur_hour_purge DATE; p_cur_dist_hour_purge DATE; p_cur_day_purge DATE; p_cur_dist_day_purge DATE; -- Trace counters p_rollup_start_time DATE; p_total_rc PLS_INTEGER := 0; p_raw_purge_rc PLS_INTEGER := 0; p_url_1hour_rollup_rc PLS_INTEGER := 0; p_url_dist_1hour_rollup_rc PLS_INTEGER := 0; p_url_1day_rollup_rc PLS_INTEGER := 0; p_url_dist_1day_rollup_rc PLS_INTEGER := 0; p_url_1hour_purge_rc PLS_INTEGER := 0; p_url_dist_1hour_purge_rc PLS_INTEGER := 0; p_url_1day_purge_rc PLS_INTEGER := 0; p_url_dist_1day_purge_rc PLS_INTEGER := 0; p_dom_1hour_rollup_rc PLS_INTEGER := 0; p_dom_dist_1hour_rollup_rc PLS_INTEGER := 0; p_dom_1day_rollup_rc PLS_INTEGER := 0; p_dom_dist_1day_rollup_rc PLS_INTEGER := 0; p_dom_1hour_purge_rc PLS_INTEGER := 0; p_dom_dist_1hour_purge_rc PLS_INTEGER := 0; p_dom_1day_purge_rc PLS_INTEGER := 0; p_dom_dist_1day_purge_rc PLS_INTEGER := 0; p_ip_1hour_rollup_rc PLS_INTEGER := 0; p_ip_dist_1hour_rollup_rc PLS_INTEGER := 0; p_ip_1day_rollup_rc PLS_INTEGER := 0; p_ip_dist_1day_rollup_rc PLS_INTEGER := 0; p_ip_1hour_purge_rc PLS_INTEGER := 0; p_ip_dist_1hour_purge_rc PLS_INTEGER := 0; p_ip_1day_purge_rc PLS_INTEGER := 0; p_ip_dist_1day_purge_rc PLS_INTEGER := 0; p_pr_mapping_1hour_rollup_rc PLS_INTEGER := 0; p_pr_mapping_1day_rollup_rc PLS_INTEGER := 0; p_pr_mapping_1hour_purge_rc PLS_INTEGER := 0; p_pr_mapping_1day_purge_rc PLS_INTEGER := 0; p_inc_loads_1hour_rollup_rc PLS_INTEGER := 0; p_inc_loads_1day_rollup_rc PLS_INTEGER := 0; p_inc_loads_1hour_purge_rc PLS_INTEGER := 0; p_inc_loads_1day_purge_rc PLS_INTEGER := 0; END EMD_RT_ROLLUP; / show errors;