Disclaimer

Wednesday, 26 February 2025

HOW TO RESCHEDULE THE AUTOMATIC OPTIMIZER STATISTICS COLLECTION JOB

 

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_SA


Here 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 schedules which called as WINDOW NAME.


SET LINES 300 
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  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 it can be seen from the above output the Monday-To-Friday WINDOW NAME start on Weekdays at 10 PM / 22  and last for 4 hours, 
and the Saturday-To-Sunday WINDOW NAME start on Weekends at 6 AM and last for 20 hours.







set lines 200
col WINDOW_NAME for a24
col WINDOW_NEXT_TIME for a45
select WINDOW_NAME,WINDOW_NEXT_TIME,OPTIMIZER_STATS,WINDOW_ACTIVE,AUTOTASK_STATUS from DBA_AUTOTASK_WINDOW_CLIENTS;
 
WINDOW_NAME               WINDOW_NEXT_TIME                              OPTIMIZE WINDO AUTOTASK
------------------------- --------------------------------------------- -------- ----- --------
MONDAY_WINDOW             03-MAR-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
TUESDAY_WINDOW            04-MAR-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
WEDNESDAY_WINDOW          26-FEB-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
THURSDAY_WINDOW           27-FEB-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
FRIDAY_WINDOW             28-FEB-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
SATURDAY_WINDOW           01-MAR-24 06.00.00.000000 AM EUROPE/VIENNA    ENABLED  FALSE ENABLED
SUNDAY_WINDOW             02-MAR-24 06.00.00.000000 AM EUROPE/VIENNA    ENABLED  FALSE ENABLED




 
There are  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  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  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  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
-------------------- -------------------- ----------------------------------------
24-SEP-2024 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  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 a24
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 TRUE





d. 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 A24
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 A24
COL WINDOW_NEXT_TIME for a24
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-2024 22:00:00      ENABLED         ENABLED
TUESDAY_WINDOW            27-SEP-2024 22:00:00      ENABLED         ENABLED
WEDNESDAY_WINDOW          28-SEP-2024 22:00:00      ENABLED         ENABLED
THURSDAY_WINDOW           29-SEP-2024 22:00:00      ENABLED         ENABLED
FRIDAY_WINDOW             30-SEP-2024 22:00:00      ENABLED         ENABLED
SATURDAY_WINDOW           24-SEP-2024 06:00:00      ENABLED         ENABLED
SUNDAY_WINDOW             24-SEP-2024 06:00:00      ENABLED         ENABLED
GATHERSTATISTICS_MON_FRI  26-SEP-2024 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 A24
COL WINDOW_NEXT_TIME for a24
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-2024 22:00:00      ENABLED         DISABLED
TUESDAY_WINDOW            27-SEP-2024 22:00:00      ENABLED         DISABLED
WEDNESDAY_WINDOW          28-SEP-2024 22:00:00      ENABLED         DISABLED
THURSDAY_WINDOW           29-SEP-2024 22:00:00      ENABLED         DISABLED
FRIDAY_WINDOW             30-SEP-2024 22:00:00      ENABLED         DISABLED
SATURDAY_WINDOW           24-SEP-2024 06:00:00      ENABLED         DISABLED
SUNDAY_WINDOW             24-SEP-2024 06:00:00      ENABLED         DISABLED
GATHERSTATISTICS_MON_FRI  26-SEP-2024 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 A24
COL WINDOW_NEXT_TIME for a24
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-2024 22:00:00      ENABLED         ENABLED
TUESDAY_WINDOW            27-SEP-2024 22:00:00      ENABLED         ENABLED
WEDNESDAY_WINDOW          28-SEP-2024 22:00:00      ENABLED         ENABLED
THURSDAY_WINDOW           29-SEP-2024 22:00:00      ENABLED         ENABLED
FRIDAY_WINDOW             30-SEP-2024 22:00:00      ENABLED         ENABLED
SATURDAY_WINDOW           24-SEP-2024 06:00:00      ENABLED         ENABLED
SUNDAY_WINDOW             24-SEP-2024 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  and last for  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   TRUE








C. 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 A24
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 a24
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-2024 22:00:00      ENABLED         ENABLED
TUESDAY_WINDOW                 27-SEP-2024 22:00:00      ENABLED         ENABLED
WEDNESDAY_WINDOW               28-SEP-2024 22:00:00      ENABLED         ENABLED
THURSDAY_WINDOW                29-SEP-2024 22:00:00      ENABLED         ENABLED
FRIDAY_WINDOW                  30-SEP-2024 22:00:00      ENABLED         ENABLED
SATURDAY_WINDOW                24-SEP-2024 06:00:00      ENABLED         ENABLED
SUNDAY_WINDOW                  24-SEP-2024 06:00:00      ENABLED         ENABLED
GATHERSTATISTICS_MON_FRI_OP2   26-SEP-2024 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 a24
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-2024 22:00:00      ENABLED         DISABLED
TUESDAY_WINDOW                 27-SEP-2024 22:00:00      ENABLED         DISABLED
WEDNESDAY_WINDOW               28-SEP-2024 22:00:00      ENABLED         DISABLED
THURSDAY_WINDOW                29-SEP-2024 22:00:00      ENABLED         DISABLED
FRIDAY_WINDOW                  30-SEP-2024 22:00:00      ENABLED         DISABLED
SATURDAY_WINDOW                24-SEP-2024 06:00:00      ENABLED         DISABLED
SUNDAY_WINDOW                  24-SEP-2024 06:00:00      ENABLED         DISABLED
GATHERSTATISTICS_MON_FRI_OP2   26-SEP-2024 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 A24
COL WINDOW_NEXT_TIME for a24
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-2024 22:00:00      ENABLED         ENABLED
TUESDAY_WINDOW            27-SEP-2024 22:00:00      ENABLED         ENABLED
WEDNESDAY_WINDOW          28-SEP-2024 22:00:00      ENABLED         ENABLED
THURSDAY_WINDOW           29-SEP-2024 22:00:00      ENABLED         ENABLED
FRIDAY_WINDOW             30-SEP-2024 22:00:00      ENABLED         ENABLED
SATURDAY_WINDOW           24-SEP-2024 06:00:00      ENABLED         ENABLED
SUNDAY_WINDOW             24-SEP-2024 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-2024 13:04:45   00 Days  12 Hours  46 Minutes      ORA$AT_OS_OPT_SY_586   SUCCEEDED  24-09-2024 13:04:58    0 D  0 H  0 M 27 S             0
MONDAY_WINDOW   19-09-2024 22:00:00   04 Days  15 Hours  04 Minutes      ORA$AT_OS_OPT_SY_566   SUCCEEDED  19-09-2024 22:00:02    0 D  0 H  0 M 52 S             0
SUNDAY_WINDOW   11-09-2024 11:47:01   07 Days  02 Hours  18 Minutes      ORA$AT_OS_OPT_SY_564   SUCCEEDED  11-09-2024 15:51:21    0 D  0 H  0 M 24 S             0
SUNDAY_WINDOW   11-09-2024 11:47:01   07 Days  02 Hours  18 Minutes      ORA$AT_OS_OPT_SY_561   SUCCEEDED  11-09-2024 11:47:44    0 D  0 H  0 M 31 S             0











at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

ORA-07445: exception encountered: core dump [qcpibfa()+235] [SIGSEGV] [ADDR:0x7FFF6A9C7C88] [PC:0x4BF0E4B] [Address not mapped to object

  After patching 19.26 , we received complain from Application team that they were facing below error while executing one particular SQL_ID ...