High "Resmgr:Cpu Quantum" Wait Events

High "Resmgr:Cpu Quantum" Wait Events In 11g Even When Resource Manager Is Disabled (文件 ID 949033.1)
APPLIES TO:


Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
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


Please review the following document first to address any known bugs:




Note 392037.1 - Database 'Hangs'. Sessions Wait for 'resmgr:cpu quantum'


Note 1339803.1   Recommended Patches for CPU Resource Manager


It may be better to move the maintenance windows to a time of day when CPU resources might be more available for such tasks to run and complete.


The following solution should only be used as a last resort because it may lead to other issues in the long run if Oracle has inadequate maintenance windows to collect new optimizer stats, find better execution plans for expensive SQL, purge AWR, etc.




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','');


Since in 11g there are more Maintenance Windows, we should add them too:


execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN',''); 
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
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','');




3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:


execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');