VMCD.ORG

Focus on database mail:ylouis83#gmail.com

11g DEFAULT_MAINTENANCE_PLAN 总结

Posted by admin on June 18th, 2012

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

CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
13,664.95 12 1,138.75 8.99 15,656.35 87.28 11.83 3a1wvmtvsdvqv PL/SQL Developer call pkg_rpt_union.pro_rpt_uni…
10,474.67 5 2,094.93 6.89 14,773.14 70.90 28.90 6mcpb06rctk0x DBMS_SCHEDULER call dbms_space.auto_space_adv…
9,215.02 5 1,843.00 6.06 17,031.19 54.11 45.65 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas…
8,881.02 2,737 3.24 5.84 11,250.82 78.94 21.14 9q7k9nbpvk8pv DBMS_SCHEDULER SELECT NVL(SUM(TIME_WAITED/100…
8,555.57 96 89.12 5.63 22,080.53 38.75 4.02 7ctdtkwpbrj4k sqlplus@racdb02 (TNS V1-V3) BEGIN edw1_user.pkg_rpt_today….
7,474.88 65,717 0.11 4.92 7,538.15 99.16 0.08 6m4s9pjrydbxt oracle@tracker-db2 (TNS V1-V3)

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

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>