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.