Objective: Move auto task for stats to a custom schedule for working days. Ensure the other auto tasks arent impacted
Reference: Oracle note 1300313.1
Verify existing setup:
set lines 1200 pages 20000 colsep , time on timing on trim on trims on
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
col window_name for a30
col window_next_time for a45
col window_active for a5
col optimizer_stats for a10
select window_name, window_next_time, window_active, optimizer_Stats from dba_autotask_window_clients order by window_next_time;
Disable stats on all working days:
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;
/
Setup a new schedule:
BEGIN
dbms_scheduler.create_window(
window_name=>'STATS_WORKDAY_WINDOW',
resource_plan=>'DEFAULT_MAINTENANCE_PLAN',
repeat_interval=>'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=2;byminute=0;bysecond=0',
duration=>interval '2' hour,
comments=>'TEST MAINTENANCE FOR STATS TASK');
exception
when others then
if sqlcode=-27477 then NULL;
else raise;
end if;
end;
/
begin
dbms_scheduler.set_attribute('STATS_WORKDAY_WINDOW','SYSTEM',TRUE);
dbms_scheduler.set_attribute('STATS_WORKDAY_WINDOW','FOLLOW_DEFAULT_TIMEZONE',TRUE);
exception
when others then
if sqlcode=-27477 then NULL;
else raise;
end if;
end;
/
-- add stat_workday_window to the maintenance window group
begin
dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP','STATS_WORKDAY_WINDOW');
end;
/
Verify new schedule:
set lines 1200 pages 20000 colsep , time on timing on trim on trims on
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
col window_name for a16
col window_next_time for a45
col window_active for a5
col optimizer_stats for a10
select window_name, window_next_time, window_active, optimizer_Stats from dba_autotask_window_clients order by window_next_time;
Output:
14:36:08 SQL> select * from dba_autotask_window_clients;
WINDOW_NAME ,WINDOW_NEXT_TIME ,WINDO,AUTOTASK,OPTIMIZER_,SEGMENT_,SQL_TUNE
----------------,---------------------------------------------,-----,--------,----------,--------,--------
MONDAY_WINDOW ,01-JUL-24 10.00.00.000000 PM EUROPE/LONDON ,FALSE,ENABLED ,DISABLED ,ENABLED ,ENABLED
TUESDAY_WINDOW ,02-JUL-24 10.00.00.000000 PM EUROPE/LONDON ,FALSE,ENABLED ,DISABLED ,ENABLED ,ENABLED
WEDNESDAY_WINDOW,03-JUL-24 10.00.00.000000 PM EUROPE/LONDON ,FALSE,ENABLED ,DISABLED ,ENABLED ,ENABLED
THURSDAY_WINDOW ,04-JUL-24 10.00.00.000000 PM EUROPE/LONDON ,FALSE,ENABLED ,DISABLED ,ENABLED ,ENABLED
FRIDAY_WINDOW ,28-JUN-24 10.00.00.000000 PM EUROPE/LONDON ,FALSE,ENABLED ,DISABLED ,ENABLED ,ENABLED
SATURDAY_WINDOW ,29-JUN-24 06.00.00.000000 AM EUROPE/LONDON ,FALSE,ENABLED ,ENABLED ,ENABLED ,ENABLED
SUNDAY_WINDOW ,30-JUN-24 06.00.00.000000 AM EUROPE/LONDON ,FALSE,ENABLED ,ENABLED ,ENABLED ,ENABLED
STATS_WORKDAY_WI,01-JUL-24 02.00.00.000000 AM EUROPE/LONDON ,FALSE,ENABLED ,ENABLED ,ENABLED ,ENABLED
NDOW , , , , , ,
8 rows selected.
Elapsed: 00:00:00.06
14:36:17 SQL>
Disable segment advisor and sql tuning advisor from the new schedule:
>> ideally this will activate ther other 2 auto task clients as well segment advisor, sql tuning advisor as well.
We wanted only stats to run in this window, so we disable the other 2 in new window.
begin
dbms_auto_task_admin.disable(
client_name=>'auto space advisor',
operation=>NULL,
window_name=>'STATS_WORKDAY_WINDOW');
end;
/
begin
dbms_auto_task_admin.disable(
client_name=>'sql tuning advisor',
operation=>NULL,
window_name=>'STATS_WORKDAY_WINDOW');
end;
/
A test routine:
since it is scheduled to start early in the morning, let us reschedule it in such a way that we can validate it works fine.
create a new schedule and associate:
BEGIN
dbms_scheduler.create_window(
window_name=>'STATS_FRIDAY_WINDOW',
resource_plan=>'DEFAULT_MAINTENANCE_PLAN',
repeat_interval=>'freq=daily;byday=FRI;byhour=15;byminute=0;bysecond=0',
duration=>interval '2' hour,
comments=>'TEST MAINTENANCE FOR STATS TASK');
exception
when others then
if sqlcode=-27477 then NULL;
else raise;
end if;
end;
/
begin
dbms_scheduler.set_attribute('STATS_FRIDAY_WINDOW','SYSTEM',TRUE);
dbms_scheduler.set_attribute('STATS_FRIDAY_WINDOW','FOLLOW_DEFAULT_TIMEZONE',TRUE);
exception
when others then
if sqlcode=-27477 then NULL;
else raise;
end if;
end;
/
begin
dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP','STATS_FRIDAY_WINDOW');
end;
/
begin
dbms_auto_task_admin.disable(
client_name=>'auto space advisor',
operation=>NULL,
window_name=>'STATS_FRIDAY_WINDOW');
end;
/
begin
dbms_auto_task_admin.disable(
client_name=>'sql tuning advisor',
operation=>NULL,
window_name=>'STATS_FRIDAY_WINDOW');
end;
/
As we see above the auto stats job ran for < 1 min and completed as well. So the schedule works fine. You also dont see any other auto task launched.
Thanks
