修改统计信息后台任务（大哥整理）

原创

楠蛮入侵 2023-10-10 08:46:25 博主文章分类：oracle ©著作权

文章标签 sql SQL 文章分类 Oracle 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者楠蛮入侵的原创作品，请联系作者获取转载授权，否则将追究法律责任

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;