>
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 Schneider

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

Leave a New Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Disclaimer

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

about.me

Jeremy Schneider
Follow

Get every new post delivered to your Inbox.

Join 808 other followers

%d bloggers like this: