Some bad performance pdbs may result in disaster for the whole db machine, in order to avoid this situation we need to limit the pdb resource usage!
There's tens of pdbs in our 12cR2, each one requires DIFFERENT resources, we have to limit them with DIFFERENT configurations.
1) As we known, we could assign the SGA_TARGET, but how could we change the sga_max_size for SOME SPECIFIC pdb?
We've fired the 'ALTER SYSTEM SET SGA_MAX_SIZE=... SCOPE=SPFILE' clause, but met the ORA-65040 error.
SQL> alter session set container=n2dev;
SQL> ALTER SYSTEM SET SGA_MAX_SIZE=2G container=CURRENT scope=spfile;
ALTER SYSTEM SET SGA_MAX_SIZE=2G container=current scope=spfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
2) Also we wanna assign the CPU upper limit for some pdbs, will the CPU_COUNT parameter work or shall we consider CDB Resource Manager Plans? CPU_COUNT seems to be much convenient.
3) by the way, we've both set the max_iops and max_mbps to limit the IO of PDB, would these 2 params takes effect on UNDO tbs?
Thanks!
In a container, SGA_TARGET will be in effect synonymous with SGA_MAX_SIZE, in that, it restricts the pluggable database to consume that amount of memory.
Similarly, cpu_count at the pluggable level works in a similar way to instance caging at the database level, we will use resource manager to throttle the instance (in this case, the pluggable) to use that much worth of CPU, eg
SQL> conn / as sysdba
Connected.
SQL> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ ----------- ----------
cpu_count integer 16
SQL>
SQL>
SQL> alter session set container = pdb122a;
Session altered.
SQL> alter system set cpu_count = 4;
System altered.
Check this whitepaper for details
https://www.oracle.com/technetwork/database/multitenant/overview/multitenant-wp-12c-2078248.pdf