5.1 Customizing Default Statistics Collection Schedule
- 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