摘要:在Oracle数据库11g中,默认自动收集统计信息的时间为晚上10点(周一到周五,4个小时),早上6点(周六,周日,20个小时),如下所示:
在Oracle数据库11g中,默认自动收集统计信息的时间为晚上10点(周一到周五,4个小时),早上6点(周六,周日,20个小时),如下所示:
[sql] view plain copy 1.select a.window_name, a.repeat_interval,a.duration 2. from dba_scheduler_windows a, dba_scheduler_wingroup_members b 3. where a.window_name = b.window_name 4. and b.window_group_name = 'MAINTENANCE_WINDOW_GROUP'; 5. 6.WINDOW_NAME REPEAT_INTERVAL DURATION 7.------------------------------ ------------------------------------------------------------ -------------------- 8.WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 9.FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 10.SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 11.THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 12.TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 13.SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 14.MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
由于很多系统晚上10点还是处于业务高峰期,因此有必要调整下时间,这个要根据各自的业务自己判断,在我们系统调为:
周一到周五,凌晨1点开始,持续5个小时; 周六、周日,凌晨1点开始,持续10个小时。
用sys用户执行如下语句即可:
[sql] view plain copy 1.begin 2. sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0'); 3. sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); 4.end; 5./ 6.begin 7. sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0'); 8. sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); 9.end; 10./ 11.begin 12. sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0'); 13. sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); 14.end; 15./ 16.begin 17. sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0'); 18. sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); 19.end; 20./ 21.begin 22. sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0'); 23. sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); 24.end; 25./ 26.begin 27. sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0'); 28. sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 10:00:00'); 29.end; 30./ 31.begin 32. sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0'); 33. sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 10:00:00'); 34.end; 35./
上面语句执行成功后的结果如下:
[sql] view plain copy 1.WINDOW_NAME REPEAT_INTERVAL DURATION 2.------------------------------ ------------------------------------------------------------ -------------------- 3.WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0 +000 05:00:00 4.FRIDAY_WINDOW freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0 +000 05:00:00 5.SATURDAY_WINDOW freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0 +000 10:00:00 6.THURSDAY_WINDOW freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0 +000 05:00:00 7.TUESDAY_WINDOW freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0 +000 05:00:00 8.SUNDAY_WINDOW freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0 +000 10:00:00 9.MONDAY_WINDOW freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0 +000 05:00:00
时间调整成功后,下一步就是开启11G的自动收集统计信息job,首先确认当前未开启:
[sql] view plain copy 1.SYS@PROD> select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection'; 2. 3.CLIENT_NAME STATUS 4.---------------------------------------------------------------- -------- 5.auto optimizer stats collection DISABLED
执行如下语句开启:
[sql] view plain copy 1.BEGIN 2. dbms_auto_task_admin.enable( 3. client_name => 'auto optimizer stats collection', 4. operation => NULL, 5. window_name => NULL); 6.END; 7./
确认已被开启:
[sql] view plain copy 1.SYS@PROD> select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection'; 2. 3.CLIENT_NAME STATUS 4.---------------------------------------------------------------- -------- 5.auto optimizer stats collection ENABLED
附:关闭这个job的语句:
[sql] view plain copy 1.BEGIN 2. dbms_auto_task_admin.disable( 3. client_name => 'auto optimizer stats collection', 4. operation => NULL, 5. window_name => NULL); 6. END; 7./
本文由职坐标整理并发布,希望对同学们学习Oracle数据库函数有所理解。
了解更多详情请关注职坐标Oracle数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号