在一次性能监控中，发现DBTime70%花在两个等待事件cursor: pin S wait on X及library cache lock，当时数据库每个小时几十万次的DML操作，这些SQL发生严重的等待

经分析发现此时的统计信息进程正在执行call dbms_stats.gather_database_stats_job_proc ( )，因为是周末所以统计信息早上6点开始持续20小时，这样当数据不断变化时，就会不断的收集统计信息，造成严重的客户端用户的cursor: pin S wait on X等待和library cache lock以及SYS用户的library cache lock等待。所以决定调整周末的调度任务窗口与工作日一致。下面是修改过程

修改job的运行时间，防止在周末用户业务收阻

1 查询当前统计时间段

SQL> 

  col WINDOW_NAME for a30;

  col REPEAT_INTERVAL for a60;

  col DURATION for a30;

  set line 200;

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> /

WINDOW_NAME                                        REPEAT_INTERVAL                                              DURATION

-------------------------------------------------- ------------------------------------------------------------ ------------------------------

SUNDAY_WINDOW                                      freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00

FRIDAY_WINDOW                                      freq=daily;byday=FRI;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

TUESDAY_WINDOW                                     freq=daily;byday=TUE;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

THURSDAY_WINDOW                                    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00

MONDAY_WINDOW                                      freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00

7 rows selected.

发现周末早上6点开始持续20小时 ，工作日22点开始持续4个小时 

禁止调度窗口：

 begin

    DBMS_SCHEDULER.DISABLE(

    name => '"SYS"."SUNDAY_WINDOW"');

   end;

    /

begin

    DBMS_SCHEDULER.DISABLE(

    name => '"SYS"."SATURDAY_WINDOW"');

   end;

    /

修改调度窗口时间

 BEGIN

    DBMS_SCHEDULER.SET_ATTRIBUTE(

    name => '"SYS"."SUNDAY_WINDOW"',

    attribute => 'REPEAT_INTERVAL',

    value =>'freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0');

    end;

    /

 BEGIN

    DBMS_SCHEDULER.SET_ATTRIBUTE(

    name => '"SYS"."SUNDAY_WINDOW"',

     attribute => 'DURATION',

    value =>numtodsinterval(240,'minute'));

  end;

   /

 BEGIN

    DBMS_SCHEDULER.SET_ATTRIBUTE(

    name => '"SYS"."SATURDAY_WINDOW"',

    attribute => 'REPEAT_INTERVAL',

    value =>'freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0');

    end;

    /

BEGIN

    DBMS_SCHEDULER.SET_ATTRIBUTE(

    name => '"SYS"."SATURDAY_WINDOW"',

     attribute => 'DURATION',

    value =>numtodsinterval(240,'minute'));

  end;

   /

开启调度窗口 

 begin

    DBMS_SCHEDULER.ENABLE(

    name => '"SYS"."SUNDAY_WINDOW"');

   end;

    /

begin

    DBMS_SCHEDULER.ENABLE(

    name => '"SYS"."SATURDAY_WINDOW"');

   end;

    /

再次查询结果：

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

------------------------------ ------------------------------------------------------------ ------------------------------

SUNDAY_WINDOW                     freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0         +000 04:00:00

SATURDAY_WINDOW                 freq=daily;byday=SAT;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

THURSDAY_WINDOW                freq=daily;byday=THU;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

TUESDAY_WINDOW                   freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00

MONDAY_WINDOW                    freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00

7 rows selected.