Analyze tables

Job details

Name:	Analyze tables
Platform:	Oracle
Category:	Maintenance
Premium package:	Maintenance package
Description:	Analyse tables automatically.
Long description:	Analyses tables to improve performance
Version:	1.8
Default schedule:	15 0 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & eleven_and_above=‘1′]

Name	Default value	Description
history threshold	30	Defines how many days we keep data for in the dbw_analyze_table_hist table.
calculate time	2	Defines how long time we can use to analyze statistics in hours. (approximately, since it will not start a new analysis after the time limit, but will complete any analysis started before this time)

Purpose: The purpose of this job is to automatically analyze tables in an Oracle database to compute statistics, aiming to enhance the performance of the database by optimizing query operations and improving data management efficiency.
Why: This job is important as it ensures that the statistics of tables are up to date, which is crucial for the Oracle optimizer to make accurate decisions about the best execution plans for SQL queries. If the statistics are outdated, it can lead to suboptimal query performance and slow processing times.
Manual checking: You can check the status manually within the database by issuing the following SQL command:

SELECT owner, table_name, analyze_time FROM dbw_analyze_table_hist ORDER BY histr_date DESC;

Table/Procedure Name	Description
dbw_analyze_table_hist	Stores historical data of when and which tables are analyzed, including the time taken for analysis.
dbw_not_analyze_tables	Logs tables that failed during the analysis process along with error details.
dbw_analyze_table_proc	Procedure responsible for the analysis of tables. It filters which tables to analyze based on specific criteria, executes the analysis, and handles potential errors by logging them.

The procedure starts by retrieving configuration values (like history threshold and calculate time).
It deletes old records from the history table based on the history threshold.
Opens a cursor to fetch tables in sequence based on their last analysis date which aren’t system-owned or in system-named tablespaces.
In a loop, each table is analyzed using Oracle’s “analyze table” command, compiling statistics for the table, all indexes, and all indexed columns.
Catches any errors during analysis, logs these specifically to the dbw_not_analyze_tables table.
Inserts successful analysis logs into the dbw_analyze_table_hist.
Contains logic to limit the total execution time of the procedure.

This job has self-dependency on dbw_analyze_table_proc and further depends on two additional helper tables for recording historical data and log errors.
It has a clean-up mechanism which activates if a failure occurs, ensuring no corrupt data propagation.

Records and reports on each table’s analysis in terms of execution time and stores this data for performance monitoring and potential troubleshooting.
Organizes the recorded data into a structured report format showing the history of analyzed tables.

Prior to implementation, the job confirms that necessary privileges like ‘ANALYZE ANY DICTIONARY‘ and ‘ANALYZE ANY‘ are granted to the executing schema, ensuring no security lapses during its operations.