Table statistics check

Job details

Name:	Table statistics check
Platform:	Oracle
Category:	Maintenance
Description:	Reacts on old (or missing) statistics in DBA_TABLES dictionary table (column LAST_ANALYZED).
Long description:	Reacts on old (or missing) statistics in DBA_TABLES dictionary table (column LAST_ANALYZED).
Version:	1.6
Default schedule:	10 7 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[ten_and_above=‘1′ & hasengine=‘YES’]

Parameters

Name	Default value	Description
ignore schemas	SYS,SYSMAN,dbwatch,system,DBSNMP,olapsys	Schemas excluded from being checked for table statistics (coma separated).
return status when missing statistics	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing statistics.
return status when old statistics	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when old statistics.
last analyzed threshold	14	Expire time in days (LAST_ANALYZED column in DBA_TABLES dictionary view).

Job Summary

Purpose: The purpose of this job is to monitor and react to outdated or missing statistics in the DBA_TABLES dictionary table, specifically focusing on the LAST_ANALYZED column.
Why: This job is essential because having up-to-date table statistics is critical for the efficient performance of the database’s query optimizer. Missing or outdated statistics can lead to suboptimal execution plans and degraded performance. By automatically checking and alerting on these statistics, potential performance issues can be proactively addressed.
Manual Checking: You can manually check for outdated or missing statistics by executing the following SQL commands:

SELECT owner AS "Schema", TO_NUMBER(TO_CHAR(LAST_ANALYZED, 'yyyymmdd')) AS "Day Analyzed", COUNT(*) AS "Tables Analyzed"
FROM dba_tables
WHERE (LAST_ANALYZED < SYSDATE - :threshold OR LAST_ANALYZED IS NULL)
AND owner NOT IN (SELECT sch_name FROM dbw_excl_schemas)
GROUP BY owner, TO_NUMBER(TO_CHAR(LAST_ANALYZED, 'yyyymmdd'))
ORDER BY 2 ASC;

Detailed Operation Description

The task responds to outdated or missing statistics by assessing the ‘LAST_ANALYZED‘ date in the Oracle database’s ‘DBA_TABLES‘.
Schemas such as SYS, SYSMAN, dbwatch, system, DBSNMP, and olapsys are excluded from checks.
The job can return different statuses based on the severity of the findings, which include:
- ALARM for very outdated statistics,
- WARNING for moderately outdated statistics, and
- OK if all statistics are within the acceptable range defined by the ‘last analyzed threshold’.
The frequency of checks is set for a default schedule which can be customized as per requirement.

Implementation Details

A procedure named ‘table_statistics_check’ is responsible for the actual checking of the ‘LAST_ANALYZED‘ column.
Special handling includes:
- Handling exceptions robustly,
- Updating job statuses in the dbWatch system,
- Utilizing helper tables and procedures for managing the list of excluded schemas.

Reporting Capabilities

“Table statistics check” reports are generated to provide a snapshot of the current statistics status. The primary elements of the report include:

Schema	Day Analyzed	Tables Analyzed
owner	number representing date (yyyymmdd)	count

The report has two main sections:
- General overview of all the tables and their last analyzed date.
- Specific focus on tables with statistics that are outdated or missing.

Reports are set to auto-refresh as per the defined schedule in the monitoring job configuration.

Dependencies and Post-Installation

There are dependencies on self-contained procedures within the task and an external helper table ‘dbw_excl_schemas’ used for managing exclusions.
Post-installation scripts ensure that the necessary database objects and configurations are adequately set up to support ongoing monitoring tasks.
The system is designed to ensure that any changes or updates are propagated correctly during updates or reinstallation of monitoring scripts.