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.
Create a resource manager plan to guarantee fair scheduling between a large number of applications on a heavily consolidated 22.214.171.124 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.
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.
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.)
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.