Memory Resource Management for PDBs in Oracle Database 12c Rel.2 (12.2)

In the previous release there was no way to control the amount of memory used by
an individual PDB. It could use up lots of memory and impact the performance of
other PDBs in the same instance.
Oracle Database 12c Rel.2 (12.2) allows to control the amount of memory used by a
PDB.
PDB Memory Parameters
The following parameters can be set at the PDB level.

• DB_CACHE_SIZE : minimum buffer cache size for the PDB.
• SHARED_POOL_SIZE : minimum shared pool size for the PDB.
• PGA_AGGREGATE_LIMIT : maximum PGA size for the PDB.
• PGA_AGGREGATE_TARGET : target PGA size for the PDB.
• SGA_MIN_SIZE : minimum SGA size for the PDB.
• SGA_TARGET : maximum SGA size for the PDB.

Setting PDB Memory Parameters
The process of setting memory parameters for a PDB is similar to setting regular
instance parameters. The example below uses the SGA_TARGET parameter:
check the current settings for the root container.
CONN / AS SYSDBA
SHOW PARAMETER sga_target;
NAME TYPE VALUE
— — — — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — — — — — — —
sga_target big integer 2000M
SQL>
Check the current settings for the pluggable database.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
SHOW PARAMETER sga_target;
NAME TYPE VALUE
— — — — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — — — — — — —
sga_target big integer 0
SQL>
Set the SGA_TARGET for the current PDB.
SQL> ALTER SYSTEM SET sga_target=1G SCOPE=BOTH;
System altered.
SQL> SHOW PARAMETER sga_target;
NAME TYPE VALUE
— — — — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — — — — — — —
sga_target big integer 1G
SQL>
Monitoring Memory Usage for PDBs
Oracle now provides views to monitor the resource (CPU, I/O, parallel execution,
memory) usage of PDBs. Each view contains similar information, but for different
retention periods.

• V$RSRCPDBMETRIC : A single row per PDB, holding the last of the 1 minute
  samples.

• V$RSRCPDBMETRIC_HISTORY : 61 rows per PDB, holding the last 60 minutes
  worth of samples from the V$RSRCPDBMETRIC view.

• DBA_HIST_RSRC_PDB_METRIC : AWR snaphots, retained based on the AWR
  retention period.

The following queries are examples of their usage.
— Last sample per PDB.
SELECT r.con_id,
p.pdb_name,
r.begin_time,
r.end_time,
r.sga_bytes,
r.pga_bytes,
r.buffer_cache_bytes,
r.shared_pool_bytes
FROM v$rsrcpdbmetric r,
cdb_pdbs p
WHERE r.con_id = p.con_id
ORDER BY p.pdb_name;