(HOWTO: Oracle DB) - Ebiz database running slow due to RESOURCE MANAGER feature is enabled

Ebiz database running slow due to RESOURCE MANAGER feature is enabled

RESOURCE MANAGER feature is enable in the database by default when you install 11g. This feature limit the amount of resources a process can use. This impact all payroll processes and other batch processes and cause session to wait on an event “resmgr:cpu quntum”.

This will hopefully remove the CPU limit that is causing “resmgr:cpu quntum” waits in your production database affecting concurrent programs mainly running during business hours.

Check database parameters

SQL> show parameter resource;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

resource_limit boolean FALSE

resource_manager_cpu_allocation integer 8

resource_manager_plan string SCHEDULER[0x7AFFC]:DEFAULT_MAINTENANCE_PLAN

Disable resource manager from spfile

SQL> alter system set resource_manager_plan='' scope=both;

System altered.

Then, check resource manager windows

SQL> select window_name, resource_plan from dba_scheduler_windows;

WINDOW_NAME RESOURCE_PLAN

------------------------------ ------------------------------

WEEKNIGHT_WINDOW DEFAULT_MAINTENANCE_PLAN

WEEKEND_WINDOW DEFAULT_MAINTENANCE_PLAN

MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN

TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN

WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN

THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN

FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN

SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN

SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN

Next step is to un-assign the plan from the window

SQL> select 'execute dbms_scheduler.set_attribute('''||window_name||''',''RESOURCE_PLAN'','''');' from DBA_SCHEDULER_WINDOWS;

'EXECUTEDBMS_SCHEDULER.SET_ATTRIBUTE('''||WINDOW_NAME||''',''RESOURCE_PLAN'','''

--------------------------------------------------------------------------------

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

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

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

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

9 rows selected.

SQL> execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');

PL/SQL procedure successfully completed.

...

...

SQL> commit;

Finally, disableauto tasks

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

/

2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> SQL> commit;

All Done.

Reference: High "Resmgr:Cpu Quantum" Wait Events In 11g Even When Resource Manager Is Disabled [ID 949033.1]