互联网服务数据库 Oracle Oracle数据库

统计信息收集时间窗口

数据库自动的统计信息收集经常影响正常的业务批处理的执行效率，请问是否有较为妥善的解决方案

关注5

参与14

4同行回答
全部行业
全部行业 软件开发 互联网服务 银行 保险
|
按赞同排序
按时间排序

like052数据库管理员学习待业中

1、调整调度执行时间；
2、如不能全部收集，停掉系统的调度，根据需要可以收集部分表统计信息等；

收起

软件开发 · 2017-05-08

查看赞同的人

haizdl技术经理大连

【分析】对于统计信息收集来讲，每一个生产系统的高峰业务时间和负载特点都是不一样的，假设收集统计信息的触发时间正好在高峰时点，那么一定会对数据库性能造成影响，而且对于统计信息收集来讲还是相当耗费系统资源的一个任务。

【建议】建议自动优化器统计收集调整到空闲时间段执行。
【例】
exec dbms_auto_task_admin.disable(client_name => 'sql tuning advisor',operation=> NULL,window_name => NULL);
exec dbms_auto_task_admin.disable(client_name => 'auto space advisor',operation=> NULL,window_name => NULL);
EXECUTE DBMS_SCHEDULER.DISABLE(name => 'SYS.MONDAY_WINDOW', force=>TRUE);
EXECUTE DBMS_SCHEDULER.DISABLE(name => 'SYS.TUESDAY_WINDOW', force=>TRUE);
EXECUTE DBMS_SCHEDULER.DISABLE(name => 'SYS.WEDNESDAY_WINDOW', force=>TRUE);
EXECUTE DBMS_SCHEDULER.DISABLE(name => 'SYS.THURSDAY_WINDOW', force=>TRUE);
EXECUTE DBMS_SCHEDULER.DISABLE(name => 'SYS.FRIDAY_WINDOW', force=>TRUE);
EXECUTE DBMS_SCHEDULER.DISABLE(name => 'SYS.SATURDAY_WINDOW', force=>TRUE);
EXECUTE DBMS_SCHEDULER.DISABLE(name => 'SYS.SUNDAY_WINDOW', force=>TRUE);
execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'duration', '+000 04:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW', 'duration', '+000 04:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW', 'duration', '+000 04:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'duration', '+000 04:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW', 'duration', '+000 04:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'duration', '+000 04:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'duration', '+000 04:00:00');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=weekly;byday=MON;byhour=2;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=weekly;byday=TUE;byhour=2;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=weekly;byday=WED;byhour=2;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=weekly;byday=THU;byhour=2;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=weekly;byday=FRI;byhour=2;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=weekly;byday=SAT;byhour=2;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=weekly;byday=SUN;byhour=2;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.ENABLE(name => 'SYS.MONDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.ENABLE(name => 'SYS.TUESDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.ENABLE(name => 'SYS.WEDNESDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.ENABLE(name => 'SYS.THURSDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.ENABLE(name => 'SYS.FRIDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.ENABLE(name => 'SYS.SATURDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.ENABLE(name => 'SYS.SUNDAY_WINDOW');