Agresso help and temp tables

Job details

Name:	Agresso help and temp tables
Platform:	Oracle
Category:	Agresso
Description:	Collects information about objects (temporary tables and help tables) created in the agresso application schema.
Long description:	Collects information about objects (temporary tables and help tables) created in the agresso application schema.
Version:	1.3
Default schedule:	20 7,16 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & maj_version > 11]

Parameters

Name	Default value	Description
Agresso schema owner	agr%	The name of the agresso schema. You can specify a list of schemas (separated by comma). You can use % (percent sign) to represent wild card characters.
Help tables prefix	agr%	Prefix used by the Agresso help tables created when running Agresso reports.
Temporary tables prefix	H%	Prefix used by the temporary tables in the Agresso schema.
warning threshold — total size	1000	Warning threshold, in MB, for aggregated size value for all help and tmp tables.
warning threshold — total count	1000	Warning threshold for total count value of all help and tmp tables.
history threshold	366	The number of days the statistics will be kept in the history table.

Job Summary

Purpose: The purpose of this job is to monitor and collect statistics on temporary and help tables created in the Agresso application schema on Oracle databases.
Why: This job is crucial for managing the performance and storage efficiency of databases. By tracking the number and size of these tables, database administrators can ensure efficient resource utilization and identify potential issues arising from excessive use of temporary storage.
Manual checking: To check this information manually, the following SQL commands can be executed:

select sch_name "Schema", h_tab_count "Help tables count", round(h_tab_size/1024,1) "Help tables size", tmp_tab_count "Tmp tables count", round(tmp_tab_size/1024,1) "Tmp tables size", histr_date "Date checked" from agr_help_tabs_schemas_curr order by h_tab_size+tmp_tab_size desc;

Job Implementation Details

Tables and Procedures:
- Two tables are created, agr_help_tabs_schemas_curr and agr_help_tabs_schemas_histr, to store current and historical data about the help and temporary tables respectively.
- A procedure named agr_help_tabs_check is used for populating these tables with the necessary data and performing checks against defined thresholds for table count and size. If the thresholds are exceeded, a warning status is flagged.

Dependencies:
- The job depends on itself, specifically on the agr_help_tabs_check procedure, and its own current and historical data tables for error checking and data consistency.

Dependency Object	Description
agr_help_tabs_check	Main procedure to check and collect table statistics
agr_help_tabs_schemas_curr	Current statistics database table
agr_help_tabs_schemas_histr	Historical statistics database table

Reporting Templates

“Current table statistics” Report:
- Displays detailed statistics like count and size of help and temporary tables in each schema as of the latest checked date.
- Data is displayed in descending order of total size combined.

Historical Analysis Reports

“Agresso Help and Temporary tables history”:
- This report shows the history of table creation each month categorized by type, contributing to trend analysis and growth tracking.

“Table count history”:
- Lists the total count of help and temporary tables over time for each schema, useful for noticing trends in data storage usage.

Additional Information

This job requires instances of Oracle database version higher than 11.
The job is categorized under “Agresso,” emphasizing its specific focus for environments utilizing the Agresso application.
The procedure handles exceptions robustly by logging them in the DBW_ERRORS table, ensuring that any issues during execution are recorded for troubleshooting.