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

Last updated on MAY 22, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 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:

 
IndicatorView Containing
Information
What this might indicatePotential 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'
       
       
       
       
       
       

 

 

 

 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms