resmgr:cpu quantum等待

《resmgr:cpu quantum等待》有 4 条评论

1. 惜分飞 说：

   2013 年 9 月 22 日22:47

   lter system set resource_manager_plan='' scope=both;
   execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
   execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
   execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
   execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
   execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
   execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
   execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
   execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
   
2. 惜分飞 说：

   2013 年 9 月 2 日16:47

   可以设置隐含参数关闭，不过需要重启生效

   SQL> alter system set "_resource_manager_always_on"=false;
   alter system set "_resource_manager_always_on"=false
                    *
   ERROR at line 1:
   ORA-02095: specified initialization parameter cannot be modified
   
   
   SQL> alter system set "_resource_manager_always_on"=false scope=spfile;
   
   System altered.
   
3. 惜分飞 说：

   2012 年 6 月 14 日14:50

   High “Resmgr:Cpu Quantum” Wait Events In 11g Even When Resource Manager Is Disabled

   Applies to:
   Oracle Server - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
   Information in this document applies to any platform.
   ***Checked for relevance on 18-NOV-2011***
   
   Symptoms
   Issuing a sqlplus / as sysdba might be hanging and/or high waits on event 'resmgr:cpu quantum' 
   might be noticed even when resource manager is disabled.    
   
   You already have confirmed parameter RESOURCE_MANAGER_PLAN is set to null 
   but still noticing the above wait events.
   
   Top 5 Timed Foreground Events:
   
   Event                    Waits   Time(s)  Avg wait(ms) % DB time Wait Class
   
   ------------------------ ------- -------- ------------ -------------- ---------- -----------
   
   
   resmgr:cpu quantum         1,596  346,281       216968          89.19 Scheduler
   
   
   db file scattered read   171,071   14,778           86           3.81 User I/O
   
   
   log file sync             28,575   10,810          378           2.78 Commit
   
   
   db file sequential read  943,457   6,569             7           1.69 User I/O
   
   
   DB CPU                     2,133   0.55
   
   Cause
   This could be due to DEFAULT_MAINTENANCE_PLAN. From 11g onwards every weekday window has 
   a pre-defined Resource Plan called DEFAULT_MAINTENANCE_PLAN, which will become active 
   once the related window opens.
   
   Following entries can also be noted in alert log at the time of issue.
   
   Wed Sep 16 02:00:00 2009
   Clearing Resource Manager plan via parameter
   :
   Wed Sep 16 22:00:00 2009
   Setting Resource Manager plan SCHEDULER[0x2C55]:DEFAULT_MAINTENANCE_PLAN via scheduler window
   Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
   Wed Sep 16 22:00:05 2009
   Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
   
   Solution
   To disable the DEFAULT_MAINTENANCE_PLAN you can use the below steps as suggested in Note 786346.1
   
   1. Set the current resource manager plan to null (or another plan that is not restrictive):
   
   alter system set resource_manager_plan='' scope=both;
   
   2. Change the active windows to use the null resource manager plan (or other nonrestrictive plan) using:
   
   execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN',''); 
   execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
   
   3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:
   
   execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');
   
4. 惜分飞 说：

   2012 年 6 月 14 日14:44

   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
   
   As said you asked the advantages and disadvantages of this plan,
   Advantage: You have space advisor which gives you recommendation of segments if they are fregmented.You have Gather stats job, which is very crucial interms of performance, you might not have any custom scripts to gather stats so this job do it automatically.You have tunning advisory which tell you how to tune bad running queries. So all of these jobs are important and resource consuming.To make it run faster and efficient with enough resources, resource manager gives them priority under default maintenance group.
   Disadvantage: You have custom jobs which runs during default maintenance window time, then these can have negative impact on performance. As default jobs are very resource intensive and runs in high priority and left over resource is given to other group which is obviously impact your custom job.