>
Technical

Resource Manager Limits (ORA-29376)

So this is what happens when you hit that publish button too quickly – thanks Sayed for the feedback! A few corrections follow. :)


Just a brief post to mention a limit I’ve recently bumped up against. I couldn’t find any mention of this limit in the oracle docs or even on oracle’s support knowledge base. The only mention is in the text of a particular error message – I couldn’t find any further explanation or documentation.

Goal

Create a resource manager plan to guarantee fair scheduling between a large number of applications on a heavily consolidated 11.2.0.3 database. Create a consumer group for each application, put them all into a ratio-based plan so that each application gets an equal share of CPU under load.

Error

This is the error I received:

ORA-29382: validation of pending area failed
ORA-29376: number of consumer groups 312 in plan SHARED_MASTER_PLAN exceeds 28
ORA-06512: at "SYS.DBMS_RMIN", line 444
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 815
ORA-06512: at line 54

(08:06) ~
[oracle@collabn1 11203:RAC1]$ oerr ora 29376
29376, 00000, "number of consumer groups %s in plan %s exceeds %s"
// *Cause:    The number of consumer groups in the specified plan was
//            more than 28.
// *Action:   Change the plan to have no more than 28 consumer groups.

Cause

The resource manager has a surprisingly low limit (imo) on the number of allowed resource groups. When I read through the docs initially I hadn’t taken note of this limit, so the error surprised me. And upon returning to the docs I missed the off-hand reference to this limit in the admin guide. Finally found the reference later, thanks to Sayed’s feedback.

Version Limit Total Groups Reference
8.1 32 Admin Guide / Error Messages Doc
9.1 32 Admin Guide / Error Messages Doc
9.2 32 Admin Guide / Error Messages Doc
10.1 32 Admin Guide / Error Messages Doc
10.2 32 Admin Guide / Error Messages Doc
11.1 31 Admin Guide / Error Messages Doc
11.2 28 Admin Guide / Error Messages Doc
12.1 28 (non-CDB), 8 (PDB) Admin Guide (non-CDB) / Admin Guide (PDB) / Error Messages Doc

This limits what resource manager can do for any consolidation use case. It also makes me wonder if there’s a global limit for 12c CDB’s? Someone will just have to do some trial-and-error to find out! (I’ll post if I have a chance to do this.)

Workaround

I simply can’t take my previous strategy of using a ratio-based plan where each application has its own consumer group. The next-best approach that I’ve come up with so far is to have a general app group and another (lower priority) group for “abusive sessions” that run for hours or days at 100% CPU. I can’t use the built-in automatic switching because all of these sessions are coming from connection pools. So I’m thinking that initially I would manually move sessions into and out of this group with perhaps some pl/sql and dbms_scheduler around it in the future.

About these ads

About Jeremy

Doing stuff with Oracle Database, Performance, Clusters, Linux. about.me/jeremy_schneider

Discussion

One thought on “Resource Manager Limits (ORA-29376)

  1. Hi Jeremy,

    Thanks for really useful information. Follows 12c limit for consumer groups

    12c Pluggable database
    ========================

    maximum number of consumer group is 8

    As per documentation

    http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_dbrm.htm#ADMIN13774

    In a CDB, the following restrictions apply to PDB resource plans:

    A PDB resource plan cannot have subplans.
    A PDB resource plan can have a maximum of eight consumer groups.
    A PDB resource plan cannot have a multiple-level scheduling policy.

    Testcase (PDBDDB)

    ERROR at line 1:
    ORA-29382: validation of pending area failed
    ORA-29376: number of consumer groups 20 in plan TEST_PLAN exceeds 8
    ORA-06512: at “SYS.DBMS_RMIN_SYS”, line 3549

    12c non-pluggable database :
    =============================

    Maximum limit consumer group is 28 which is mentioned in documentation

    http://docs.oracle.com/cd/E16655_01/server.121/e17636/dbrm.htm#ADMIN027

    Testcase
    ———–
    ERROR at line 1:
    ORA-29382: validation of pending area failed
    ORA-29376: number of consumer groups 29 in plan ORACLE_PLAN exceeds 28
    ORA-29384: number of consumer groups and subplans for plan ORACLE_PLAN exceeds
    28

    Posted by Sayed | September 27, 2013, 12:54 am

Disclaimer

(a) This is my personal blog.

The views expressed on this website are mine alone and do not necessarily reflect the views of my employer.

racattack-iconOakTableLogo-small

about.me

Jeremy Schneider
Follow

Get every new post delivered to your Inbox.

Join 925 other followers

%d bloggers like this: