Tuesday, August 21, 2012

(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] 

1 comment:

  1. Mummys Gold Casino & Resort - Jackson City, MS
    Welcome to Mummys Gold 남양주 출장안마 Casino & 원주 출장샵 Resort, a premier 통영 출장마사지 casino gaming destination 대전광역 출장샵 in the 구미 출장샵 heart of the Mississippi River. With over 2000 slots and 25 tables

    ReplyDelete