等待事件cursor: pin S wait on X及library cache lock的处理过

2020-10-12 00:00:00 等待 调度 窗口 小时 周末

在一次性能监控中,发现DBTime70%花在两个等待事件cursor: pin S wait on Xlibrary 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.

相关文章