如何开启Oracle自动统计信息收集[转]

检查标准：

如下语句可以查询自动统计信息是否打开，需要这三个语句查出来的所有状态都是enable。

select window_name,autotask_status,optimizer_stats from dba_autotask_window_clients;
select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';
select window_name,repeat_interval,duration,enabled from dba_scheduler_windows whereENABLED='TRUE' AND window_name not like 'WEEK%';  

后续通过如下语句可以查询任务执行历史信息：

select * from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS%' order by log_date desc;

 

如果设置不正确，请按下列步骤重新设置

1.　重启所有自动维护任务

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable();
  DBMS_AUTO_TASK_ADMIN.enable();
END;
/

2.  单独打开auto optimizer stats collection ,关闭其他两个任务

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

如下为调整时间窗策略，实际执行时间，根据现网业务状况决定。

3.  调整自动数据库维护任务执行时间策略

（调整自动维护任务的运行时间，避开业务高峰期）

#修改周一到周五的启动时间,修改启动时间到晚上3点（直接修改byhour=3即可），

begin
dbms_scheduler.set_attribute(name=>'MONDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=MON;byhour=3;byminute=0; bysecond=0');
dbms_scheduler.set_attribute(name=>'TUESDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=TUE;byhour=3;byminute=0; bysecond=0');
dbms_scheduler.set_attribute(name=>'WEDNESDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=WED;byhour=3;byminute=0; bysecond=0');
dbms_scheduler.set_attribute(name=>'THURSDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=THU;byhour=3;byminute=0; bysecond=0');
dbms_scheduler.set_attribute(name=>'FRIDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=FRI;byhour=3;byminute=0; bysecond=0');
end;
/

 

#设置周六、周日每天3点执行定时任务，每次执行四小时：

begin
dbms_scheduler.set_attribute(name=>'SATURDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
dbms_scheduler.set_attribute(name=>'SATURDAY_WINDOW',attribute=>'DURATION',value=>'+000 04:00:00');
dbms_scheduler.set_attribute(name=>'SUNDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=SUN;byhour=3;byminute=0; bysecond=0');
dbms_scheduler.set_attribute(name=>'SUNDAY_WINDOW',attribute=>'DURATION',value=>'+000 04:00:00');
end;
/

 

4.  查看此时是否按要求开启

select window_name,autotask_status,optimizer_stats from dba_autotask_window_clients;

select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';

--通过下列语句查看时间窗是否生效：（注意：WEEKNIGHT_WINDOW和WEEKEND_WINDOW时间窗主要用于兼容之前的oracle版本，可忽略）

select window_name,repeat_interval,duration,next_start_date,enabled from dba_scheduler_windows;

select client_name,status from dba_autotask_client where client_name='auto optimizer stats collection';

 

后续通过如下语句可以查询任务执行历史信息：

select * from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS%' order by log_date desc;

 

其他：

如果仅仅时间窗没有打开（执行select window_name,repeat_interval,duration,enabled from dba_scheduler_windows; ENABLED字段全为FALSE），可单独执行下列语句打开时间窗：

BEGIN
  dbms_scheduler.enable(name => 'MONDAY_WINDOW');
  dbms_scheduler.enable(name => 'TUESDAY_WINDOW');
  dbms_scheduler.enable(name => 'WEDNESDAY_WINDOW');
  dbms_scheduler.enable(name => 'THURSDAY_WINDOW');
  dbms_scheduler.enable(name => 'FRIDAY_WINDOW');
  dbms_scheduler.enable(name => 'SATURDAY_WINDOW');
  dbms_scheduler.enable(name => 'SUNDAY_WINDOW'); 
END;
/

 

相关表：

select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_JOB_HISTORY;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_SCHEDULE;
select * from DBA_AUTOTASK_TASK;
select * from DBA_SCHEDULER_WINDOWS;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_WINDOW_HISTORY;
select * from DBA_RSRC_PLANS;
select * from DBA_RSRC_PLAN_DIRECTIVES;
select *  from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name like 'ORA$AT_SQ_SQL_SW_%';