ITPub博客

首页 > 数据库 > Oracle > ORACLE 11G 统计信息自动收集job

ORACLE 11G 统计信息自动收集job

Oracle 作者:royevictory 时间:2016-01-22 15:23:50 0 删除 编辑
  1. ORACLE 11G的自动收集统计信息介绍

在11g中,默认自动收集统计信息的时间为晚上10点(周一到周五,4个小时),早上6点(周六,周日,20个小时),如下所示:

select window_name,duration,next_start_date from dba_scheduler_windows;

  1. 修改统计信息的收集时间

每个公司的业务情况都不一样,有的公司在晚上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 window_name,duration,next_start_date from dba_scheduler_windows;

 

  1. 检查统计信息的收集任务的开启和关闭
  • 检查统计信息任务的状态

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管理 )  

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29302187/viewspace-1980799/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle创建job
请登录后发表评论 登录
全部评论

注册时间:2014-08-06

  • 博文量
    195
  • 访问量
    527755