在一次性能监控中，发现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.
