November 29, 2012

resmgr:cpu quantum

User ping me and complain one DB is slow.

After checking found the server load is very low, CPU/IO 90+ percent free.
But many sessiones waiting on event “resmgr:cpu quantum”.
From AWR we can see “resmgr:cpu quantum” is the TOP ONE and consuming 100% DB time:


resmgr:cpu quantum” means a session is waiting in a resource queue, wait until resource manager assign CPU pieces to it.

Obviously we hit a resource manager's bug again. You can see my previous post for another case that we used to get:
http://dbakevin.blogspot.com/2012/04/test.html

Resource manager is not enabled in this DB:
SQL> show parameter resour
NAME                          TYPE                 VALUE
----------------------------- -------------------- ------------
resource_manager_plan         string               NONE

But we know by default there is a maintaince task defined for database:
DEFAULT_MAINTENANCE_PLAN

And there are windows defined for the maintaince task and there are resource plan defined for the windows.

So once the maintaince task begin to run, the resource plan will be activited along with the window. And then we hit the bug.
In our enviroment it automaticlly recovered due to the window end at 2:00 PM.
So after 2:00 PM, the Database suddendly recovered and become OK.

To permanently solve this, we can simply disable the default maintaince plan.
Refference:
High "Resmgr:Cpu Quantum" Wait Events In 11g Even When Resource Manager Is Disabled [ID 949033.1]
Resource Manager and Sql Tunning Advisory DEFAULT_MAINTENANCE_PLAN [ID 786346.1]


Or:
_resource_manager_always_on = false
Some tables for diagnose resource manager:
DBA_RSRC_MANAGER_SYSTEM_PRIVS;
DBA_RSRC_CONSUMER_GROUP_PRIVS;
DBA_RSRC_CONSUMER_GROUPS;
DBA_RSRC_PLANS;
DBA_RSRC_PLAN_DIRECTIVES;
DBA_RSRC_MAPPING_PRIORITY;
DBA_RSRC_GROUP_MAPPINGS;
V$RSRC_CONSUMER_GROUP;
V$RSRC_CONSUMER_GROUP_CPU_MTH;
V$RSRC_PLAN;
V$RSRC_PLAN_CPU_MTH;
V$RSRC_CONS_GROUP_HISTORY;
V$RSRC_PLAN_HISTORY;
V$RSRC_SESSION_INFO;

4 Comments:

RAJ said...

Perfect Inputs

Unknown said...

IF resource manager is enable then How disable resource manager will help to solve this problem?

Unknown said...

We had this same issue, i just restarted the database and the wait event was gone.

Kevin Zhang said...

yes and it will come back next time. You cannot always restart the DB every time it happens.

Post a Comment