oracle 11g修改自动收集统计信息计划任务的执行时间

程序员文章站 2024-01-11 15:37:46

oracle 11g默认的自动收集统计信息的时间是22:00–2:00,但这个时段在公司大型活动期间是业务的高峰期，给本已紧张的系统带来更大的负担。所以，需要把自动执行的时间改到空闲的时段。获得当前自动收集统计信息的执行时间col WINDOW_NAME for a20col REPEAT_INTERVAL for a60col DURATION for a30set linesize 120SELECT t1.window_name, t1.repeat_interval, t1.dura....

oracle 11g默认的自动收集统计信息的时间是22:00–2:00,但这个时段在公司大型活动期间是业务的高峰期，给本已紧张的系统带来更大的负担。所以，需要把自动执行的时间改到空闲的时段。

获得当前自动收集统计信息的执行时间

col WINDOW_NAME for a20
col REPEAT_INTERVAL for a60
col DURATION for a30
set linesize 120
SELECT t1.window_name, t1.repeat_interval, t1.duration,enabled
	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                       ENABL
-------------------- ------------------------------------------------------------ ------------------------------ -----
MONDAY_WINDOW        freq=daily;byday=MON;byhour=12;byminute=0; bysecond=0        +000 04:00:00                  TRUE
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=12;byminute=0; bysecond=0        +000 04:00:00                  TRUE
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=12;byminute=0; bysecond=0        +000 04:00:00                  TRUE
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=12;byminute=0; bysecond=0        +000 04:00:00                  TRUE
FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=12;byminute=0; bysecond=0        +000 04:00:00                  TRUE
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00                  TRUE
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00                  TRUE

# WINDOW_NAME：任务名
# REPEAT_INTERVAL：任务重复间隔时间
# DURATION：持续时间

修改自动收集统计信息的执行时间

停止任务

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."MONDAY_WINDOW"',
force=>TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."TUESDAY_WINDOW"',
force=>TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."WEDNESDAY_WINDOW"',
force=>TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."THURSDAY_WINDOW"',
force=>TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."SATURDAY_WINDOW"',
force=>TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."SUNDAY_WINDOW"',
force=>TRUE);
END;
/

修改任务的持续时间，单位是分钟

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."MONDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."TUESDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEDNESDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."THURSDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SUNDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
/

开始执行时间，byhour=1,表示1点开始执行

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."MONDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."TUESDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."WEDNESDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."THURSDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SATURDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0');
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."SUNDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0');
END;
/

开启任务

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."MONDAY_WINDOW"');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."TUESDAY_WINDOW"');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."WEDNESDAY_WINDOW"');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."THURSDAY_WINDOW"');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."SATURDAY_WINDOW"');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."SUNDAY_WINDOW"');
END;
/

查看修改后自动收集统计信息的执行时间

col WINDOW_NAME for a20
col REPEAT_INTERVAL for a60
col DURATION for a30
set linesize 120
SELECT t1.window_name, t1.repeat_interval, t1.duration,enabled
	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                       ENABL
-------------------- ------------------------------------------------------------ ------------------------------ -----
MONDAY_WINDOW        freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0         +000 03:00:00                  TRUE
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0         +000 03:00:00                  TRUE
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0         +000 03:00:00                  TRUE
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0         +000 03:00:00                  TRUE
FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0         +000 03:00:00                  TRUE
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0         +000 03:00:00                  TRUE
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0         +000 03:00:00                  TRUE

本文地址：https://blog.csdn.net/qq_41944882/article/details/111037912

相关标签： Oracle11g相关知识 oracle

上一篇：浅析51个PHP处理字符串的函数_PHP教程

下一篇：在php中怎么利用js把参数传递给弹窗

oracle 11g修改自动收集统计信息计划任务的执行时间

获得当前自动收集统计信息的执行时间

修改自动收集统计信息的执行时间

停止任务

修改任务的持续时间，单位是分钟

开始执行时间，byhour=1,表示1点开始执行

开启任务

查看修改后自动收集统计信息的执行时间

oracle 11g修改自动收集统计信息计划任务的执行时间

Oracle 11g系统自动收集统计信息的一些知识

oracle 11g 收集统计信息的新特点介绍

Oracle 收集统计信息在11g和12C下的区别

Oracle 收集统计信息在11g和12C下的区别

Oracle 11g系统自动收集统计信息的一些知识

oracle自动统计信息时间的修改过程记录

Oracle 11g统计信息收集--多列统计信息的收集

禁用Oracle 11g的统计数据自动收集功能

oracle 11g修改自动收集统计信息计划任务的执行时间