Automatic Statistics Gathering does not Complete - Diagnostics Interpretation Guidelines (Doc ID 1902112.1)

Last updated on MARCH 01, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Goal

This article outlines how to interpret the information on the Automatic Statistics Collection Jobs that you would collect to diagnose collection problems. The suggestions are based upon the standard information collected to troubleshoot scheduler issues using the following document:

<Document 1561498.1> Troubleshooting Scheduler Autotask Issues

but with a focus on automatic optimizer statistics.

This document collects information from a number of dictionary views related to the scheduler. Since the automatic statistics job is part of this, the output provides useful information.

The list of diagnostic selects are reproduced below for reference:

The queries can provide specific information on:


Indicator	View Containing Information	What this might indicate	Potential Solution
Is 'auto optimizer stats collection' enabled ?	DBA_AUTOTASK_CLIENT DBA_AUTOTASK_OPERATION DBA_AUTOTASK_TASK	The Job needs to be enabled to run	Enable 'auto optimizer stats collection'
Is ORA$AT_WGRP_OS window group is enabled ?	DBA_AUTOTASK_CLIENT	The Job needs to be enabled to run	Enable ORA$AT_WGRP_OS window group
When is the ORA$AT_WGRP_OS window group scheduled to start next?	DBA_AUTOTASK_CLIENT	If the window group is not being scheduled to run then this may be why statistics are not being collected	Check why the ORA$AT_WGRP_OS window group is not scheduled to be run.
Has the 'auto optimizer stats collection' run in the last 7/30 days and how did it perform?	DBA_AUTOTASK_CLIENT	If the job has not run/completed recently then this may be why statistics are not being collected	Check why job has not run/completed
Has 'auto optimizer stats collection' ever successfully completed?	DBA_AUTOTASK_CLIENT_HISTORY DBA_SCHEDULER_JOB_RUN_DETAILS	If the job completed at some time in the past then something has changed such that it no longer completes.	Check why job has not run/completed
When did 'auto optimizer stats collection' last successfully complete? When did it start to fail?	DBA_AUTOTASK_CLIENT_HISTORY DBA_AUTOTASK_JOB_HISTORY DBA_SCHEDULER_JOB_RUN_DETAILS	If a change has occurred then it was after the last successful completion	Check why job has not run/completed
Is 'auto optimizer stats collection' job running currently? Is it supposed to be running?	DBA_AUTOTASK_CLIENT_JOB DBA_AUTOTASK_TASK	If the job is still running it may be that since it never completes the statistics are not being collected	Check why job has not completed yet
If the job is still running, is there a history of this occurring? When did it start?	DBA_SCHEDULER_JOB_LOG	Identifying when the behavior changed provides a target for investigating what might have changed	Check why job did not completed in past runs
Did the Job stop with an error?	DBA_AUTOTASK_JOB_HISTORY DBA_SCHEDULER_JOB_RUN_DETAILS DBA_SCHEDULER_JOB_LOG	An error may or may not be an indicator of a problem	Check what the error is and how to resolve it.
When did the Job last run successfully?	DBA_AUTOTASK_TASK DBA_SCHEDULER_JOB_RUN_DETAILS DBA_SCHEDULER_JOB_LOG	Something changed after this time such that it was unsuccessful	Look for changes after the job last ran successfully and revert to the previous state.
What was the duration of the last successful job? What is the duration of the unsuccessful jobs?	DBA_AUTOTASK_TASK DBA_SCHEDULER_JOB_RUN_DETAILS	If unsuccessful jobs have an excessive duration then this may be preventing completion	Determine what part of the unsuccessful job is taking all the time. Look for issues and resolutions
Are the maintenance window jobs that run the 'auto optimizer stats collection' enabled ?	DBA_AUTOTASK_WINDOW_CLIENTS DBA_SCHEDULER_WINDOWS	These need to be enabled for the 'auto optimizer stats collection' to run	Enable 'auto optimizer stats collection'
Is the optimizer task enabled?	DBA_AUTOTASK_WINDOW_CLIENTS	The optimizer task needs to be enabled for the 'auto optimizer stats collection' to run	Enable the optimizer task using DBMS_AUTO_TASK_ADMIN
Are the WEEKNIGHT_WINDOW and WEEKEND_WINDOW disabled?	DBA_SCHEDULER_WINDOWS	These are the old 10g windows and may interfere with collection if enabled	Disable the WEEKNIGHT_WINDOW and WEEKEND_WINDOW using DBMS_SCHEDULER.DISABLE
Is there a group for 'auto optimizer stats collection'? Is it enabled? Is the next start date ok?	DBA_SCHEDULER_WINDOW_GROUPS	The group needs to be enabled and the next start date should be scheduled in the future	Enable the group for 'auto optimizer stats collection'