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

Home » Articles » 12c » Here

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.

Related articles

PDB Memory Parameters

The following parameters can be set at the PDB level.

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

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.

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;

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