DBMS_SCHEDULER修改资源调度总结

DBMS_SCHEDULER修改资源调度总结：
相关的视图：

select CLIENT_NAME,STATUS from DBA_AUTOTASK_CLIENT;
DBA_AUTOTASK_WINDOW_CLIENTS：显示MAINTENANCE_WINDOW_GROUP时间窗口的相关信息各个作业的状态。
dba_scheduler_windows:显示数据库中所有scheduler的作业窗口
dba_scheduler_wingroup_members：显示数据库中scheduler window group的所有member
dba_scheduler_groups

默认情况下：每天(周一到周五)晚上10点开始，持续4小时。周六(日)早上6点开始，持续20小时。
SQL> set lines 200 pages 1000
SQL> col window_name for a30
SQL> col window_next_time for a45
SQL> col optimizer_stats for a15
SQL> col segment_advisor for a15
SQL> col sql_tune_advisor for a15
SQL> select window_name,window_next_time,optimizer_stats,segment_advisor,sql_tune_advisor from DBA_AUTOTASK_WINDOW_CLIENTS;

7 rows selected.

SQL>

查看自动作业的详细情况
SQL> set lines 200 pages 1000
SQL> col comments for a40
SQL> col window_name for a20
SQL> col next_date for a20
SQL> col repeat_interval for a60
SQL> col duration for a15
SQL> SELECT t3.comments,to_char(t1.next_start_date,'yyyy-mm-dd hh24') next_date,t1.window_name, t1.repeat_interval, t1.duration
2 FROM dba_scheduler_windows t1, dba_scheduler_wingroup_members t2,dba_scheduler_groups t3
3 WHERE t1.window_name = t2.window_name
4 and t2.WINDOW_GROUP_NAME=t3.GROUP_NAME
5 and t3.comments in ('auto optimizer stats collection','auto space advisor','sql tuning advisor') order by 1,2;

COMMENTS NEXT_DATE WINDOW_NAME REPEAT_INTERVAL DURATION
---------------------------------------- -------------------- -------------------- ------------------------------------------------------------ ---------------
auto optimizer stats collection 2017-12-28 22 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto optimizer stats collection 2017-12-29 22 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto optimizer stats collection   2017-12-30 06 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
auto optimizer stats collection   2017-12-31 06 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
auto optimizer stats collection   2018-01-01 22 MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto optimizer stats collection   2018-01-02 22 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto optimizer stats collection   2018-01-03 22 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto space advisor 2017-12-28 22 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto space advisor   2017-12-29 22 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto space advisor   2017-12-30 06 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
auto space advisor   2017-12-31 06 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
auto space advisor   2018-01-01 22 MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto space advisor   2018-01-02 22 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
auto space advisor   2018-01-03 22 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
sql tuning advisor 2017-12-28 22 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
sql tuning advisor   2017-12-29 22 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
sql tuning advisor   2017-12-30 06 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
sql tuning advisor   2017-12-31 06 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
sql tuning advisor   2018-01-01 22 MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
sql tuning advisor   2018-01-02 22 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
sql tuning advisor   2018-01-03 22 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00

21 rows selected.

SQL>

例子：
开发写的sp中，有时存在ddl语句(例如：EXECUTE IMMEDIATE 'TRUNCATE TABLE test'; test正好是实体表)。在执行sp的时候，可能存在下列的报错ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 。经过排查，是由于统计信息收集的作业时，影响了sp的正常执行。
下面提供两种解决方法：
方法一：修改统计信息的收集时间段。
方法二：将涉及的表统计信息，进行lock处理。系统在收集统计信息的时候，会跳过该表

方法一例子：将周六的从早上6点开始的统计信息作业，修改到中午12点开始执行，持续时间修改为12个小时。
可以修改原作业的窗口属性。DBMS_SCHEDULER.SET_ATTRIBUTE修改。也可以新建时间窗口。这里我采用新建时间窗口实现
1.禁用周六的统计信息收集作业
BEGIN
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => 'SATURDAY_WINDOW');
END;
/

SQL> select window_name,window_next_time,optimizer_stats,segment_advisor,sql_tune_advisor from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME WINDOW_NEXT_TIME OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISO
------------------------------ --------------------------------------------- --------------- --------------- ---------------
MONDAY_WINDOW 01-JAN-18 10.00.00.000000 PM PRC ENABLED ENABLED ENABLED
TUESDAY_WINDOW 02-JAN-18 10.00.00.000000 PM PRC ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW 03-JAN-18 10.00.00.000000 PM PRC ENABLED ENABLED ENABLED
THURSDAY_WINDOW 28-DEC-17 10.00.00.000000 PM PRC ENABLED ENABLED ENABLED
FRIDAY_WINDOW 29-DEC-17 10.00.00.000000 PM PRC ENABLED ENABLED ENABLED
SATURDAY_WINDOW 30-DEC-17 06.00.00.000000 AM PRC DISABLED ENABLED ENABLED ---周六收集统计信息的窗口已禁用
SUNDAY_WINDOW 31-DEC-17 06.00.00.000000 AM PRC ENABLED ENABLED ENABLED

7 rows selected.

SQL>
2.新建时间窗口
新建一个周六时间窗口
BEGIN
dbms_scheduler.create_window(
window_name=>'SATURDAY_WINDOW_USER',
resource_plan=>'DEFAULT_MAINTENANCE_PLAN',
repeat_interval=>'freq=daily;byday=SAT;byhour=12;byminute=0;bysecond=0', ---从12点开始
duration=>interval '12' hour, ----持续时间12小时
comments=>'Saturday window tasks by user'); ---comments注释
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/

SQL> select window_name,repeat_interval,duration from SYS.DBA_SCHEDULER_WINDOWS t where window_name='SATURDAY_WINDOW_USER';

WINDOW_NAME REPEAT_INTERVAL DURATION
------------------------- ------------------------------------------------------------ -------------------------
SATURDAY_WINDOW_USER freq=daily;byday=SAT;byhour=12;byminute=0;bysecond=0 +000 12:00:00

SQL>
3.将新建的窗口时间加入到window group
BEGIN
dbms_scheduler.add_group_member('MAINTENANCE_WINDOW_GROUP','SATURDAY_WINDOW_USER');
END;
/

4.启动新的时间窗口
BEGIN
dbms_auto_task_admin.enable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => 'SATURDAY_WINDOW_USER');
END;
/

SQL> select window_name,window_next_time,optimizer_stats,segment_advisor,sql_tune_advisor from DBA_AUTOTASK_WINDOW_CLIENTS;

方法二：
1.首先确定表的统计信息(dbms_stats.set_table_stats+DBMS_STATS.SET_COLUMN_STATS),然后设置表的统计信息(后面可以根据执行计划设置比较靠谱的统计信息)。比如表大概多少行
SQL> execute dbms_stats.set_table_stats( ownname => 'ERWA',tabname =>'EMP',numrows => 5000,numblks => 7,avgrlen => 100);
PL/SQL procedure successfully completed
SQL>
2.将表的统计信息lock住
SQL> execute DBMS_STATS.LOCK_TABLE_STATS('ERWA','EMP')
PL/SQL procedure successfully completed
SQL>

SQL> EXECUTE DBMS_AUTO_TASK_ADMIN.enable; <<<<<<启用auto_task

PL/SQL procedure successfully completed.

SQL>EXECUTE DBMS_AUTO_TASK_ADMIN.disable; <<<<<<禁用auto_task

参考资料；

FAQ: Automatic Statistics Collection (文档 ID 1233203.1)
How to Create an Own Maintenance Window for Autotask Jobs in 11g (文档 ID 1300313.1)