Search

Top 60 Oracle Blogs

Recent comments

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.