cpu quantum造成的Oracle等待事件解决办法

不少接触数据库的朋友有一个困扰已久的问题——resmgr:cpu quantum。已经遇过不少次这种CPU突然全绿的情况，通过隐含参数屏蔽了一下，方便研究。

刚好有人问我这个问题，就干脆翻文档写一篇文章给这位朋友。

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

alter system set resource_manager_plan='';

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

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

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

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

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

Top User Events

EventEvent Class% ActivityAvg Active Sessions

resmgr:cpu quantumScheduler34.692.90

CPU + Wait for CPUCPU26.622.23

latch: library cacheConcurrency25.912.17

latch: shared poolConcurrency5.050.42

latch freeOther4.150.35

Top Event P1/P2/P3 Values

Event% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3

resmgr: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 OVER THROTTLING

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

Bug 7527260  HIGH WAIT EVENTS ON "RESMGR CPU QUANTUM" WHEN RESOURCE MANAGER IS ENABLED   Fixed in patchset 10.2.0.4

Workaround by setting the parameters :

_enable_NUMA_optimization=FALSE

_db_block_numa=1

Bug 6874858  - Poor performance with Resource Manager when RMAN is running

Fixed in 11g Release2 and planned to be included in patchset 10.2.0.5 and 11.1.0.7

Workaround:

Disable Resource Manager.

One-off patches available Patch 6874858

See also Note 759503.1  Resource Manager Plan Changes Settings Every Week

which might be causing higher waits on 'RESMGR:  ' events due to the changed Resource Plan.

转载oracle认证网