从Oracle 11G开始，数据库统计信息的自动收集被整合到自动维护任务中，满足大多数情形下的运行需求。自动收集统计信息的时间是22:00--2:00 这个时段往往是业务的高峰期，但对于在线商城，交易系统而言，可能需要调整其执行时间或者自行指定收集窗口。

--0.1 获得当前自动收集统计信息的执行时间

说明：WINDOW_NAME：任务名、REPEAT_INTERVAL：任务重复间隔时间、DURATION：持续时间

col REPEAT_INTERVAL for a55;
set linesize 400;
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

--0.2 查看历史执行情况
SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history
WHERE client_name like '%stats%';

--1.1 关闭所有时间调度窗口

BEGIN
DBMS_AUTO_TASK_ADMIN.disable (
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

--1.2 关闭单个时间调度窗口
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."MONDAY_WINDOW"');
END;
/

--2.修改任务的持续时间，单位是分钟

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."MONDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(360, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."TUESDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(360, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEDNESDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(360, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."THURSDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(360, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(360, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(360, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SUNDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(360, 'minute'));
END;
/

说明：
MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW

--3.开始执行时间，BYHOUR=16,表示下午4点开始执行

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."MONDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=DAILY;BYDAY=MON;BYHOUR=16;BYMINUTE=0;BYSECOND=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."TUESDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=DAILY;BYDAY=TUE;BYHOUR=16;BYMINUTE=0;BYSECOND=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEDNESDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=DAILY;BYDAY=WED;BYHOUR=16;BYMINUTE=0;BYSECOND=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."THURSDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=DAILY;BYDAY=THU;BYHOUR=16;BYMINUTE=0;BYSECOND=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=DAILY;BYDAY=FRI;BYHOUR=16;BYMINUTE=0;BYSECOND=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=DAILY;BYDAY=SAT;BYHOUR=16;BYMINUTE=0;BYSECOND=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SUNDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=DAILY;BYDAY=SUN;BYHOUR=16;BYMINUTE=0;BYSECOND=0');
END;
/

说明：（一周）周期性统计信息收集时间对应表
freq=daily;byday=MON;
freq=daily;byday=TUE;
freq=daily;byday=WED;
freq=daily;byday=THU;
freq=daily;byday=FRI;
freq=daily;byday=SAT;
freq=daily;byday=SUN;

--4.开启任务

4.1 开启所有时间调度窗口
BEGIN
DBMS_AUTO_TASK_ADMIN.enable (
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

4.2 开启单个时间调度窗口
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;
/

--5. 获得修改完自动收集统计信息的执行时间

说明：WINDOW_NAME：任务名、REPEAT_INTERVAL：任务重复间隔时间、DURATION：持续时间

col REPEAT_INTERVAL for a55;
set linesize 400;
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

查看自动收集统计信息的任务及状态
SQL> select client_name,status from dba_autotask_client;

oracle之 统计信息时间调整

oracle之统计信息时间调整