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;
Recent Comments