8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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

In the previous release there was no way to control the amount of memory used by an individual PDB. As a result a "noisy neighbour" could use up lots of memory and impact the performance of other PDBs in the same instance. Oracle Database 12c Release 2 (12.2) allows you to control the amount of memory used by a PDB, making consolidation more reliable.

If you are running a lone-PDB stetup there is no point using these settings as you want the PDB to use all the memory assigned to the instance.

PDB Memory Parameters
Setting PDB Memory Parameters
Monitoring Memory Usage for PDBs

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.

Despite this, according to the following MOS note the only SGA memory sizing parameter that Oracle recommends setting at the PDB level is SGA_TARGET.

ORA-04031 on Multitenant Database with Excessive Amounts of KGLH0 and / or SQLA Memory and Parameter SHARED_POOL_SIZE or SGA_MIN_SIZE Set at the PDB Level (Doc ID 2590172.1)

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;

The PDB memory settings aren't written to the SPFILE, like those of the CDB. Instead they are stored in the PDB_SPFILE$ table. You can query it using the following query from the root container.

set linesize 120
column pdb_name format a10
column name format a30
column value$ format a30

select ps.db_uniq_name,
       ps.pdb_uid,
       p.name as pdb_name,
       ps.name,
       ps.value$
from   pdb_spfile$ ps
       join v$pdbs p on ps.pdb_uid = p.con_uid
order by 1, 2, 3;

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;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.