Oracle Study之--resmgr:cpu quantum等待事件

痴心VS绝对

　　Oracle Study之--resmgr:cpu quantum等待事件
　　
　　在AWR Report中出现“resmgr:cpu quantum”等待事件：
　　http://img.blog.csdn.net/20150602162750478?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbHF4MDQwNQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
　　
　　“resmgr:cpu quantum”等待事件：
　　参考metalink的解决方案，是oracle资源管理方面的问题，原文如下：
Symptoms
　　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>
　　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 inNote 786346.1
　　1. Set the current resource manager plan to null (or another plan that is not restrictive):
　　alter system set resource_manager_plan='';
　　2. Change the active windows to use the null resource manager plan (or other nonrestrictive plan)
　　using:3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:
　　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('','RESOURCE_PLAN','');
　　execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');
References
　　NOTE:392037.1 - Database Hangs. Sessions wait for 'resmgr:cpu quantum'
　　NOTE:756734.1 - 11g: Scheduler Maintenance Tasks or Autotasks
　　NOTE:786346.1 - Resource Manager and Sql Tunning Advisory DEFAULT_MAINTENANCE_PLAN
　　NOTE:806893.1 - Large Waits With The Wait Event "Resmgr:Cpu Quantum"
　　注意：
　　但是很多用户会发现禁用资源计划很多时候没有作用.可以禁用Oracle缺省启用的资源调度,最后通过以下参数设置解决问题:
　　_resource_manager_always_on = false