HOW TO RESCHEDULE THE AUTOMATIC OPTIMIZER STATISTICS COLLECTION JOB
The post provides a number of steps to follow to run the Automatic Optimizer Statistics Collection Job with another time schedule.
RELATED
HOW TO RESCHEDULE THE AUTOMATIC OPTIMIZER STATISTICS COLLECTION JOB
SOLUTION
Ensure that the Automatic Optimizer Statistics Collection Job is ENABLED.
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 CL WINDOW_GROUP
----------------------------------- ---------- ------------------------------ -- --------------------
sql tuning advisor ENABLED ORA$AUTOTASK SQ ORA$AT_WGRP_SQ
auto optimizer stats collection ENABLED ORA$AUTOTASK OS ORA$AT_WGRP_OS
auto space advisor ENABLED ORA$AUTOTASK SA ORA$AT_WGRP_SAHere is the Automatic Optimizer Statistics Collection Job is ENABLED and assigned to the ORA$AT_WGRP_OS window group.
Logically a window group is like a container with different schedules. By default the ORA$AT_WGRP_OS group has 7 schedules which called as WINDOW NAME.
COL WINDOW_GROUP_NAME FOR A20
COL WINDOW_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
7 rows selected.Every WINDOW NAME is linked to a specific day of the week and runs at a specific period of time. The DBA_SCHEDULER_WINDOWS view contains schedule details about every WINDOW NAME
SET LINES 300
SET PAGES 999
COL WINDOW_NAME FOR A18
COL REPEAT_INTERVAL FOR A72
COL WINDOW_DURATION FOR A32
COL ENABLED FOR A10
SELECT WINDOW_NAME,
REPEAT_INTERVAL,
TO_CHAR(EXTRACT(DAY FROM DURATION),'90') || ' Days ' ||
TO_CHAR(EXTRACT(HOUR FROM DURATION),'90') || ' Hours ' ||
TO_CHAR(EXTRACT(MINUTE FROM DURATION),'90') || ' Minutes '
WINDOW_DURATION,
ENABLED
FROM
DBA_SCHEDULER_WINDOWS;WINDOW_NAME REPEAT_INTERVAL WINDOW_DURATION ENABLED
---------------- ---------------------------------------------------------------------- -------------------------------- ----------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 0 Days 4 Hours 0 Minutes TRUE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 0 Days 4 Hours 0 Minutes TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 0 Days 4 Hours 0 Minutes TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 0 Days 4 Hours 0 Minutes TRUE
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 0 Days 4 Hours 0 Minutes TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 0 Days 20 Hours 0 Minutes TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 0 Days 20 Hours 0 Minutes TRUE
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 0 Days 8 Hours 0 Minutes FALSE
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 2 Days 0 Hours 0 Minutes FALSE
9 rows selected.As can be seen from the above output the Monday-To-Friday WINDOW NAME start on Weekdays at 10PM and last for 4 hours, and the Saturday-To-Sunday WINDOW NAME start on Weekends at 6AM and last for 20 hours.
There are 2 options to schedule the Automatic Optimizer Statistics Collection Job at another time. Both options include creating a new WINDOW NAME. In this demo I modify the schedule of the job without touching original WINDOW NAME schedules. It might be useful if you want to have the ability to reset all changes fast.
Option 1 : Creates a WINDOW NAME using a Named Schedule Object a. Disable the Automatic Optimizer Statistics Collection Job [optional] b. Create a new Named Schedule Object c. Create a new Window Name and assign a created Named Scheduled Object (b) to it d. Assign a created Windows Name (c) to the default Maintenance Group e. Enable the Automatic Optimizer Statistics Collection Job with a newly created Windows Name f. Disable default Window Names Option 2 : Creates a WINDOW NAME using an Inlined Schedule A. Disable the Automatic Optimizer Statistics Collection Job [optional] B. Create a new Windows Name with required repeat interval C. Assign a created Windows Name (B) to the default Maintenance group D. Enable the Automatic Optimizer Statistics Collection Job with a newly created Windows Name E. Disable default Window Names
Option 1 : Creates a Window Name using a Named Schedule Object
a. Disable the Automatic Optimizer Statistics Collection Job [optional]
-- Disable the Auto Optimizer Stats Collection job
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
CLIENT_NAME => 'auto optimizer stats collection',
OPERATION => NULL,
WINDOW_NAME => NULL);
END;
/
-- Confirm that it's been disabled
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 Named Schedule Object.
In this example, the schedule will start at 10AM from Monday to Friday. A name of a new Named Schedule Object (see SCHEDULE_NAME parameter) can be anything you want.
-- Define any name for a new Named Schedule Object
DEFINE SCHEDULE_NAME = 'GATHER_STATS_SCHEDULE'
-- Create a new Named Schedule Object
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
SCHEDULE_NAME => '&SCHEDULE_NAME',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=10;byminute=0; bysecond=0',
END_DATE => NULL,
COMMENTS => 'REPEATS DAILY (MON-FRI) AT 10AM FOREVER.');
END;
/
-- To remove the created Named Scheduled Object
/*
EXEC DBMS_SCHEDULER.DROP_SCHEDULE(SCHEDULE_NAME => '&SCHEDULE_NAME');
*/
-- Query repeat interval for the created Named Scheduled Object
SET LINES 300
SET PAGES 999
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='&SCHEDULE_NAME';
OWNER SCHEDULE_NAME REPEAT_INTERVAL
----- ----------------------- ----------------------------------------------------------------------
SYS GATHER_STATS_SCHEDULE freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=10;byminute=0; bysecond=0
-- Query when the created Named Schedule Object will be started
COL START_DATE FOR A20
COL END_DATE FOR A20
SELECT TO_CHAR(START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE,
TO_CHAR(END_DATE,'DD-MON-YYYY HH24:MI:SS') END_DATE,
COMMENTS
FROM
DBA_SCHEDULER_SCHEDULES
WHERE
SCHEDULE_NAME='&SCHEDULE_NAME';
START_DATE END_DATE COMMENTS
-------------------- -------------------- ----------------------------------------
25-SEP-2022 01:38:11 REPEATS DAILY (MON-FRI) AT 10AM FOREVER.c. Create a new Window Name and assign a created Named Scheduled Object (b) to it.
A name of a new Window Name (see WINDOW_NAME parameter) can be anything you want. In this example, the duration of a new Window Name is 4 hours.
-- Define any name for a new Window Name
DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'
-- Create a new Window Name with the GATHER_STATS_SCHEDULE assigned to it
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
WINDOW_NAME => '&WINDOW_NAME',
RESOURCE_PLAN => 'DEFAULT_MAINTENANCE_PLAN',
SCHEDULE_NAME => 'GATHER_STATS_SCHEDULE',
DURATION => interval '4' hour,
WINDOW_PRIORITY => 'LOW',
COMMENTS => 'Weekdays Gather Statistic Job. Used instead of default window names.');
END;
/
-- To remove the created Window Name
/*
EXEC DBMS_SCHEDULER.DROP_WINDOW (window_name => '&WINDOW_NAME');
*/
-- Confirm that the Windows Name is created
SET LINES 300
SET PAGES 999
COL WINDOW_NAME for a25
COL REPEAT_INTERVAL FOR A15
COL DURATION FOR A30
COL SCHEDULE_NAME FOR A21
COL ENABLED FOR A7
SELECT WINDOW_NAME,
REPEAT_INTERVAL,
TO_CHAR(EXTRACT(DAY FROM DURATION),'90') || ' Days' ||
TO_CHAR(EXTRACT(HOUR FROM DURATION),'00') || ' Hours' ||
TO_CHAR(EXTRACT(MINUTE FROM DURATION),'00') || ' Minutes '
DURATION,
SCHEDULE_NAME,
ENABLED
FROM
DBA_SCHEDULER_WINDOWS
WHERE
WINDOW_NAME=UPPER('&WINDOW_NAME');
old 3: WHERE WINDOW_NAME=UPPER('&WINDOW_NAME')
new 3: WHERE WINDOW_NAME=UPPER('GATHERSTATISTICS_MON_FRI')
WINDOW_NAME REPEAT_INTERVAL DURATION SCHEDULE_NAME ENABLED
------------------------- --------------- ------------------------------ --------------------- -------
GATHERSTATISTICS_MON_FRI 0 Days 04 Hours 00 Minutes GATHER_STATS_SCHEDULE TRUEd. Assign a created Windows Name (c) to the default Maintenance Group.
The default Maintenance Group is used for automated tasks
-- Add the created Window Name to the Window Group
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'
BEGIN
DBMS_SCHEDULER.ADD_GROUP_MEMBER(
GROUP_NAME => 'MAINTENANCE_WINDOW_GROUP',
MEMBER => '&WINDOW_NAME');
END;
/
-- To exclude the created Window Name from the Window Group
/*
BEGIN
DBMS_SCHEDULER.REMOVE_GROUP_MEMBER(
GROUP_NAME => 'MAINTENANCE_WINDOW_GROUP',
MEMBER => '&WINDOW_NAME');
END;
/
*/
e. Enable the Automatic Optimizer Statistics Collection Job with a newly created Windows Name
-- Enable the job
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
CLIENT_NAME => 'auto optimizer stats collection',
OPERATION => NULL,
WINDOW_NAME => '&WINDOW_NAME');
END;
/
-- Confirm that it is enabled
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
-- Verify that created Window Name are in the list of the Window Group
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
-- Statuses of all available Window Names in the Window Group
COL WINDOW_NAME FOR A25
COL WINDOW_NEXT_TIME for a25
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15
SELECT WINDOW_NAME,
TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
AUTOTASK_STATUS STATUS,
OPTIMIZER_STATS
FROM
DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME STATUS OPTIMIZER_STATS
------------------------- ------------------------- --------------- ---------------
MONDAY_WINDOW 26-SEP-2022 22:00:00 ENABLED ENABLED
TUESDAY_WINDOW 27-SEP-2022 22:00:00 ENABLED ENABLED
WEDNESDAY_WINDOW 28-SEP-2022 22:00:00 ENABLED ENABLED
THURSDAY_WINDOW 29-SEP-2022 22:00:00 ENABLED ENABLED
FRIDAY_WINDOW 30-SEP-2022 22:00:00 ENABLED ENABLED
SATURDAY_WINDOW 24-SEP-2022 06:00:00 ENABLED ENABLED
SUNDAY_WINDOW 25-SEP-2022 06:00:00 ENABLED ENABLED
GATHERSTATISTICS_MON_FRI 26-SEP-2022 10:00:00 ENABLED ENABLED
8 rows selected.f. Disable default Window Names
-- Disable defaults through the LOOP
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'
BEGIN
FOR W IN (SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS
WHERE WINDOW_NAME!='&WINDOW_NAME')
LOOP
BEGIN
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => W.WINDOW_NAME);
END;
END LOOP;
END;
/
-- Confirm that all default Window Names are disabled
COL WINDOW_NAME FOR A25
COL WINDOW_NEXT_TIME for a25
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15
SELECT WINDOW_NAME,
TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
AUTOTASK_STATUS STATUS,
OPTIMIZER_STATS
FROM
DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME STATUS OPTIMIZER_STATS
------------------------- ------------------------- --------------- ---------------
MONDAY_WINDOW 26-SEP-2022 22:00:00 ENABLED DISABLED
TUESDAY_WINDOW 27-SEP-2022 22:00:00 ENABLED DISABLED
WEDNESDAY_WINDOW 28-SEP-2022 22:00:00 ENABLED DISABLED
THURSDAY_WINDOW 29-SEP-2022 22:00:00 ENABLED DISABLED
FRIDAY_WINDOW 30-SEP-2022 22:00:00 ENABLED DISABLED
SATURDAY_WINDOW 24-SEP-2022 06:00:00 ENABLED DISABLED
SUNDAY_WINDOW 25-SEP-2022 06:00:00 ENABLED DISABLED
GATHERSTATISTICS_MON_FRI 26-SEP-2022 10:00:00 ENABLED ENABLED
8 rows selected.To RESET all changes made in the Option 1
-- Names for the Named Schedule Object and Window Name
DEFINE SCHEDULE_NAME = 'GATHER_STATS_SCHEDULE'
DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'
-- Drop the Named Schedule Object and Window Name
EXEC DBMS_SCHEDULER.DROP_WINDOW(WINDOW_NAME => '&WINDOW_NAME');
EXEC DBMS_SCHEDULER.DROP_SCHEDULE(SCHEDULE_NAME => '&SCHEDULE_NAME');
-- Enable default Window Names
BEGIN
FOR W IN (SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS)
LOOP
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
CLIENT_NAME => 'auto optimizer stats collection',
OPERATION => NULL,
WINDOW_NAME => W.WINDOW_NAME);
END;
END LOOP;
END;
/
-- Confirm that all default Window Names are enabled
COL WINDOW_NAME FOR A25
COL WINDOW_NEXT_TIME for a25
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15
SELECT WINDOW_NAME,
TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
AUTOTASK_STATUS STATUS,
OPTIMIZER_STATS
FROM
DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME STATUS OPTIMIZER_STATS
------------------------- ------------------------- --------------- ---------------
MONDAY_WINDOW 26-SEP-2022 22:00:00 ENABLED ENABLED
TUESDAY_WINDOW 27-SEP-2022 22:00:00 ENABLED ENABLED
WEDNESDAY_WINDOW 28-SEP-2022 22:00:00 ENABLED ENABLED
THURSDAY_WINDOW 29-SEP-2022 22:00:00 ENABLED ENABLED
FRIDAY_WINDOW 30-SEP-2022 22:00:00 ENABLED ENABLED
SATURDAY_WINDOW 24-SEP-2022 06:00:00 ENABLED ENABLED
SUNDAY_WINDOW 25-SEP-2022 06:00:00 ENABLED ENABLED
7 rows selected.
Option 2 : Creates a Window Name using an Inlined Schedule
A. Disable the Automatic Optimizer Statistics Collection Job [optional]
-- Disable the Auto Optimizer Stats Collection job
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
CLIENT_NAME => 'auto optimizer stats collection',
OPERATION => NULL,
WINDOW_NAME => NULL);
END;
/
-- Confirm that it's been disabled
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 Windows Name with required REPEAT_INTERVAL.
In this example a new Windows Name will start at 4AM and last for 5 hours
-- Define any name for a new Window Name
DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
WINDOW_NAME => '&WINDOW_NAME',
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 the created Window Name
/*
EXEC DBMS_SCHEDULER.DROP_WINDOW (window_name => '&WINDOW_NAME');
*/
-- Confirm that the Windows Name is created
SET LINES 300
SET PAGES 999
COL WINDOW_NAME FOR A28
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
WHERE WINDOW_NAME=UPPER('&WINDOW_NAME');
old 3: WHERE WINDOW_NAME=UPPER('&WINDOW_NAME')
new 3: WHERE WINDOW_NAME=UPPER('GATHERSTATISTICS_MON_FRI_OP2')
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 TRUEC. Assign a created Windows Name (B) to the default Maintenance group.
The default Maintenance Group is used for automated tasks
-- Add the created Window Name to the Window Group
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'
BEGIN
DBMS_SCHEDULER.ADD_GROUP_MEMBER(
GROUP_NAME => 'MAINTENANCE_WINDOW_GROUP',
MEMBER => '&WINDOW_NAME');
END;
/
-- To exclude the created Window Name from the Window Group
/*
BEGIN
DBMS_SCHEDULER.REMOVE_GROUP_MEMBER(
GROUP_NAME => 'MAINTENANCE_WINDOW_GROUP',
MEMBER => '&WINDOW_NAME');
END;
/
*/
D. Enable the Automatic Optimizer Statistics Collection Job with a newly created Windows Name.
-- Enable the job
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
CLIENT_NAME => 'auto optimizer stats collection',
OPERATION => NULL,
WINDOW_NAME => '&WINDOW_NAME');
END;
/
-- Confirm that it is enabled
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
-- Verify that created Window Name are in the list of the Window Group
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_OP2
8 rows selected.
-- Statuses of all available Window Names in the Window Group
COL WINDOW_NAME FOR A30
COL WINDOW_NEXT_TIME for a25
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15
SELECT WINDOW_NAME,
TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
AUTOTASK_STATUS STATUS,
OPTIMIZER_STATS
FROM
DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME STATUS OPTIMIZER_STATS
------------------------------ ------------------------- --------------- ---------------
MONDAY_WINDOW 26-SEP-2022 22:00:00 ENABLED ENABLED
TUESDAY_WINDOW 27-SEP-2022 22:00:00 ENABLED ENABLED
WEDNESDAY_WINDOW 28-SEP-2022 22:00:00 ENABLED ENABLED
THURSDAY_WINDOW 29-SEP-2022 22:00:00 ENABLED ENABLED
FRIDAY_WINDOW 30-SEP-2022 22:00:00 ENABLED ENABLED
SATURDAY_WINDOW 24-SEP-2022 06:00:00 ENABLED ENABLED
SUNDAY_WINDOW 25-SEP-2022 06:00:00 ENABLED ENABLED
GATHERSTATISTICS_MON_FRI_OP2 26-SEP-2022 04:00:00 ENABLED ENABLED
8 rows selected.E. Disable default Window Names.
-- Disable defaults through the LOOP
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'
BEGIN
FOR W IN (SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS
WHERE WINDOW_NAME!='&WINDOW_NAME')
LOOP
BEGIN
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => W.WINDOW_NAME);
END;
END LOOP;
END;
/
-- Confirm that all default Window Names are disabled
COL WINDOW_NAME FOR A30
COL WINDOW_NEXT_TIME for a25
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15
SELECT WINDOW_NAME,
TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
AUTOTASK_STATUS STATUS,
OPTIMIZER_STATS
FROM
DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME STATUS OPTIMIZER_STATS
------------------------------ ------------------------- --------------- ---------------
MONDAY_WINDOW 26-SEP-2022 22:00:00 ENABLED DISABLED
TUESDAY_WINDOW 27-SEP-2022 22:00:00 ENABLED DISABLED
WEDNESDAY_WINDOW 28-SEP-2022 22:00:00 ENABLED DISABLED
THURSDAY_WINDOW 29-SEP-2022 22:00:00 ENABLED DISABLED
FRIDAY_WINDOW 30-SEP-2022 22:00:00 ENABLED DISABLED
SATURDAY_WINDOW 24-SEP-2022 06:00:00 ENABLED DISABLED
SUNDAY_WINDOW 25-SEP-2022 06:00:00 ENABLED DISABLED
GATHERSTATISTICS_MON_FRI_OP2 26-SEP-2022 04:00:00 ENABLED ENABLED
8 rows selected.To RESET all changes made in the Option 2
-- Names for the Named Schedule Object and Window Name
DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'
-- Drop the Window Name
EXEC DBMS_SCHEDULER.DROP_WINDOW(WINDOW_NAME => '&WINDOW_NAME');
-- Enable default Window Names
BEGIN
FOR W IN (SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS)
LOOP
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
CLIENT_NAME => 'auto optimizer stats collection',
OPERATION => NULL,
WINDOW_NAME => W.WINDOW_NAME);
END;
END LOOP;
END;
/
-- Confirm that all default Window Names are enabled
COL WINDOW_NAME FOR A25
COL WINDOW_NEXT_TIME for a25
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15
SELECT WINDOW_NAME,
TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
AUTOTASK_STATUS STATUS,
OPTIMIZER_STATS
FROM
DBA_AUTOTASK_WINDOW_CLIENTS;
WINDOW_NAME WINDOW_NEXT_TIME STATUS OPTIMIZER_STATS
------------------------- ------------------------- --------------- ---------------
MONDAY_WINDOW 26-SEP-2022 22:00:00 ENABLED ENABLED
TUESDAY_WINDOW 27-SEP-2022 22:00:00 ENABLED ENABLED
WEDNESDAY_WINDOW 28-SEP-2022 22:00:00 ENABLED ENABLED
THURSDAY_WINDOW 29-SEP-2022 22:00:00 ENABLED ENABLED
FRIDAY_WINDOW 30-SEP-2022 22:00:00 ENABLED ENABLED
SATURDAY_WINDOW 24-SEP-2022 06:00:00 ENABLED ENABLED
SUNDAY_WINDOW 25-SEP-2022 06:00:00 ENABLED ENABLED
7 rows selected.
To get historic details about all the Automatic Optimizer Statistics Collection Job executions
-- Specify how many rows you want the query to returns
DEFINE ROWNUM = 5
SET LINES 300
SET PAGES 999
COL WINDOW_NAME FOR A15
COL WINDOW_DURATION FOR A35
COL JOB_NAME FOR A22
COL WINDOW_START_TIME FOR A20
COL JOB_DURATION FOR A23
COL JOB_INFO FOR A2
COL JOB_STATUS FOR A10
COL JOB_START_TIME FOR A20
SELECT * FROM (
SELECT WINDOW_NAME,
TO_CHAR(WINDOW_START_TIME,'DD-MM-YYYY HH24:MI:SS') WINDOW_START_TIME,
TO_CHAR(EXTRACT(DAY FROM WINDOW_DURATION),'00') || ' Days ' ||
TO_CHAR(EXTRACT(HOUR FROM WINDOW_DURATION),'00') || ' Hours ' ||
TO_CHAR(EXTRACT(MINUTE FROM WINDOW_DURATION),'00') || ' Minutes '
WINDOW_DURATION,
JOB_NAME,
JOB_STATUS,
TO_CHAR(JOB_START_TIME,'DD-MM-YYYY HH24:MI:SS') JOB_START_TIME,
TO_CHAR(EXTRACT(DAY FROM JOB_DURATION),'90') || ' D' ||
TO_CHAR(EXTRACT(HOUR FROM JOB_DURATION),'90') || ' H' ||
TO_CHAR(EXTRACT(MINUTE FROM JOB_DURATION),'90') || ' M' ||
TO_CHAR(EXTRACT(SECOND FROM JOB_DURATION),'90') || ' S'
JOB_DURATION,
JOB_ERROR
FROM DBA_AUTOTASK_JOB_HISTORY
WHERE CLIENT_NAME='auto optimizer stats collection'
ORDER BY JOB_START_TIME DESC
) WHERE ROWNUM < '&ROWNUM';
WINDOW_NAME WINDOW_START_TIME WINDOW_DURATION JOB_NAME JOB_STATUS JOB_START_TIME JOB_DURATION JOB_ERROR
--------------- -------------------- ----------------------------------- ---------------------- ---------- -------------------- ----------------------- ----------
SATURDAY_WINDOW 24-09-2022 13:04:45 00 Days 12 Hours 46 Minutes ORA$AT_OS_OPT_SY_586 SUCCEEDED 24-09-2022 13:04:58 0 D 0 H 0 M 27 S 0
MONDAY_WINDOW 19-09-2022 22:00:00 04 Days 15 Hours 04 Minutes ORA$AT_OS_OPT_SY_566 SUCCEEDED 19-09-2022 22:00:02 0 D 0 H 0 M 52 S 0
SUNDAY_WINDOW 11-09-2022 11:47:01 07 Days 02 Hours 18 Minutes ORA$AT_OS_OPT_SY_564 SUCCEEDED 11-09-2022 15:51:21 0 D 0 H 0 M 25 S 0
SUNDAY_WINDOW 11-09-2022 11:47:01 07 Days 02 Hours 18 Minutes ORA$AT_OS_OPT_SY_561 SUCCEEDED 11-09-2022 11:47:44 0 D 0 H 0 M 31 S 0
Written At
24 SEPTEMBER 202214:30
Red Hat Release
7.x x64
Database Release
19.13.0.0.0