oracle自动收集统计信息

1.查看是否自动收集统计信息了
col REPEAT_INTERVAL for a60
col DURATION for a30
SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
FROM dba_autotask_window_clients c, dba_scheduler_windows w
WHERE c.window_name = w.window_name
AND c.optimizer_stats = ‘ENABLED’;

如图是周一到周五 22点开始 持续4小时，周末 6点开始 持续20小时

都需要开启才能使用自动收集
SELECT WINDOW_NAME,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM DBA_AUTOTASK_WINDOW_CLIENTS;

---

关（关的AUTOTASK_STATUS）
BEGIN
DBMS_AUTO_TASK_ADMIN.enable();
END;
/

开
BEGIN
DBMS_AUTO_TASK_ADMIN.enable();
END;
/

---

替换的方法

先停止（下掉整行）
BEGIN
DBMS_SCHEDULER.DISABLE(
name => ‘“SYS”.“MONDAY_WINDOW”’);
end;
/

改开始时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘“SYS”.“MONDAY_WINDOW”’,
attribute => ‘REPEAT_INTERVAL’,
value =>
‘freq=daily;byday=MON;byhour=2;byminute=0;bysecond=0’);
end;
/

改持续时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>‘“SYS”.“MONDAY_WINDOW”’,
attribute=>‘DURATION’,
value=>numtodsinterval(180, ‘minute’));
END;
/

启用
BEGIN
DBMS_SCHEDULER.ENABLE(
name => ‘“SYS”.“MONDAY_WINDOW”’);
end;
/

---

关闭某天的自动收集（关的OPTIMIZER_STATS）
BEGIN
DBMS_AUTO_TASK_ADMIN.disable (
client_name => ‘auto optimizer stats collection’,
operation => NULL,
window_name => ‘MONDAY_WINDOW’);
END;

/

开启某天的自动收集
BEGIN
DBMS_AUTO_TASK_ADMIN.enable (
client_name => ‘auto optimizer stats collection’,
operation => NULL,
window_name => ‘MONDAY_WINDOW’);
END;

/

自己定制定时统计信息job

1.1查看是否自动收集统计信息
select client_name,status from dba_autotask_client;
select client_name,status from Dba_Autotask_Client where client_name=‘auto optimizer stats collection’;

1.2开启自动收集统计信息
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => ‘auto optimizer stats collection’,operation => NULL,window_name => NULL);

1.3关闭自动收集统计信息
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => ‘auto optimizer stats collection’,operation => NULL,window_name => NULL);