why Database Statistic Collection Job is running two times inside a Maintenance Window? (Doc ID 1576688.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Goal

In an EE 11.2.0.3 on AIX running, the automatic statistic gathering job is defined to run in a weeknight window which is defined from 8PM for 7 hrs runtime:

SQL>
1 select client_name,window_name,window_start_time
2 from dba_autotask_job_history
3 where client_name='auto optimizer stats collection'

CLIENT_NAME WINDOW_NAME WINDOW_START_TIME
----------------------------------- -------------------- ---------------------------------------------------------------------------
auto optimizer stats collection WEEKNIGHT_WINDOW 10-JUL-13 08.00.00.373951 PM +02:00
auto optimizer stats collection WEEKNIGHT_WINDOW 09-JUL-13 08.00.00.533848 PM +02:00
auto optimizer stats collection WEEKNIGHT_WINDOW 08-JUL-13 08.00.00.501908 PM +02:00
auto optimizer stats collection WEEKNIGHT_WINDOW 07-JUL-13 08.00.00.347718 PM +02:00


But why is the gathering optimizer stats job is running two times in one window?
Meaning, why is this job executed two times?

SQL> SELECT job_name, actual_start_date
2 FROM dba_scheduler_job_run_details
3 where status ='SUCCEEDED'
4 and job_name like 'ORA$AT_OS_OPT%'
5 and rownum < 6
6 order by actual_start_date desc;


JOB_NAME ACTUAL_START_DATE
----------------------------------------------------------------- ---------------------------------------------------------------------------
ORA$AT_OS_OPT_SY_2822 10-JUL-13 07.00.02.748351 PM AFRICA/TUNIS
ORA$AT_OS_OPT_SY_2805 09-JUL-13 11.00.55.509541 PM AFRICA/TUNIS
ORA$AT_OS_OPT_SY_2802 09-JUL-13 07.00.02.725150 PM AFRICA/TUNIS
ORA$AT_OS_OPT_SY_2785 08-JUL-13 11.08.43.748181 PM AFRICA/TUNIS
 

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