RESCHEDULING OF THE AUTOMATIC OPTIMIZER STATISTICS COLLECTION JOB

 
REQUEST

 
RELATED

 
SOLUTION

Find out the status of AOSCJ

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A35
COL CONSUMER_GROUP FOR A30 
COL WINDOW_GROUP FOR A20 
COL STATUS FOR A10 
SELECT CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,WINDOW_GROUP FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME                         STATUS     CONSUMER_GROUP                 CLIENT WINDOW_GROUP
----------------------------------- ---------- ------------------------------ ------ --------------------
auto optimizer stats collection     ENABLED    ORA$AUTOTASK_STATS_GROUP       OS     ORA$AT_WGRP_OS
auto space advisor                  ENABLED    ORA$AUTOTASK_SPACE_GROUP       SA     ORA$AT_WGRP_SA
sql tuning advisor                  DISABLED   ORA$AUTOTASK_SQL_GROUP         SQ     ORA$AT_WGRP_SQ

AOSCJ is in ORA$AT_WGRP_OS window group which is default for this kind of job. To get what windows names are in the group

COL WINDOW_GROUP_NAME FOR A17
COL WINDOW_NAME FOR A17
SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS WHERE WINDOW_GROUP_NAME='ORA$AT_WGRP_OS';

WINDOW_GROUP_NAME WINDOW_NAME
----------------- -----------------
ORA$AT_WGRP_OS    MONDAY_WINDOW
ORA$AT_WGRP_OS    TUESDAY_WINDOW
ORA$AT_WGRP_OS    WEDNESDAY_WINDOW
ORA$AT_WGRP_OS    THURSDAY_WINDOW
ORA$AT_WGRP_OS    FRIDAY_WINDOW
ORA$AT_WGRP_OS    SATURDAY_WINDOW
ORA$AT_WGRP_OS    SUNDAY_WINDOW

So, ORA$AT_WGRP_OS window group has 7 windows names, 5 for workdays and 2 for weekends. To get deatails about every window name

SET LINES 300 
SET PAGES 999
COL WINDOW_NAME FOR A18
COL REPEAT_INTERVAL FOR A70 
COL DURATION FOR A15
COL SCHEDULE_NAME FOR A30
SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,ENABLED FROM DBA_SCHEDULER_WINDOWS;

WINDOW_NAME        REPEAT_INTERVAL                                                        DURATION        ENABLED
------------------ ---------------------------------------------------------------------- --------------- -------
WEEKNIGHT_WINDOW   freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00   FALSE
WEEKEND_WINDOW     freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00   FALSE
MONDAY_WINDOW      freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                  +000 04:00:00   TRUE
TUESDAY_WINDOW     freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                  +000 04:00:00   TRUE
WEDNESDAY_WINDOW   freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                  +000 04:00:00   TRUE
THURSDAY_WINDOW    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                  +000 04:00:00   TRUE
FRIDAY_WINDOW      freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                  +000 04:00:00   TRUE
SATURDAY_WINDOW    freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00   TRUE
SUNDAY_WINDOW      freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                   +000 20:00:00   TRUE

Workdays window names start daily at 10pm and last for 4 hours. Weekends window names start daily at 6am and last for 20 hours (almost for whole day).
 
There are 2 options to reschedule this job. You can use option 1 or option 2 to create a new window name. Here I modify the schedule without touching original window names. It might be usefull if you want to have an ability to revert all changes back fast.

Option 1 : Creates a window using a named schedule object

           a. Disable AOSCJ [optional]
           b. Create a new schedule object 
           c. Create a new window name and assign a new scheduled object (b) to it
           d. Assign a new windows name (c) to the default maintenance group
           e. Enable AOSCJ with a newly created windows name 
           f. Disable default window names

Option 2 : Creates a window using an inlined schedule

           A. Disable AOSCJ [optional]
           B. Create a new windows name with required repeat interval 
           C. Assign a new windows name (B) to the default maintenance group 
           D. Enable AOSCJ with a newly created windows name 
           E. Disable default window names

Option 1 : Creates a window using a named schedule object

a. Disable AOSCJ [optional]

BEGIN
   DBMS_AUTO_TASK_ADMIN.DISABLE(
   client_name => 'auto optimizer stats collection', 
   operation   => NULL, 
   window_name => NULL);
END;
/

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A35
COL CONSUMER_GROUP FOR A30 
COL WINDOW_GROUP FOR A20 
COL STATUS FOR A10 
SELECT CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,WINDOW_GROUP FROM DBA_AUTOTASK_CLIENT 
WHERE CLIENT_NAME='auto optimizer stats collection';

CLIENT_NAME                         STATUS     CONSUMER_GROUP                 CLIENT WINDOW_GROUP
----------------------------------- ---------- ------------------------------ ------ --------------------
auto optimizer stats collection     DISABLED   ORA$AUTOTASK_STATS_GROUP       OS     ORA$AT_WGRP_OS

b. Create a new schedule object. For this example schedule will start at 10am from Monday to Friday. A name of a new schedule name can be anything you want.

BEGIN 
   DBMS_SCHEDULER.CREATE_SCHEDULE(
   SCHEDULE_NAME   => 'GATHER_STATS_SCHEDULE',
   START_DATE      => SYSTIMESTAMP,
   REPEAT_INTERVAL => 'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=10;byminute=0; bysecond=0',
   END_DATE        => NULL, 
   COMMENTS        => 'REPEATS DAYLY (MON-FRI) AT 10AM FOREVER.');
END;
/


-- To delete 
-- BEGIN 
-- DBMS_SCHEDULER.DROP_SCHEDULE(SCHEDULE_NAME => 'GATHER_STATS_SCHEDULE');
-- END;
-- /

COL OWNER FOR A5 
COL SCHEDULE_NAME FOR A23
col START_DATE for a40 
col REPEAT_INTERVAL for a70 
col END_DATE for a10 
col COMMENTS for a40

SELECT OWNER,SCHEDULE_NAME,REPEAT_INTERVAL FROM DBA_SCHEDULER_SCHEDULES WHERE SCHEDULE_NAME='GATHER_STATS_SCHEDULE';

OWNER SCHEDULE_NAME           REPEAT_INTERVAL
----- ----------------------- ----------------------------------------------------------------------
SYS   GATHER_STATS_SCHEDULE   freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=10;byminute=0; bysecond=0

SELECT START_DATE,END_DATE,COMMENTS FROM DBA_SCHEDULER_SCHEDULES WHERE SCHEDULE_NAME='GATHER_STATS_SCHEDULE';

START_DATE                               END_DATE   COMMENTS
---------------------------------------- ---------- ----------------------------------------
20-NOV-17 03.02.32.288838 PM +03:00                 REPEATS DAYLY (MON-FRI) AT 10AM FOREVER.

c. Create a new window name and assign a new scheduled object (b) to it. A name of a new window name can be anything you want. In this example duration of a new window name is 4 hours.

BEGIN   
	DBMS_SCHEDULER.CREATE_WINDOW ( 
	window_name     => 'GATHERSTATISTICS_MON_FRI',
	resource_plan   =>'DEFAULT_MAINTENANCE_PLAN',
	schedule_name   => 'GATHER_STATS_SCHEDULE',
	duration        => interval '4' hour, 
	window_priority => 'LOW',
	comments        => 'Window name for the gather statistic job to be run during business hours. Used instead of default window names.');
END;
/

-- To remove
-- BEGIN   
-- DBMS_SCHEDULER.DROP_WINDOW (window_name => 'GATHERSTATISTICS_MON_FRI');
-- END;
-- /

SET LINES 300 
SET PAGES 999
COL WINDOW_NAME for a25
COL DURATION FOR A15
COL SCHEDULE_NAME FOR A21
COL ENABLED FOR A7
SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,SCHEDULE_NAME,ENABLED FROM DBA_SCHEDULER_WINDOWS 
WHERE WINDOW_NAME=UPPER('GATHERSTATISTICS_MON_FRI');

WINDOW_NAME               REPEAT_INT DURATION        SCHEDULE_NAME         ENABLED
------------------------- ---------- --------------- --------------------- -------
GATHERSTATISTICS_MON_FRI             +000 04:00:00   GATHER_STATS_SCHEDULE TRUE

d. Assign a new windows name (c) to the default maintenance group. The default maintenance group is used for automated tasks

BEGIN   
   DBMS_SCHEDULER.ADD_GROUP_MEMBER(
   group_name => 'MAINTENANCE_WINDOW_GROUP',
   member     => 'GATHERSTATISTICS_MON_FRI');
END;
/


-- To remove 
-- BEGIN   
--   DBMS_SCHEDULER.REMOVE_GROUP_MEMBER(
--   group_name => 'MAINTENANCE_WINDOW_GROUP',
--   member     => 'GATHERSTATISTICS_MON_FRI');
-- END;
-- /

e. Enable AOSCJ with a newly created windows name

BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE(
   client_name => 'auto optimizer stats collection', 
   operation => NULL, 
   window_name => 'GATHERSTATISTICS_MON_FRI');
END;
/

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A31
COL CONSUMER_GROUP FOR A25
COL WINDOW_GROUP FOR A15 
COL STATUS FOR A7 
SELECT CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,WINDOW_GROUP FROM DBA_AUTOTASK_CLIENT 
WHERE CLIENT_NAME='auto optimizer stats collection';

CLIENT_NAME                     STATUS  CONSUMER_GROUP            CLIENT WINDOW_GROUP
------------------------------- ------- ------------------------- ------ ---------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP  OS     ORA$AT_WGRP_OS

COL WINDOW_GROUP_NAME for a20 
SELECT * from DBA_SCHEDULER_WINGROUP_MEMBERS where WINDOW_GROUP_NAME='ORA$AT_WGRP_OS';

WINDOW_GROUP_NAME    WINDOW_NAME
-------------------- ------------------------------
ORA$AT_WGRP_OS       MONDAY_WINDOW
ORA$AT_WGRP_OS       TUESDAY_WINDOW
ORA$AT_WGRP_OS       WEDNESDAY_WINDOW
ORA$AT_WGRP_OS       THURSDAY_WINDOW
ORA$AT_WGRP_OS       FRIDAY_WINDOW
ORA$AT_WGRP_OS       SATURDAY_WINDOW
ORA$AT_WGRP_OS       SUNDAY_WINDOW
ORA$AT_WGRP_OS       GATHERSTATISTICS_MON_FRI

COL WINDOW_NAME FOR A25
COL WINDOW_NEXT_TIME for a35
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15 
SELECT WINDOW_NAME,WINDOW_NEXT_TIME,AUTOTASK_STATUS STATUS,OPTIMIZER_STATS FROM DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME               WINDOW_NEXT_TIME                    STATUS          OPTIMIZER_STATS
------------------------- ----------------------------------- --------------- ---------------
MONDAY_WINDOW             22-JAN-18 10.00.00.000000 PM +03:00 ENABLED         ENABLED
TUESDAY_WINDOW            16-JAN-18 10.00.00.000000 PM +03:00 ENABLED         ENABLED
WEDNESDAY_WINDOW          17-JAN-18 10.00.00.000000 PM +03:00 ENABLED         ENABLED
THURSDAY_WINDOW           18-JAN-18 10.00.00.000000 PM +03:00 ENABLED         ENABLED
FRIDAY_WINDOW             19-JAN-18 10.00.00.000000 PM +03:00 ENABLED         ENABLED
SATURDAY_WINDOW           20-JAN-18 06.00.00.000000 AM +03:00 ENABLED         ENABLED
SUNDAY_WINDOW             21-JAN-18 06.00.00.000000 AM +03:00 ENABLED         ENABLED
GATHERSTATISTICS_MON_FRI  16-JAN-18 10.00.00.000000 AM +03:00 ENABLED         ENABLED

f. Disable default window names

BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'auto optimizer stats collection', 
   operation   => NULL, 
   window_name => 'MONDAY_WINDOW');
END;
/

BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'auto optimizer stats collection', 
   operation   => NULL, 
   window_name => 'TUESDAY_WINDOW');
END;
/

BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'auto optimizer stats collection', 
   operation   => NULL, 
   window_name => 'WEDNESDAY_WINDOW');
END;
/

BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'auto optimizer stats collection', 
   operation   => NULL, 
   window_name => 'THURSDAY_WINDOW');
END;
/


BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'auto optimizer stats collection', 
   operation   => NULL, 
   window_name => 'FRIDAY_WINDOW');
END;
/


BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'auto optimizer stats collection', 
   operation   => NULL, 
   window_name => 'SATURDAY_WINDOW');
END;
/

BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'auto optimizer stats collection', 
   operation   => NULL, 
   window_name => 'SUNDAY_WINDOW');
END;
/

COL WINDOW_NAME FOR A25
COL WINDOW_NEXT_TIME for a35
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15 
SELECT WINDOW_NAME,WINDOW_NEXT_TIME,AUTOTASK_STATUS STATUS,OPTIMIZER_STATS FROM DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME               WINDOW_NEXT_TIME                    STATUS          OPTIMIZER_STATS
------------------------- ----------------------------------- --------------- ---------------
MONDAY_WINDOW             22-JAN-18 10.00.00.000000 PM +03:00 ENABLED         DISABLED
TUESDAY_WINDOW            16-JAN-18 10.00.00.000000 PM +03:00 ENABLED         DISABLED
WEDNESDAY_WINDOW          17-JAN-18 10.00.00.000000 PM +03:00 ENABLED         DISABLED
THURSDAY_WINDOW           18-JAN-18 10.00.00.000000 PM +03:00 ENABLED         DISABLED
FRIDAY_WINDOW             19-JAN-18 10.00.00.000000 PM +03:00 ENABLED         DISABLED
SATURDAY_WINDOW           20-JAN-18 06.00.00.000000 AM +03:00 ENABLED         DISABLED
SUNDAY_WINDOW             21-JAN-18 06.00.00.000000 AM +03:00 ENABLED         DISABLED
GATHERSTATISTICS_MON_FRI  16-JAN-18 10.00.00.000000 AM +03:00 ENABLED         ENABLED

Option 2 : Creates a window using an inlined schedule

A. Disable AOSCJ [optional] by the same way as in Option 1 step a
B. Create a new windows name with required repeat interval. In this example a new windows name will start at 4am and last for 5 hours

BEGIN   
   DBMS_SCHEDULER.CREATE_WINDOW ( 
   window_name     => 'GATHERSTATISTICS_MON_FRI_OP2',
   resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
   start_date      => SYSTIMESTAMP,
   repeat_interval => 'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=4;byminute=0; bysecond=0',
   end_date        => NULL,
   duration        => interval '5' hour, 
   window_priority => 'LOW',
   comments        => 'Custom window name for the gather statistics job. Starts at 4am and last for 5 hours.');
END;
/

-- To remove
-- BEGIN   
-- DBMS_SCHEDULER.DROP_WINDOW (window_name => 'GATHERSTATISTICS_MON_FRI_OP2');
-- END;
-- /

SET LINES 300 
SET PAGES 999
COL WINDOW_NAME FOR A18
COL REPEAT_INTERVAL FOR A70 
COL DURATION FOR A15
COL SCHEDULE_NAME FOR A30
SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,ENABLED FROM DBA_SCHEDULER_WINDOWS;

WINDOW_NAME                  REPEAT_INTERVAL                                                      DURATION      ENABLED 
---------------------------- -------------------------------------------------------------------- ------------- -------
GATHERSTATISTICS_MON_FRI_OP2 freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=4;byminute=0; bysecond=0 +000 05:00:00 TRUE

C. Assign a new windows name (B) to the default maintenance group.
D. Enable AOSCJ with a newly created windows name
E. Disable default window names
For steps C-E do the same actions as in Option 1 steps d-f

To get details about all AOSCJ exectutions

SET LINES 300
SET PAGES 999
COL WINDOW_NAME FOR A25
COL WINDOW_START_TIME FOR A20 
COL JOB_NAME FOR A22 
COL JOB_START_TIME_MAN FOR A20 
COL JOB_DURATION FOR A15 
COL JOB_INFO FOR A2 
COL JOB_STATUS FOR A10 
COL WINDOW_DURATION FOR A26 

SELECT * FROM (SELECT WINDOW_NAME,TO_CHAR(WINDOW_START_TIME,'DD-MM-YYYY HH24:MI:SS') WINDOW_START_TIME,WINDOW_DURATION,JOB_NAME,
JOB_STATUS,TO_CHAR(JOB_START_TIME,'DD-MM-YYYY HH24:MI:SS') JOB_START_TIME_MAN,JOB_DURATION,JOB_ERROR FROM DBA_AUTOTASK_JOB_HISTORY 
WHERE CLIENT_NAME='auto optimizer stats collection' ORDER BY JOB_START_TIME DESC) WHERE ROWNUM < 5;

WINDOW_NAME               WINDOW_START_TIME    WINDOW_DURATION            JOB_NAME               JOB_STATUS JOB_START_TIME_MAN   JOB_DURATION     JOB_ERROR
------------------------- -------------------- -------------------------- ---------------------- ---------- -------------------- --------------- ----------
GATHERSTATISTICS_MON_FRI  16-01-2018 10:00:00  +000000000 03:59:59.991223 ORA$AT_OS_OPT_SY_10742 SUCCEEDED  16-01-2018 10:00:01  +000 00:01:06            0
GATHERSTATISTICS_MON_FRI  15-01-2018 10:00:00  +000000000 03:59:59.923946 ORA$AT_OS_OPT_SY_10722 SUCCEEDED  15-01-2018 10:00:02  +000 00:03:22            0
GATHERSTATISTICS_MON_FRI  12-01-2018 10:00:01  +000000000 03:59:59.030701 ORA$AT_OS_OPT_SY_10664 SUCCEEDED  12-01-2018 10:00:02  +000 00:01:18            0
GATHERSTATISTICS_MON_FRI  11-01-2018 10:00:00  +000000000 04:00:00.153721 ORA$AT_OS_OPT_SY_10662 SUCCEEDED  11-01-2018 10:00:01  +000 00:04:08            0