一、需求说明

? ?Oracle数据库中存在定时JOB,自动执行收集统计信息的程序。但是对于7*24小时系统来说，Oracle配置的定时收集时间不太合理，需要人为调整。本篇博客就是基于这种需求，调整JOB采样的时间及终止时间。

? ?思路：查询现有的默认收集统计信息的策略，对它进行调整，调整后进行检查修改有效，操作完毕。

二、操作步骤

2.1 修改前，查询JOB信息

查询数据库自动收集统计信息是否被禁用

SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED

数据库默认，周一~周五(每天晚上10点开始，收集6小时截止）、周六日(每天早上6点开始，收集20小时截止)
SQL> 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‘); WINDOW_NAME REPEAT_INTERVAL DURATION -------------------- ------------------------------------------------------- ---------------------------------------------------- SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 06:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 06:00:00 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 06:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 06:00:00 MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 06:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
7 rows selected.

?

2.2 修改操作

--周六日统计信息收集最长时间调整为4小时 BEGIN dbms_scheduler.disable( name => ‘SATURDAY_WINDOW‘); dbms_scheduler.set_attribute( name => ‘SATURDAY_WINDOW‘,attribute => ‘DURATION‘,value => numtodsinterval(4,‘hour‘)); dbms_scheduler.enable( name => ‘SATURDAY_WINDOW‘); END; / BEGIN dbms_scheduler.disable( name => ‘SUNDAY_WINDOW‘); dbms_scheduler.set_attribute( name => ‘SUNDAY_WINDOW‘,‘hour‘)); dbms_scheduler.enable( name => ‘SUNDAY_WINDOW‘); END; / commit; --

--统计信息开始收集时间调整 周一-周五，调整为0点开始 1 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name=>‘"SYS"."MONDAY_WINDOW"‘,attribute=>‘REPEAT_INTERVAL‘,value=>‘FREQ=WEEKLY;BYDAY=MON;BYHOUR=0;BYMINUTE=0;BYSECOND=0‘); END; /


2 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name=>‘"SYS"."TUESDAY_WINDOW"‘,value=>‘FREQ=WEEKLY;BYDAY=TUE;BYHOUR=0;BYMINUTE=0;BYSECOND=0‘); END; /

 

3 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name=>‘"SYS"."WEDNESDAY_WINDOW"‘,value=>‘FREQ=WEEKLY;BYDAY=WED;BYHOUR=0;BYMINUTE=0;BYSECOND=0‘); END; /
4 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name=>‘"SYS"."THURSDAY_WINDOW"‘,value=>‘FREQ=WEEKLY;BYDAY=THU;BYHOUR=0;BYMINUTE=0;BYSECOND=0‘); END; /


5 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name=>‘"SYS"."FRIDAY_WINDOW"‘,value=>‘FREQ=WEEKLY;BYDAY=FRI;BYHOUR=0;BYMINUTE=0;BYSECOND=0‘); END; /
6 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name=>‘"SYS"."SATURDAY_WINDOW"‘,value=>‘FREQ=WEEKLY;BYDAY=SAT;BYHOUR=0;BYMINUTE=0;BYSECOND=0‘); END; /


7 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name=>‘"SYS"."SUNDAY_WINDOW"‘,value=>‘FREQ=WEEKLY;BYDAY=SUN;BYHOUR=0;BYMINUTE=0;BYSECOND=0‘); END; / SQL> commit;

2.3? 修改后检查

SQL> select t1.window_name,‘BSLN_MAINTAIN_STATS_SCHED‘); 2 WINDOW_NAME REPEAT_INTERVAL DURATION -------------------- ------------------------------------------------------- ---------------------------------------------- SATURDAY_WINDOW FREQ=WEEKLY;BYDAY=SAT;BYHOUR=0;BYMINUTE=0;BYSECOND=0 +000 04:00:00 FRIDAY_WINDOW FREQ=WEEKLY;BYDAY=FRI;BYHOUR=0;BYMINUTE=0;BYSECOND=0 +000 06:00:00 THURSDAY_WINDOW FREQ=WEEKLY;BYDAY=THU;BYHOUR=0;BYMINUTE=0;BYSECOND=0 +000 06:00:00 WEDNESDAY_WINDOW FREQ=WEEKLY;BYDAY=WED;BYHOUR=0;BYMINUTE=0;BYSECOND=0 +000 06:00:00 TUESDAY_WINDOW FREQ=WEEKLY;BYDAY=TUE;BYHOUR=0;BYMINUTE=0;BYSECOND=0 +000 06:00:00 MONDAY_WINDOW FREQ=WEEKLY;BYDAY=MON;BYHOUR=0;BYMINUTE=0;BYSECOND=0 +000 06:00:00 SUNDAY_WINDOW FREQ=WEEKLY;BYDAY=SUN;BYHOUR=0;BYMINUTE=0;BYSECOND=0 +000 04:00:00

7 rows selected. SQL> select WINDOW_NAME,to_char(WINDOW_START_TIME,‘yyyy-mm-dd hh24:mi:ss‘),to_char(WINDOW_END_TIME,‘yyyy-mm-dd hh24:mi:ss‘)
 from dba_autotask_window_history order by 2; WINDOW_NAME TO_CHAR(WINDOW_STAR TO_CHAR(WINDOW_END_ -------------------- ------------------- ------------------- FRIDAY_WINDOW 2019-04-19 22:00:00 2019-04-20 04:00:00 SATURDAY_WINDOW 2019-04-20 06:00:00 2019-04-21 02:00:00 SUNDAY_WINDOW 2019-04-21 06:00:00 2019-04-22 02:00:00 MONDAY_WINDOW 2019-04-22 22:00:00 2019-04-23 04:00:00 TUESDAY_WINDOW 2019-04-23 22:00:00 2019-04-24 04:00:00 WEDNESDAY_WINDOW 2019-04-24 22:00:00 2019-04-25 04:00:00 THURSDAY_WINDOW 2019-04-25 22:00:00 2019-04-26 04:00:00 FRIDAY_WINDOW 2019-04-26 22:00:00 2019-04-27 04:00:00 SATURDAY_WINDOW 2019-04-27 06:00:00 2019-04-28 02:00:00 SUNDAY_WINDOW 2019-04-28 06:00:00 2019-04-29 02:00:00 MONDAY_WINDOW 2019-04-29 22:00:00 2019-04-30 04:00:00 WINDOW_NAME TO_CHAR(WINDOW_STAR TO_CHAR(WINDOW_END_ -------------------- ------------------- ------------------- TUESDAY_WINDOW 2019-04-30 22:00:00 2019-05-01 04:00:00 WEDNESDAY_WINDOW 2019-05-01 22:00:00 2019-05-02 04:00:00 THURSDAY_WINDOW 2019-05-02 22:00:00 2019-05-03 04:00:00 FRIDAY_WINDOW 2019-05-03 22:00:00 2019-05-04 04:00:00 SATURDAY_WINDOW 2019-05-04 06:00:00 2019-05-05 02:00:00 SUNDAY_WINDOW 2019-05-05 06:00:00 2019-05-06 02:00:00 MONDAY_WINDOW 2019-05-06 22:00:00 2019-05-07 04:00:00 TUESDAY_WINDOW 2019-05-07 22:00:00 2019-05-08 04:00:00 WEDNESDAY_WINDOW 2019-05-08 22:00:00 2019-05-09 04:00:00 THURSDAY_WINDOW 2019-05-09 22:00:00 2019-05-10 04:00:00 FRIDAY_WINDOW 2019-05-10 22:00:00 2019-05-11 04:00:00 WINDOW_NAME TO_CHAR(WINDOW_STAR TO_CHAR(WINDOW_END_ -------------------- ------------------- ------------------- SATURDAY_WINDOW 2019-05-11 06:00:00 2019-05-12 02:00:00 SUNDAY_WINDOW 2019-05-12 06:00:00 2019-05-13 02:00:00 MONDAY_WINDOW 2019-05-13 22:00:00 2019-05-14 04:00:00 TUESDAY_WINDOW 2019-05-14 22:00:00 2019-05-15 04:00:00 WEDNESDAY_WINDOW 2019-05-15 22:00:00 2019-05-16 04:00:00 THURSDAY_WINDOW 2019-05-16 22:00:00 2019-05-17 04:00:00 SATURDAY_WINDOW 2019-05-18 00:00:00 2019-05-18 04:00:00 SUNDAY_WINDOW 2019-05-19 00:00:00 2019-05-19 04:00:00 MONDAY_WINDOW 2019-05-20 00:00:00 2019-05-20 06:00:00

31 rows selected.

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!