Customizing Default Statistics Collection Schedule

The Default Scheduler is to be customized for the following:

  • Ensure that the default statistics gathering program is configured and Running.
    SELECT STATUS 
    FROM DBA_AUTOTASK_CLIENT 
    WHERE CLIENT_NAME='auto optimizer stats collection';
    
Should return - ENABLED
  • Ensure that the default statistics gathering program is configured to run only on weekends.
/* Start of Script – Script to be executed as SYS*/
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
 CLIENT_NAME => 'auto optimizer stats collection', 
 OPERATION => NULL, 
 WINDOW_NAME => 'SATURDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.ENABLE(
 CLIENT_NAME => 'auto optimizer stats collection', 
 OPERATION => NULL, 
 WINDOW_NAME => 'SUNDAY_WINDOW');
END;
/

/* End of Script */

  • Default schedule is daily. So disable the daily schedules for optimizer statistics.

    /* Start of Script – Script to be executed as SYS*/

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
 CLIENT_NAME => 'auto optimizer stats collection', 
 OPERATION => NULL, 
 WINDOW_NAME => 'MONDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(
 CLIENT_NAME =>'auto optimizer stats collection', 
 OPERATION => NULL, 
 WINDOW_NAME => 'TUESDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(
 CLIENT_NAME =>'auto optimizer stats collection', 
 OPERATION => NULL, 
 WINDOW_NAME => 'WEDNESDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(
 CLIENT_NAME => 'auto optimizer stats collection', 
 OPERATION => NULL, 
 WINDOW_NAME => 'THURSDAY_WINDOW');
DBMS_AUTO_TASK_ADMIN.DISABLE(
 CLIENT_NAME => 'auto optimizer stats collection', 
 OPERATION => NULL, 
 WINDOW_NAME => 'FRIDAY_WINDOW');
END;
/
/* End of Script */
Verify the setup using the following SQL
SELECT WINDOW_NAME,OPTIMIZER_STATS
FROM DBA_AUTOTASK_WINDOW_CLIENTS;

Should return
MONDAY_WINDOW                   DISABLED
TUESDAY_WINDOW                   DISABLED
WEDNESDAY_WINDOW            DISABLED
THURSDAY_WINDOW                DISABLED
FRIDAY_WINDOW                      DISABLED
SATURDAY_WINDOW               ENABLED
SUNDAY_WINDOW                    ENABLED