修改统计信息后台任务（大哥整理）-摩杜云开发者社区

1、查询任务状态

SELECT client_name,status FROM dba_autotask_client;

  
CLIENT_NAME     STATUS

---------------------------------------- ------------------------

auto optimizer stats collection   ENABLED

auto space advisor    ENABLED

sql tuning advisor    ENABLED

2、关闭SQL建议和空间段管理建议

BEGIN

      dbms_auto_task_admin.disable(

        client_name => 'sql tuning advisor',

        operation   => NULL,

        window_name => NULL);

    END;

    /

    

BEGIN

      dbms_auto_task_admin.disable(

        client_name => 'auto space advisor',

        operation   => NULL,

        window_name => NULL);

    END;

    /

3、修改统计信息收集时间

exec dbms_scheduler.disable( name => 'MONDAY_WINDOW', force => TRUE);

exec dbms_scheduler.disable( name => 'TUESDAY_WINDOW', force => TRUE);

exec dbms_scheduler.disable( name => 'WEDNESDAY_WINDOW', force => TRUE);

exec dbms_scheduler.disable( name => 'THURSDAY_WINDOW', force => TRUE);

exec dbms_scheduler.disable( name => 'FRIDAY_WINDOW', force => TRUE);

exec dbms_scheduler.disable( name => 'SATURDAY_WINDOW', force => TRUE);

exec dbms_scheduler.disable( name => 'SUNDAY_WINDOW', force => TRUE);


exec dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=MON;byhour=0;byminute=0;bysecond=0');

exec dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(360,'minute'));


exec dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=TUE;byhour=0;byminute=0;bysecond=0');

exec dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(360,'minute'));


exec dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=WED;byhour=0;byminute=0;bysecond=0');

exec dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(360,'minute'));



exec dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=THU;byhour=0;byminute=0;bysecond=0');

exec dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(360,'minute'));


exec dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=FRI;byhour=0;byminute=0;bysecond=0');

exec dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(360,'minute'));


exec dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0');

exec dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(360,'minute'));



exec dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=SUN;byhour=0;byminute=0;bysecond=0');

exec dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW', attribute => 'DURATION',value => numtodsinterval(360,'minute'));



exec dbms_scheduler.enable( name => 'MONDAY_WINDOW');

exec dbms_scheduler.enable( name => 'TUESDAY_WINDOW');

exec dbms_scheduler.enable( name => 'WEDNESDAY_WINDOW');

exec dbms_scheduler.enable( name => 'THURSDAY_WINDOW');

exec dbms_scheduler.enable( name => 'FRIDAY_WINDOW');

exec dbms_scheduler.enable( name => 'SATURDAY_WINDOW');

exec dbms_scheduler.enable( name => 'SUNDAY_WINDOW');

4、查询任务状态

SELECT client_name,status FROM dba_autotask_client;

  
CLIENT_NAME     STATUS

---------------------------------------- ------------------------

auto optimizer stats collection   ENABLED

auto space advisor    DISABLED

sql tuning advisor    DISABLED



SQL> select window_name,resource_plan,repeat_interval,duration from dba_scheduler_windows;