Rem Rem $Header: beacon_e2e_rollup_pkgdef.sql 08-jul-2005.06:13:11 scgrover Exp $ Rem Rem beacon_e2e_rollup_pkgdef.sql Rem Rem Copyright (c) 2002, 2005, Oracle. All rights reserved. Rem Rem NAME Rem beacon_e2e_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 snakai 09/22/03 - remove stmt_keep Rem snakai 08/08/03 - purge sql stmt and conn tables Rem asawant 05/14/03 - Adding JDBC table Rem snakai 05/06/03 - snakai_e2e_agg_schema Rem snakai 12/30/02 - Created Rem CREATE OR REPLACE PACKAGE EMD_E2E_ROLLUP AS -- -- ****** WARNING!! NON-JAVA DECLARATIONS MUST BE BEFORE JAVA !!! ****** -- PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN); PROCEDURE ROLLUP; PROCEDURE EMD_RAW_PURGE; PROCEDURE EMD_SUMMARY_1HOUR_ROLLUP; PROCEDURE EMD_DETAILS_1HOUR_ROLLUP; PROCEDURE EMD_SQL_1HOUR_ROLLUP; PROCEDURE EMD_JDBC_1HOUR_ROLLUP; PROCEDURE EMD_SUMMARY_1HOUR_PURGE; PROCEDURE EMD_DETAILS_1HOUR_PURGE; PROCEDURE EMD_SQL_1HOUR_PURGE; PROCEDURE EMD_JDBC_1HOUR_PURGE; FUNCTION EMD_SUMMARY_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_DETAILS_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_SQL_1HOUR_CLEANUP RETURN BOOLEAN; FUNCTION EMD_JDBC_1HOUR_CLEANUP RETURN BOOLEAN; PROCEDURE EMD_SUMMARY_1DAY_ROLLUP; PROCEDURE EMD_DETAILS_1DAY_ROLLUP; PROCEDURE EMD_SQL_1DAY_ROLLUP; PROCEDURE EMD_JDBC_1DAY_ROLLUP; PROCEDURE EMD_SUMMARY_1DAY_PURGE; PROCEDURE EMD_DETAILS_1DAY_PURGE; PROCEDURE EMD_SQL_1DAY_PURGE; PROCEDURE EMD_JDBC_1DAY_PURGE; FUNCTION EMD_SUMMARY_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_DETAILS_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_SQL_1DAY_CLEANUP RETURN BOOLEAN; FUNCTION EMD_JDBC_1DAY_CLEANUP RETURN BOOLEAN; PROCEDURE EMD_SQL_STMT_CONN_PURGE; PROCEDURE EMD_INITIALIZE; FUNCTION EMD_GET_TARGET_INFO(cur_target_guid IN RAW) RETURN BOOLEAN; TYPE p_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_rowid_list_type IS TABLE OF UROWID INDEX BY BINARY_INTEGER; TYPE p_varchar2_128_list_type IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER; TYPE p_varchar2_256_list_type IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER; TYPE p_varchar2_1000_list_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER; TYPE p_varchar2_2000_list_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; TYPE p_summary_record_type IS RECORD ( vhost p_varchar2_256_list_type, uri p_varchar2_2000_list_type, rollup_time p_date_list_type, hit_count p_number_list_type, total_time p_number_list_type, max_time p_number_list_type, min_time p_number_list_type, servlet_count p_number_list_type, servlet_time p_number_list_type, jsp_count p_number_list_type, jsp_time p_number_list_type, ejb_count p_number_list_type, ejb_time p_number_list_type, jdbc_time p_number_list_type ); TYPE p_details_record_type IS RECORD ( vhost p_varchar2_256_list_type, app_id p_varchar2_128_list_type, uri p_varchar2_2000_list_type, rollup_time p_date_list_type, key_guid p_guid_list_type, parent_key_guid p_guid_list_type, node_id p_varchar2_1000_list_type, node_attr p_varchar2_1000_list_type, node_type p_number_list_type, hit_count p_number_list_type, total_time p_number_list_type, comp_time p_number_list_type ); TYPE p_sql_record_type IS RECORD ( vhost p_varchar2_256_list_type, app_id p_varchar2_128_list_type, uri p_varchar2_2000_list_type, rollup_time p_date_list_type, key_guid p_guid_list_type, exec_count p_number_list_type, exec_time p_number_list_type, fetch_count p_number_list_type, fetch_time p_number_list_type ); TYPE p_jdbc_record_type IS RECORD ( vhost p_varchar2_256_list_type, app_id p_varchar2_128_list_type, uri p_varchar2_2000_list_type, rollup_time p_date_list_type, key_guid p_guid_list_type, used_conn_count p_number_list_type, conn_cache_hit p_number_list_type, conn_cache_miss p_number_list_type, stmt_create_time p_number_list_type, stmt_cache_hit p_number_list_type, stmt_cache_miss p_number_list_type ); p_summary_raw_table VARCHAR(30) := 'MGMT_E2E_SUMMARY'; p_summary_1hour_table VARCHAR(30) := 'MGMT_E2E_SUMMARY_1HOUR'; p_summary_1day_table VARCHAR(30) := 'MGMT_E2E_SUMMARY_1DAY'; p_details_raw_table VARCHAR(30) := 'MGMT_E2E_DETAILS'; p_details_1hour_table VARCHAR(30) := 'MGMT_E2E_DETAILS_1HOUR'; p_details_1day_table VARCHAR(30) := 'MGMT_E2E_DETAILS_1DAY'; p_sql_raw_table VARCHAR(30) := 'MGMT_E2E_SQL'; p_sql_1hour_table VARCHAR(30) := 'MGMT_E2E_SQL_1HOUR'; p_sql_1day_table VARCHAR(30) := 'MGMT_E2E_SQL_1DAY'; p_jdbc_1hour_table VARCHAR(30) := 'MGMT_E2E_JDBC_1HOUR'; p_jdbc_1day_table VARCHAR(30) := 'MGMT_E2E_JDBC_1DAY'; -- The following indexes MUST be ordered alphabetically by table name p_details_1day_idx PLS_INTEGER := 1; p_details_1hour_idx PLS_INTEGER := 2; p_jdbc_1day_idx PLS_INTEGER := 3; p_jdbc_1hour_idx PLS_INTEGER := 4; p_sql_1day_idx PLS_INTEGER := 5; p_sql_1hour_idx PLS_INTEGER := 6; p_summary_1day_idx PLS_INTEGER := 7; p_summary_1hour_idx PLS_INTEGER := 8; p_raw_keep_param VARCHAR(32) := 'mgmt_e2e_raw_keep_window'; p_hour_keep_param VARCHAR(32) := 'mgmt_e2e_hour_keep_window'; p_day_keep_param VARCHAR(32) := 'mgmt_e2e_day_keep_window'; p_target_batchsize PLS_INTEGER := 500; p_rollup_batchsize PLS_INTEGER := 2000; -- rows/bulk insert p_purge_batchsize PLS_INTEGER := 2000; -- rows/bulk delete p_default_raw_keep NUMBER := 24; -- in hours p_default_hour_keep NUMBER := 168; -- in hours (1 week) p_default_day_keep NUMBER := 31; -- in days p_curdef_raw_keep NUMBER; p_curdef_hour_keep NUMBER; p_curdef_day_keep NUMBER; 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_day_keep NUMBER; -- Performance logging variables p_1hour_rollup_time NUMBER := 0; p_1hour_rollup_records NUMBER := 0; p_1day_rollup_time NUMBER := 0; p_1day_rollup_records NUMBER := 0; p_1hour_purge_time NUMBER := 0; p_1hour_purge_records NUMBER := 0; p_1day_purge_time NUMBER := 0; p_1day_purge_records NUMBER := 0; p_raw_purge_time NUMBER := 0; p_raw_purge_records NUMBER := 0; -- Current target's rollup/purge time boundaries p_cur_hour_rollup DATE; p_cur_day_rollup DATE; p_cur_hour_purge DATE; p_cur_day_purge DATE; EST_E2E_ROLLUP_NAME CONSTANT VARCHAR(32) := 'EST_E2E_ROLLUP'; END EMD_E2E_ROLLUP; / show errors;