- ORACLE 11G的自动收集统计信息介绍
在11g中，默认自动收集统计信息的时间为晚上10点（周一到周五，4个小时），早上6点（周六，周日，20个小时），如下所示：
| select window_name,duration,next_start_date from dba_scheduler_windows;
|
- 修改统计信息的收集时间
每个公司的业务情况都不一样，有的公司在晚上10点的时候，还属于业务的高峰期，那么默认的统计信息的收集就不符合业务需求了，可以根据业务需求进行相应的修改。
例：周一到周五，凌晨2点开始，持续4个小时； 周六、周日，凌晨2点开始，持续8个小时；
| begin sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 04:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=2;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=2;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=2;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 04:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=2;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 04:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=2;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 08:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=2;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 08:00:00'); end; / |
查询修改后的结果：
- 检查统计信息的收集任务的开启和关闭
- 检查统计信息任务的状态
| select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection';
|
- 关闭统计信息收集任务
| BEGIN dbms_auto_task_admin.disable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / |
- 打开统计信息收集任务
| BEGIN dbms_auto_task_admin.enable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
本文作者：JOHN
ORACLE技术博客：ORACLE 猎人笔记 数据库技术群：367875324 （请备注ORACLE管理 ）
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
