大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
这里演示每天凌晨1点,持续5个小时收集统计信息的方法。
创新互联建站网站建设公司,提供成都网站设计、成都网站建设,网页设计,建网站,PHP网站建设等专业做网站服务;可快速的进行网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,是专业的做网站团队,希望更多企业前来合作!
1.查看版本
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL>
--默认值如下
set lines 200
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a60
col DURATION for a20
SELECT w.window_name,
w.repeat_interval,
w.duration,
w.enabled
FROM dba_autotask_window_clients c, dba_scheduler_windows w
WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';
SQL> set lines 200
SQL> col WINDOW_NAME for a20
SQL> col REPEAT_INTERVAL for a60
SQL> col DURATION for a20
SQL> SELECT w.window_name,
2 w.repeat_interval,
3 w.duration,
4 w.enabled
5 FROM dba_autotask_window_clients c, dba_scheduler_windows w
6 WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';
WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
-------------------- ------------------------------------------------------------ -------------------- -----
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
FRIDAY_WINDOW FREQ=daily;BYDAY=FRI;BYHOUR=22;BYMINUTE=30;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
6 rows selected.
--修改自动收集统计信息每天凌晨一点,持续5个小时。
--MONDAY
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"."MONDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(300,'minute'));
END;
/
--TUESDAY
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"."TUESDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(300,'minute'));
END;
/
--WEDNESDAY
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"."WEDNESDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(300,'minute'));
END;
/
--THURSDAY
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"."THURSDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(300,'minute'));
END;
/
--FRIDAY
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"."FRIDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(300,'minute'));
END;
/
--SATURDAY
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"."SATURDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(300,'minute'));
END;
/
--SUNDAY
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.SET_ATTRIBUTE(
name => '"SYS"."SUNDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(300,'minute'));
END;
/
SQL> --验证是否变更
SQL> set lines 200
SQL> col WINDOW_NAME for a20
SQL> col REPEAT_INTERVAL for a60
SQL> col DURATION for a20
SQL> SELECT w.window_name,
2 w.repeat_interval,
3 w.duration,
4 w.enabled
5 FROM dba_autotask_window_clients c, dba_scheduler_windows w
6 WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';
WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
-------------------- ------------------------------------------------------------ -------------------- -----
MONDAY_WINDOW FREQ=daily;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0 +000 05:00:00 TRUE
TUESDAY_WINDOW FREQ=daily;BYDAY=TUE;BYHOUR=1;BYMINUTE=0;BYSECOND=0 +000 05:00:00 TRUE
WEDNESDAY_WINDOW FREQ=daily;BYDAY=WED;BYHOUR=1;BYMINUTE=0;BYSECOND=0 +000 05:00:00 TRUE
THURSDAY_WINDOW FREQ=daily;BYDAY=THU;BYHOUR=1;BYMINUTE=0;BYSECOND=0 +000 05:00:00 TRUE
FRIDAY_WINDOW FREQ=daily;BYDAY=FRI;BYHOUR=1;BYMINUTE=0;BYSECOND=0 +000 05:00:00 TRUE
SATURDAY_WINDOW FREQ=daily;BYDAY=SAT;BYHOUR=1;BYMINUTE=0;BYSECOND=0 +000 05:00:00 TRUE
SUNDAY_WINDOW FREQ=daily;BYDAY=SUN;BYHOUR=1;BYMINUTE=0;BYSECOND=0 +000 05:00:00 TRUE
7 rows selected.
SQL>