RESCHEDULING OF THE AUTOMATIC OPTIMIZER STATISTICS COLLECTION JOB
REQUEST
I want the Automatic Optimizer Statistics Collection Job (AOSCJ) executes with another time schedule.
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
Version : 14:26 16.01.2018
Platform : Oracle Linux Server 7.3
Database : Oracle database 11.2.0.4
Platform : Oracle Linux Server 7.3
Database : Oracle database 11.2.0.4