关于resmgr:cpu quantum异常等待处理

今天收到告警短信
故障信息：服务器：XXX_DB1发生： Processor load is too high on XXX _DB1故障!
告警时间：2017.06.24   12:22:42
告警主机： XXX _DB1
IP地址： x.x.x.x
告警信息： Processor load is too high on XXX _DB1
告警项目：system.cpu.load[all,avg1]
故障值： 22.49
当前状态：PROBLEM:22.49
事件ID：297252
负载已经很高了(备注８核）
查看服务器负载，绝大部分是oracle进程
立即做了一个awr快照
发现有一个异常等待

此服务器安装的是oracle 11.2.0.1未安裝任何补丁
Database 'Hangs'. Sessions Wait for 'resmgr:cpu quantum' (文档 ID 392037.1)　此文档记录
The session is waiting to be allocated a quantum of cpu. 
This event occurs when the resource manager is enabled and is throttling CPU consumption. 
To reduce the occurrence of this wait event, increase the CPU allocation for the session's current consumer group.

Wait Time: The time the session waited to acquire a CPU quantum
"

Event 'resmgr: cpu quantum' is a standard event used by resource manager to controls the allocation of CPU to processes. It is possible that a 'hang' situation could be seen when sessions use significant CPU for whatever reason. Alternatively this could also indicate that true hang situation is taking place.

SOLUTION

Before looking at specifics, it is prudent to ensure that you are running with recommended patches installed for Resource Manager.  Refer to the following article for details:

Document 1339803.1 Recommended Patches for CPU Resource Manager

Additionally there are some known issues associated with resource manager and the Wait Event "Resmgr:Cpu Quantum" listed in the following articles:

As a workaround, resource management could be disabled:
我们可以关闭resouce management
Resource Manager and SQL Tuning Advisory DEFAULT_MAINTENANCE_PLAN (文档 ID 786346.1)

 To disable the resource manager you can use the below steps. 

++ set the current resource manager plan to null (or another plan that is not restrictive): 

alter system set resource_manager_plan='' scope=both

++ 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',''); and 
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

For 11g, you need to change those 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','');

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

SQL> execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');

To disable SQL tuning you can use the below procedure.  

BEGIN 
DBMS_AUTO_TASK_ADMIN.DISABLE( 
client_name => 'sql tuning advisor', 
operation => NULL, 
window_name => NULL); 
END; 
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24486203/viewspace-2141217/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24486203/viewspace-2141217/