查看数据仓库的awr报告发现以下问题 顺便总结一下之前碰到的一类问题

dbms_stats.gather_databas dbms_space.auto_space_advisor 耗费了系统大量的IO 并且耗费了大量的CPU 这可能引起cpu quantum 等待 相信不少TX遇到过 resmgr:cpu quantum等待 而时间点恰恰是22:00整,在11g 中oracle对automatic maintenance做出了调整 ：

The Wait Event: resmgr: cpu quantum

DBRM allocates CPU resources by maintaining an execution queue similar to the way the operating
system’s scheduler queues processes for their turn on the CPU. The time a session spends waiting in this
execution queue is assigned the wait event resmgr: cpu quantum. A CPU quantum is the unit of CPU
time (fraction of CPU) that Resource Manager uses for allocating CPU to consumer groups. This event
occurs when Resource Manager is enabled and is actively throttling CPU consumption. Increasing the
CPU allocation for a session’s consumer group will reduce the occurrence of this wait event and increase
the amount of CPU time allocated to all sessions in that group. For example, the CPU quantum wait
events may be reduced for the APPS resource group (currently 70% at level 1) by increasing the group’s
CPU allocation to 80%.

In Oracle 11 the automatic maintenance jobs (Space Advisor, Gather Stats, Tuning Advisor) are by default run using the Resource Manager. The default settings give these tasks up to 25% of the CPU during their scheduler windows.
If you prefer these tasks to always be taking a “back seat” compared to any other jobs running on the system it may be worth slightly adjusting the resource allocation of the DEFAULT_MAINTENANCE_PLAN.

In the DEFAULT_MAINTENANCE_PLAN (the default version) we have:
Level 1: SYS_GROUP: 100%
Level 2: ORA$AUTOTASK_SUB_PLAN 25% / ORA$DIAGNOSTICS: 5% / OTHER_GROUPS 70%
Level 3+: none

This means that OTHER_GROUPS may only get 70% of the CPU (unless the other plans are not using up their quota, in which case some of the spare quota will be available to OTHER_GROUPS). To ensure that the auto maintenance tasks only get resources when OTHER_GROUPS don’t need them you can simply change the plan directive for the DEFAULT_MAINTENANCE_PLAN

并且我们可以看到一个有趣的现象 在低版本升级到11g的库中 WEEKNIGHT_WINDOW，WEEKEND_WINDOW 也是作为了 DEFAULT_MAINTENANCE_PLAN的子windows，在Oracle 11g中，有7个预定义维护时间窗，5个针对每个工作日的，两个针对周末的（周六和周日）,七个预定义时间窗是按一周的每天命名的，所以即便默认开启了WEEKNIGHT_WINDOW，WEEKEND_WINDOW oracle依然会按照七个预定义时间窗一次执行
我们可以从一套10g升级到11g的库中得到证实：

9i/10g->11g

SQL> select window_name,resource_plan,comments from dba_scheduler_windows ;

WINDOW_NAME RESOURCE_PLAN COMMENTS
—————————— —————————— ————————————————————
WEEKNIGHT_WINDOW DEFAULT_MAINTENANCE_PLAN Weeknight window for maintenance task
WEEKEND_WINDOW DEFAULT_MAINTENANCE_PLAN Weekend window for maintenance task
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Monday window for maintenance tasks
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Tuesday window for maintenance tasks
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Wednesday window for maintenance tasks
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Thursday window for maintenance tasks
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Friday window for maintenance tasks
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Saturday window for maintenance tasks
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Sunday window for maintenance tasks

SQL> SELECT LOG_ID, TO_CHAR(LOG_DATE, ‘MM/DD/YYYY’), WINDOW_NAME, OPERATION
FROM DBA_SCHEDULER_WINDOW_LOG order by 2;

LOG_ID TO_CHAR(LO WINDOW_NAME OPERATION
———- ———- ———————————————————— ——————————
19763 06/05/2012 TUESDAY_WINDOW OPEN
19756 06/05/2012 MONDAY_WINDOW CLOSE
19798 06/06/2012 WEDNESDAY_WINDOW OPEN
19784 06/06/2012 TUESDAY_WINDOW CLOSE
19817 06/07/2012 WEDNESDAY_WINDOW CLOSE
19824 06/07/2012 THURSDAY_WINDOW OPEN
19845 06/08/2012 THURSDAY_WINDOW CLOSE
19850 06/08/2012 FRIDAY_WINDOW OPEN
19871 06/09/2012 FRIDAY_WINDOW CLOSE
19876 06/09/2012 SATURDAY_WINDOW OPEN
19903 06/10/2012 SUNDAY_WINDOW OPEN

LOG_ID TO_CHAR(LO WINDOW_NAME OPERATION
———- ———- ———————————————————— ——————————
19900 06/10/2012 SATURDAY_WINDOW CLOSE
19929 06/11/2012 MONDAY_WINDOW OPEN
19926 06/11/2012 SUNDAY_WINDOW CLOSE
19955 06/12/2012 TUESDAY_WINDOW OPEN
19948 06/12/2012 MONDAY_WINDOW CLOSE
19981 06/13/2012 WEDNESDAY_WINDOW OPEN
19976 06/13/2012 TUESDAY_WINDOW CLOSE
20007 06/14/2012 THURSDAY_WINDOW OPEN
20000 06/14/2012 WEDNESDAY_WINDOW CLOSE
20030 06/15/2012 THURSDAY_WINDOW CLOSE
20033 06/15/2012 FRIDAY_WINDOW OPEN

LOG_ID TO_CHAR(LO WINDOW_NAME OPERATION
———- ———- ———————————————————— ——————————
20052 06/16/2012 FRIDAY_WINDOW CLOSE
20059 06/16/2012 SATURDAY_WINDOW OPEN
20083 06/17/2012 SATURDAY_WINDOW CLOSE
20086 06/17/2012 SUNDAY_WINDOW OPEN

oracle按照 MONDAY_WINDOW->SUNDAY_WINDOW 一次执行 而在一套新装的11g中 Weeknight window and Weekend window are just for compatibility only

11g

SQL> select window_name,resource_plan,comments from dba_scheduler_windows ;

WINDOW_NAME RESOURCE_PLAN COMMENTS
—————————— —————————— ————————————————————
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Monday window for maintenance tasks
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Tuesday window for maintenance tasks
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Wednesday window for maintenance tasks
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Thursday window for maintenance tasks
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Friday window for maintenance tasks
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Saturday window for maintenance tasks
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN Sunday window for maintenance tasks
WEEKNIGHT_WINDOW Weeknight window – for compatibility only
WEEKEND_WINDOW Weekend window – for compatibility only

在系统出现大量的resmgr:cpu quantum等待时 我们可以采取以下方法

关闭 Resource Manager plan 这个方法xifenfei同学写过一篇故障处理过程 可以参照这篇metalink

对于DBRM来说 oracle通过调用DEFAULT_MAINTENANCE_PLAN来控制系统资源的分配，一般来说通过增加consumer group的最大资源可以减少调度过程
中的资源争用。其实我们同样可以通过disable auto task的方法来避免这个问题。

[oracle@db-41 ~]$ ora params _resource_manager_always_on

NAME VALUE DESCRIPTION
——————————————— ——————– ———————————————————————-
_io_resource_manager_always_on FALSE io resource manager always on
_resource_manager_always_on TRUE enable the resource manager always

对于这个问题 其实有一个更折中的方法 关闭 WEEKNIGHT_WINDOW,WEEKEND_WINDOW 禁止 Space Advisor，Tuning Advisor 但是Gather Stats还是建议不要禁止,对于很多系统而言 准确的统计信息往往更能辅助CBO做出正确的判断 这里给个例子 ：

BEGIN
dbms_auto_task_admin.disable(
client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL);
END;
/

BEGIN
dbms_auto_task_admin.disable(
client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/

begin
sys.dbms_scheduler.set_attribute(name => ‘SYS.xxx_WINDOW’, attribute => ‘repeat_interval’, value => ‘Freq=daily;ByDay=TUE;ByHour=2;ByMinute=0;BySecond=0’);
sys.dbms_scheduler.set_attribute(name => ‘SYS.xxx_WINDOW’, attribute => ‘duration’, value => ‘0 04:00:00’);
end;
/

修改为凌晨2点到6点收集统计信息 同时禁止 sql tuning advisor ，auto space advisor