Oracle等待事件: resmgr:cpu quant…

来源：互联网发布：淘宝账户能注销吗编辑：程序博客网时间：2024/06/05 08:21

High "Resmgr:Cpu Quantum" Wait Events In 11g Even When ResourceManager Is Disabled (文档 ID 949033.1) 转到底部

转到底部

修改时间:2013-3-20

类型:PROBLEM

状态:PUBLISHED

优先级:3

注释(0)

In thisDocument

SymptomsCauseSolutionReferences

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 andlater
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 waitson event 'resmgr:cpu quantum' might be noticedeven when resource manager isdisabled.

You already have confirmed parameter RESOURCE_MANAGER_PLAN isset 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 Schedulerdb file scattered read   171,071   14,778           86           3.81 User I/Olog file sync             28,575   10,810          378           2.78 Commitdb file sequential read  943,457   6,569             7           1.69 User I/ODB CPU                     2,133   0.55

Cause

This could be due to DEFAULT_MAINTENANCE_PLAN. From 11g onwardsevery weekday window has a pre-defined Resource Plan calledDEFAULT_MAINTENANCE_PLAN, which will become active once the relatedwindow opens.

Following entries can also be noted in alert log at the time ofissue.

Wed Sep 16 02:00:00 2009
Clearing Resource Manager plan via parameter
:
Wed Sep 16 22:00:00 2009
Setting Resource Manager planSCHEDULER[0x2C55]:DEFAULT_MAINTENANCE_PLAN via schedulerwindow
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN viaparameter
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 knownbugs:
Note 392037.1 - Database 'Hangs'. Sessions Wait for 'resmgr:cpuquantum'

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

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

To disable the DEFAULT_MAINTENANCE_PLAN you can use the below stepsas suggested in Note 786346.1

1. Set the current resource manager plan to null (or another planthat 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:

executedbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

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

execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
executedbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');

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

executedbms_scheduler.set_attribute('','RESOURCE_PLAN','');

-------------------------------------------------------------------------------------------------

http://www.eygle.com/archives/2011/07/events_resmgr_cpu_quantum.html

这个问题显然是和资源管理相关的,如果启用资源管理计划,就可能遇到这个问题.所以常规的解决方案是禁用资源管理,禁用缺省的维护计划(DEFAULT_MAINTENANCE_PLANMetalink:786346.1)

alter system set resource_manager_plan='';
executedbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');and
executedbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('<windowname>','RESOURCE_PLAN','');

以上是针对Oracle 11g的一种解决方案.

从以下的Event Class中也可以看到Scheduler的属性:

Top User Events

EventEvent Class% ActivityAvg Active Sessionsresmgr:cpu quantumScheduler34.692.90CPU + Wait for CPUCPU26.622.23latch: library cacheConcurrency25.912.17latch: shared poolConcurrency5.050.42latch freeOther4.150.35

Top Event P1/P2/P3 Values

Event% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3resmgr:cpu quantum34.69"2","0","0"27.34location "1","0","0"3.95 "3","0","0"3.40 latch: library cache26.16"14186265672","214","0"4.83addressnumbertries "14186265512","214","0"4.43 "14186266152","214","0"3.45 latch: shared pool5.20"1611577728","213","0"2.72addressnumbertries "1611577568","213","0"2.31 latch free4.20"9464682576","238","0"1.34addressnumbertries "1610749880","205","0"1.26 "14251367560","127","0"1.03
但是很多用户会发现禁用资源计划很多时候没有作用.我第一次遇到这个问题时,第一反应就是直接去寻找是否有隐含参数可以禁用Oracle缺省启用的资源调度,最后通过以下参数设置解决问题:
_resource_manager_always_on = false

在那个案例中,相关的等待事件是: resmgr:active threads,通过隐含参数可以将始终打开的资源计划关闭.

当然还有几个BUG会导致类似的问题,以下是MOS上的相关问题解决方案,提供供参考:
Bug 8221960 WAITS FOR"RESMGR CPU QUANTUM"

One-off patches available Patch 8221960

BUG 7510766 - RESOURCE MANAGER IS OVERTHROTTLING

Fixed in 11g Release2 and planned to be included inpatchset 10.2.0.5

Bug 7527260 HIGH WAIT EVENTS ON "RESMGR CPUQUANTUM" WHEN RESOURCE MANAGER ISENABLED Fixed in patchset10.2.0.4
Workaround by setting the parameters :

_enable_NUMA_optimization=FALSE
_db_block_numa=1

Bug 6874858 - Poor performance with ResourceManager when RMAN is running

Fixed in 11g Release2 and planned to be included inpatchset 10.2.0.5 and 11.1.0.7
Workaround:
Disable Resource Manager.
One-off patches available Patch6874858

See also Note 759503.1 Resource Manager PlanChanges Settings Every Week
which might be causing higher waits on 'RESMGR: 'events due to the changed Resource Plan.

0 0