Multitenant : Memory Resource Management for PDBs in Oracle Database 12c Release 2 (12.2)

 

PDB Memory Parameters

The following parameters can be set at the PDB level.

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

There are a number of restrictions regarding what values can be used, which are explained in the documentation here. To summarise.

• The NONCDB_COMPATIBLE parameter is set to FALSE in the root container.
• The MEMORY_TARGET parameter is unset or set to "0" in the root container.
• The individual parameters have a variety of maximum limits to prevent you from over-allocating memory within the PDB and the instance generally. If you attempt to set an incorrect value an error will be produced.

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, but the approach is similar for the other parameters.

Check the current settings for the root container.

CONN / AS SYSDBA
SHOW PARAMETER sga_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 2544M
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>

Attempt to make the SGA_TARGET too big compared to the value in the root container.

SQL> ALTER SYSTEM SET sga_target=3G SCOPE=BOTH;
ALTER SYSTEM SET sga_target=3G SCOPE=BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-56747: invalid value 3221225472 for parameter sga_target; must be smaller
than parameter sga_target of the root container

SQL>

The value can be set to "0" or reset if you no longer want to control this parameter.

ALTER SYSTEM SET sga_target=0 SCOPE=BOTH;
ALTER SYSTEM RESET sga_target;

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.
• V$RSRC_PDB : Cumulative statistics since the CDB resource plan was set.
• DBA_HIST_RSRC_PDB_METRIC : AWR snaphots, retained based on the AWR retention period. Access to the AWR views require additional licensing, so be warned.

The following queries are examples of their usage.

CONN / AS SYSDBA

SET LINESIZE 150
COLUMN pdb_name FORMAT A10
COLUMN begin_time FORMAT A26
COLUMN end_time FORMAT A26
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF'; 

-- 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;

-- Last hours samples for PDB1
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_history r,
       cdb_pdbs p
WHERE  r.con_id = p.con_id
AND    p.pdb_name = 'PDB1'
ORDER BY r.begin_time;

-- Check you are licensed to do this before trying!
-- All AWR snapshot information for PDB1.
SELECT r.snap_id,
       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   dba_hist_rsrc_pdb_metric r,
       cdb_pdbs p
WHERE  r.con_id = p.con_id
AND    p.pdb_name = 'PDB1'
ORDER BY r.begin_time;