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

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

转到底部

---

In this Document Symptoms   Cause   Solution   References APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.1 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 is 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. In 10gR2, DEFAULT_MAINTENANCE_PLAN is associated with WEEKNIGHT_WINDOW and WEEKEND_WINDOW. 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. The steps provided should not disable the Automated Database Maintenance Tasks or the maintenance window , but will only remove the DEFAULT_MAINTENANCE_PLAN resource manager plan assigned to it and should work normally with no resource manager plan. 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('<window name>','RESOURCE_PLAN','');   REFERENCES NOTE:392037.1 - Database 'Hangs'. Sessions Wait for 'resmgr:cpu quantum' NOTE:1339803.1 - Recommended Patches for CPU Resource Manager NOTE:1590014.1 - Excessive throttling by CPU Manager NOTE:16392079.8 - Bug 16392079 - Sessions hang waiting for 'resmgr:cpu quantum' with Resource Manager