1、开启自动收集任务
1.1 查看统计信息自动收集状态disable
SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients order by window_next_time; WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE ------------------------------ --------------------------------------------------------------------------- ----- -------- WEDNESDAY_WINDOW 28-NOV-18 10.00.00.000000 PM PRC FALSE DISABLED THURSDAY_WINDOW 29-NOV-18 10.00.00.000000 PM PRC FALSE DISABLED FRIDAY_WINDOW 30-NOV-18 10.00.00.000000 PM PRC FALSE DISABLED SATURDAY_WINDOW 01-DEC-18 06.00.00.000000 AM PRC FALSE DISABLED SUNDAY_WINDOW 02-DEC-18 06.00.00.000000 AM PRC FALSE DISABLED MONDAY_WINDOW 03-DEC-18 10.00.00.000000 PM PRC FALSE DISABLED TUESDAY_WINDOW 04-DEC-18 10.00.00.000000 PM PRC FALSE DISABLED 7 rows selected. SQL> |
1.2 查看定时任务的开始时间和持续时间
SQL> col REPEAT_INTERVAL format a80 SQL> col DURATION format a40 SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2 2 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 ------------------------------ -------------------------------------------------------------------------------- ---------------------------------------- MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 7 rows selected. SQL> |
1.3 查看自动收集详细
SQL> select window_name, 2 to_char(cast(window_next_time as date),'DD/MM/YYYY HH24:MI:SS') window_next_time, 3 window_active, 4 autotask_status, 5 optimizer_stats, 6 segment_advisor, 7 sql_tune_advisor, 8 health_monitor 9 from DBA_AUTOTASK_WINDOW_CLIENTS 10 ; WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M ------------------------------ ------------------- ----- -------- -------- -------- -------- -------- MONDAY_WINDOW 03/12/2018 22:00:00 FALSE ENABLED DISABLED DISABLED DISABLED DISABLED TUESDAY_WINDOW 04/12/2018 22:00:00 FALSE ENABLED DISABLED DISABLED DISABLED DISABLED WEDNESDAY_WINDOW 28/11/2018 22:00:00 FALSE ENABLED DISABLED DISABLED DISABLED DISABLED THURSDAY_WINDOW 29/11/2018 22:00:00 FALSE ENABLED DISABLED DISABLED DISABLED DISABLED FRIDAY_WINDOW 30/11/2018 22:00:00 FALSE ENABLED DISABLED DISABLED DISABLED DISABLED SATURDAY_WINDOW 01/12/2018 06:00:00 FALSE ENABLED DISABLED DISABLED DISABLED DISABLED SUNDAY_WINDOW 02/12/2018 06:00:00 FALSE ENABLED DISABLED DISABLED DISABLED DISABLED 7 rows selected. SQL> |
1.4 查看自动收集统计信息的任务及状态:
SQL> select CLIENT_NAME,STATUS from DBA_AUTOTASK_CLIENT; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection DISABLED auto space advisor DISABLED sql tuning advisor DISABLED SQL> |
1.5 启用自动收集统计信息
SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL); PL/SQL procedure successfully completed. SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL); PL/SQL procedure successfully completed. SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL); PL/SQL procedure successfully completed. SQL> |
如果是想禁用自动收集统计信息
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL); exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL); exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL); |
1.6 再次查看状态
SQL> select CLIENT_NAME,STATUS from DBA_AUTOTASK_CLIENT; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED SQL> SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients order by window_next_time; WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE ------------------------------ --------------------------------------------------------------------------- ----- -------- WEDNESDAY_WINDOW 28-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED THURSDAY_WINDOW 29-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED FRIDAY_WINDOW 30-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED SATURDAY_WINDOW 01-DEC-18 10.00.00.000000 PM PRC FALSE ENABLED SUNDAY_WINDOW 02-DEC-18 10.00.00.000000 PM PRC FALSE ENABLED MONDAY_WINDOW 03-DEC-18 10.00.00.000000 PM PRC FALSE ENABLED TUESDAY_WINDOW 04-DEC-18 10.00.00.000000 PM PRC FALSE ENABLED 7 rows selected. SQL> SQL> select window_name, 2 to_char(cast(window_next_time as date),'DD/MM/YYYY HH24:MI:SS') window_next_time, 3 window_active, 4 autotask_status, 5 optimizer_stats, 6 segment_advisor, 7 sql_tune_advisor, 8 health_monitor 9 from DBA_AUTOTASK_WINDOW_CLIENTS 10 ; WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M ------------------------------ ------------------- ----- -------- -------- -------- -------- -------- MONDAY_WINDOW 03/12/2018 22:00:00 FALSE ENABLED ENABLED ENABLED ENABLED DISABLED TUESDAY_WINDOW 04/12/2018 22:00:00 FALSE ENABLED ENABLED ENABLED ENABLED DISABLED WEDNESDAY_WINDOW 28/11/2018 22:00:00 FALSE ENABLED ENABLED ENABLED ENABLED DISABLED THURSDAY_WINDOW 29/11/2018 22:00:00 FALSE ENABLED ENABLED ENABLED ENABLED DISABLED FRIDAY_WINDOW 30/11/2018 22:00:00 FALSE ENABLED ENABLED ENABLED ENABLED DISABLED SATURDAY_WINDOW 01/12/2018 22:00:00 FALSE ENABLED ENABLED ENABLED ENABLED DISABLED SUNDAY_WINDOW 02/12/2018 22:00:00 FALSE ENABLED ENABLED ENABLED ENABLED DISABLED 7 rows selected. SQL> SQL> col REPEAT_INTERVAL format a80 SQL> col DURATION format a40 SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2 2 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 ------------------------------ -------------------------------------------------------------------------------- ---------------------------------------- MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 7 rows selected. SQL> |
2、修改收集时间以及持续时间
2.1 停止任务
BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."MONDAY_WINDOW"', --停止周一的任务
force => TRUE); --force选择TRUE
END;
/
SQL> BEGIN 2 DBMS_SCHEDULER.DISABLE( 3 name => '"SYS"."MONDAY_WINDOW"', 4 force => TRUE); 5 END; 6 / PL/SQL procedure successfully completed. SQL> |
select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients order by window_next_time;
SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients order by window_next_time; WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE ------------------------------ --------------------------------------------------------------------------- ----- -------- WEDNESDAY_WINDOW 28-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED THURSDAY_WINDOW 29-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED FRIDAY_WINDOW 30-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED SATURDAY_WINDOW 01-DEC-18 06.00.00.000000 AM PRC FALSE ENABLED SUNDAY_WINDOW 02-DEC-18 06.00.00.000000 AM PRC FALSE ENABLED TUESDAY_WINDOW 04-DEC-18 10.00.00.000000 PM PRC FALSE ENABLED 6 rows selected. SQL> |
2.2 修改任务的持续时间,单位是分钟
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."MONDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(180,'minute')); --持续收集180分钟
END;
/
SQL> BEGIN 2 DBMS_SCHEDULER.SET_ATTRIBUTE( 3 name => '"SYS"."MONDAY_WINDOW"', 4 attribute => 'DURATION', 5 value => numtodsinterval(180,'minute')); 6 END; 7 / PL/SQL procedure successfully completed. 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');
SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2 2 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 ------------------------------ -------------------------------------------------------------------------------- ---------------------------------------- MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 03:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 7 rows selected. SQL> |
2.3 开始执行时间,BYHOUR=2,表示2点开始执行
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."MONDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0'); --每天收集;周一收集;2点0分0秒时开始收集
END;
/
SQL> BEGIN 2 DBMS_SCHEDULER.SET_ATTRIBUTE( 3 name => '"SYS"."MONDAY_WINDOW"', 4 attribute => 'REPEAT_INTERVAL', 5 value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0'); 6 END; 7 / PL/SQL procedure successfully completed. 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');
SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2 2 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 ------------------------------ -------------------------------------------------------------------------------- ---------------------------------------- MONDAY_WINDOW freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0 +000 03:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 7 rows selected. SQL> |
2.4 开启任务
BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."MONDAY_WINDOW"'); --开启周一收集任务
END;
/
SQL> BEGIN 2 DBMS_SCHEDULER.ENABLE( 3 name => '"SYS"."MONDAY_WINDOW"'); 4 END; 5 / PL/SQL procedure successfully completed. SQL> |
select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients order by window_next_time;
SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients order by window_next_time; WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE ------------------------------ --------------------------------------------------------------------------- ----- -------- WEDNESDAY_WINDOW 28-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED THURSDAY_WINDOW 29-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED FRIDAY_WINDOW 30-NOV-18 10.00.00.000000 PM PRC FALSE ENABLED SATURDAY_WINDOW 01-DEC-18 06.00.00.000000 AM PRC FALSE ENABLED SUNDAY_WINDOW 02-DEC-18 06.00.00.000000 AM PRC FALSE ENABLED MONDAY_WINDOW 03-DEC-18 02.00.00.000000 AM PRC FALSE ENABLED TUESDAY_WINDOW 04-DEC-18 10.00.00.000000 PM PRC FALSE ENABLED 7 rows selected. 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');
SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2 2 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 ------------------------------ -------------------------------------------------------------------------------- ---------------------------------------- MONDAY_WINDOW freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0 +000 03:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 7 rows selected. SQL> |