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.

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.