GATHER_STATS_JOB Is Not Running (Doc ID 430636.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.3 [Release 10.1 to 10.2]
Information in this document applies to any platform.



Symptoms


GATHER_STATS_JOB scheduler job is not running in your database.
It could be that you recently applied a DST patch or upgraded the database by applying 10.2.0.3 patch set ,etc

SQL> select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs where job_name=

 2  'GATHER_STATS_JOB';





OWNER JOB_NAME             STATE           SCHEDULE_NAME

----- -------------------- --------------- ---------------------



SYS   GATHER_STATS_JOB     SCHEDULED  MAINTENANCE_WINDOW_GROUP



To check the members under this group , use 



SQL> SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;



WINDOW_GROUP_NAME              WINDOW_NAME

------------------------------ --------------------

MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW

MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW





clear col

set lines 150 pages 99 trimspool on echo on

col window_name for a16

col resource_plan for a13

col repeat_interval for a43

col duration for a15

col enabled for a10

col active for a10



select window_name, resource_plan, 

repeat_interval, duration, enabled, active 

from dba_scheduler_windows;



1  select WINDOW_NAME,RESOURCE_PLAN, START_DATE,REPEAT_INTERVAL,END_DATE,DURATION,ENABLED,ACTIVE 

 2* from dba_scheduler_windows

SQL> /



WINDOW_NAME          RESOURCE_PLAN        START_DATE           REPEAT_INTERVAL                END_DATE                  

-------------------- -------------------- -------------------- ------------------------------ --------------------      

DURATION             ENABL ACTIV                                                                                        

-------------------- ----- -----                                                                                        

WEEKNIGHT_WINDOW                                               freq=daily;byday=MON,TUE,WED,T                           

                                                              HU,FRI;byhour=22;byminute=0; b                           

                                                              ysecond=0                                                

+000 08:00:00        TRUE  TRUE                                                                                         

                                                                                                                       

WEEKEND_WINDOW                                                 freq=daily;byday=SAT;byhour=0;                           

                                                              byminute=0;bysecond=0                                    

+002 00:00:00        TRUE  FALSE                                                                                        

                                                                                    


The job status is shown as scheduled and the windows under the maintenance window group are also enabled

In case the job is disabled (STATE under dba_scheduler_jobs) , then use

EXEC DBMS_SCHEDULER.ENABLE ('GATHER_STATS_JOB');

In case the window is disabled (ENABLED under dba_scheduler_windows) , then use

EXEC DBMS_SCHEDULER.ENABLE ('SYS.WINDOWNAME');

Here the WINDOWNAME has to be replaced with the window which is disabled

Also, remember that for GATHER_STATS_JOB to properly work, you would have to set the STATISTICS_LEVEL initialization parameter to at least TYPICAL.

 

Cause

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