User Sessions stuck on resmgr:cpu quantum wait event

We were experiencing lot of session getting stuck on resmgr:cpu quantum in our database.
In fact at a time we had 70 sessions which were stuck on this wait event and our cpu load average was touching 60

Checking active resource plan, we found that DEFAULT_MAINTENANCE_PLAN was active. As per 11g Docs

In this plan, any sessions in the SYS_GROUP consumer group get priority. (Sessions in this group are sessions created by user accounts SYS and SYSTEM.) Any resource allocation that is unused by sessions in SYS_GROUP is then shared by sessions belonging to the other consumer groups and subplans in the plan. Of that allocation, 25% goes to maintenance tasks, 5% goes to background processes performing diagnostic operations, and 70% goes to user sessions. To reduce or increase resource allocation to the automated maintenance tasks, you make adjustments to DEFAULT_MAINTENANCE_PLAN.

These plans are associated to 11g windows like MONDAY_WINDOW. You can check it using following query

col window_name format a17
col RESOURCE_PLAN format a25
col LAST_START_DATE format a50
col duration format a15
col enabled format a5
select window_name, RESOURCE_PLAN, LAST_START_DATE, DURATION, enabled from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME	  RESOURCE_PLAN 	    LAST_START_DATE				       DURATION        ENABL
----------------- ------------------------- -------------------------------------------------- --------------- -----
MONDAY_WINDOW	  DEFAULT_MAINTENANCE_PLAN  16-JAN-12 10.00.00.007154 PM PST8PDT	       +000 04:00:00   TRUE
TUESDAY_WINDOW	  DEFAULT_MAINTENANCE_PLAN  10-JAN-12 10.00.00.002781 PM PST8PDT	       +000 04:00:00   TRUE
WEDNESDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN  11-JAN-12 10.00.00.008333 PM PST8PDT	       +000 04:00:00   TRUE
THURSDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN  12-JAN-12 10.00.00.011284 PM PST8PDT	       +000 04:00:00   TRUE
FRIDAY_WINDOW	  DEFAULT_MAINTENANCE_PLAN  13-JAN-12 10.00.00.010937 PM PST8PDT	       +000 04:00:00   TRUE
SATURDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN  14-JAN-12 06.00.00.146968 AM PST8PDT	       +000 20:00:00   TRUE
SUNDAY_WINDOW	  DEFAULT_MAINTENANCE_PLAN  15-JAN-12 06.00.00.003916 AM PST8PDT	       +000 20:00:00   TRUE
WEEKNIGHT_WINDOW									       +000 08:00:00   FALSE
WEEKEND_WINDOW										       +002 00:00:00   FALSE

 

You can disable the resource_plan by using following commands

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','');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');

Verify that resource_plan is disabled

select window_name, RESOURCE_PLAN, LAST_START_DATE, DURATION, enabled from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME	  RESOURCE_PLAN 	    LAST_START_DATE				       DURATION        ENABL
----------------- ------------------------- -------------------------------------------------- --------------- -----
MONDAY_WINDOW				    16-JAN-12 10.00.00.007154 PM PST8PDT	       +000 04:00:00   TRUE
TUESDAY_WINDOW				    10-JAN-12 10.00.00.002781 PM PST8PDT	       +000 04:00:00   TRUE
WEDNESDAY_WINDOW			    11-JAN-12 10.00.00.008333 PM PST8PDT	       +000 04:00:00   TRUE
THURSDAY_WINDOW 			    12-JAN-12 10.00.00.011284 PM PST8PDT	       +000 04:00:00   TRUE
FRIDAY_WINDOW				    13-JAN-12 10.00.00.010937 PM PST8PDT	       +000 04:00:00   TRUE
SATURDAY_WINDOW 			    14-JAN-12 06.00.00.146968 AM PST8PDT	       +000 20:00:00   TRUE
SUNDAY_WINDOW				    15-JAN-12 06.00.00.003916 AM PST8PDT	       +000 20:00:00   TRUE
WEEKNIGHT_WINDOW									       +000 08:00:00   FALSE
WEEKEND_WINDOW										       +002 00:00:00   FALSE

I was not concerned about automated tasks taking more cpu as it was already disabled using

execute DBMS_AUTO_TASK_ADMIN.DISABLE;

Leave a Reply