关于oracle统计信息收集

最新推荐文章于 2024-08-01 23:39:22 发布

好记忆不如烂笔头abc

最新推荐文章于 2024-08-01 23:39:22 发布

阅读量799

点赞数

分类专栏： oracle 文章标签：统计信息

本文链接：https://blog.csdn.net/jycjyc/article/details/89501926

版权

oracle 专栏收录该内容

433 篇文章 7 订阅

订阅专栏

检查是否启用收集任务:

select client_name,status from dba_autotask_client;

禁用收集：

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

启用收集：

exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

检查任务收集是否成功：

select * from (select job_name,status,instance_id,run_duration,ACTUAL_START_DATE from dba_scheduler_job_run_details

where job_name like 'ORA$AT_%' order by ACTUAL_START_DATE desc) where rownum<30;

检查任务窗口：

select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

调整任务执行窗口时间:

--周一:9点执行，运行窗口4小时

begin

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

sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

--周二:9点执行，运行窗口4小时

begin

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

sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

--周三:9点执行，运行窗口4小时

begin

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

sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

--周四:9点执行，运行窗口4小时

begin

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

sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

--周五:9点执行，运行窗口4小时

begin

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

sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

--周六:6点执行，运行窗口22小时

begin

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

sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 22:00:00');

end;

--周日:6点执行，运行窗口22小时

begin

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

sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 22:00:00');

end;

其他参考：http://blog.sina.com.cn/s/blog_14d5a51a90102yyif.html