Agresso CON$ system table

Job details

Name:	Agresso CON$ system table
Platform:	Oracle
Category:	Agresso
Description:	Checks the size of all indexes defined on SYS.CON$ system table.
Long description:	Checks the size of all indexes defined on SYS.CON$ system table.
Version:	1.3
Default schedule:	10 8,18 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & maj_version > 11]

Name	Default value	Description
I_CON1 max index size	200	The warning threshold for SYS.I_CON1 system index size (in MB).
min. histr. interval	3	The minimum number of days before the statistics are collected in the history tables.

Purpose: This job checks the size of all indexes defined on the SYS.CON$ system table in an Oracle database environment.
Why: Monitoring the size of system indexes like those on the SYS.CON$ table is crucial for assessing database performance and ensuring efficient indexing. Excessive growth in index size could indicate deeper issues like suboptimal queries or a need for reindexing, which could degrade performance if left unchecked.
Manual Checking: You can manually check the sizes of these indexes in the Oracle database using the following SQL commands:

SELECT segment_name, bytes FROM dba_segments WHERE owner='SYS' AND segment_name IN ('CON$', 'I_CON1', 'I_CON2');

The job involves several key operations:

Monitoring the size of the SYS.CON$ table and its related indexes.
Comparing current index sizes against predefined thresholds (e.g., maximum permissible size for the I_CON1 index).
Inserting gathered data regarding the index sizes into history tables for tracking and trend analysis.
Raising alerts if the size of any index exceeds the allowable maximum size.

To support the monitoring process, this job depends on specific database objects:

Schema	Object	Object Type	Description	Cleanup On Fail
;;dbw~schema;;	proc_con$_check	2 (Procedure)	n/a	true
;;dbw~schema;;	dbw_con$_histr	0 (Table)	History table, collects size for SYS.CON$ table and their indexes.	true
;;dbw~schema;;	dbw_con#_col_histr	0 (Table)	n/a	true

Additionally, the following SQL table creation and procedure are crucial:

CREATE TABLE dbw_con$_histr (segment_type VARCHAR2(20), segment_name VARCHAR2(90), segment_size NUMBER, histr_date DATE)

CREATE TABLE dbw_con#_col_histr (con#_val NUMBER, con#_val_dif NUMBER, period NUMBER, histr_date DATE)

CREATE OR REPLACE PROCEDURE proc_con$_check(taskid in number) IS ...

The job also includes reports that visualize the SYS.CON$ table and index sizes:

“CON$ system table statistics” – Displays the latest size statistics for the SYS.CON$ table and indexes.
Data presented include segment type, name, and size in descending order.
Historical data plot for the SYS.CON$ table and index sizes over time.
New constraints’ creation rate history graphed against time.

These reports help in understanding patterns over time, assisting database administrators in proactive database management.