Resource manager plan verification - Fix : resmgr:cpu quantum

resmgr:cpu quantum

Application users called me and complain application performance is very slow and L2 users of database team found 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.

If the "_resource_manager_always_on" oracle hidden parameter value is "TRUE", the the database will consume more CPU for their inter-plans which are required. If maintenance plan scheduled by DBA manually and DB environment is OLTP, then it can be off. It will improve the performance. But as it is hidden parameter can be set "FALSE" in production with Oracle's recommendation only.

Drilling the issue:
a) Verify the Parameter:
SQL> show parameter resource_manager;  

SQL> show parameter resource_manager;  

NAME                                              TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_cpu_allocation      integer     2
resource_manager_plan                      string      SCHEDULER[0x318E]:DEFAULT_MAIN
                                                                         TENANCE_PLAN

b) To see the resource plan:

SQL>  select * from resource_plan_directive$;  -- See the output
Here sample example to see plan window counts:
SQL> select plan,count(1) from resource_plan_directive$
group by plan order by 1;
output:

So many windows consuming CPU resource as it is on and doing some activities.

c) To turn off the resource manager:

$ connect / as sysdba
$ startup nomount
SQL> alter system set "_resource_manager_always_on"=false scope=spfile;
SQL> shutdown immediate
SQL> startup;

d) Check & Remove plan windoes ( Don't do without Oracle's recommendation)
SQL> select * from DBA_RSRC_PLAN_DIRECTIVES where plan = 'INTERNAL_PLAN';
SQL> exec dbms_resource_manager.create_pending_area();
SQL> exec dbms_resource_manager.delete_plan_directive('INTERNAL_PLAN', 'OTHER_GROUPS');
SQL> exec dbms_resource_manager.submit_pending_area();
SQL> exit


If:    Unable to open database: ora-600 [kskopen1] error found, then

Solution
1) Set this parameter in init.ora and startup again:

_resource_manager_always_on = FALSE

2) After database open run this query

SQL> set pages 999
SQL> select * from DBA_RSRC_PLAN_DIRECTIVES where plan = 'INTERNAL_PLAN';

The query should return only one row:
INTERNAL_PLAN OTHER_GROUPS CONSUMER_GROUP
0 0 0 0 0 0 0
0
FALSE

Other sessions at lower priority
YES

3) If in your case the query returns more than one row, remove the other rows, and set back
"_resource_manager_always_on"=true.

Click here to Read more about resource manager plan in 11g 

Caution: This is a hidden parameter change activity. This may harmful to your database. Do only with Oracle support's recommendation only. You can change with your own risk.

Cheers !!!