Thursday, 25 May 2017

Multitenant : Memory Resource Management for PDBs in Oracle Database 12c Release 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.

  • PDB Memory Parameters


The following parameters can be set at the PDB level.
  1. DB_CACHE_SIZE : The minimum buffer cache size for the PDB.
  2. SHARED_POOL_SIZE : The minimum shared pool size for the PDB.
  3. PGA_AGGREGATE_LIMIT : The maximum PGA size for the PDB.
  4. PGA_AGGREGATE_TARGET : The target PGA size for the PDB.
  5. SGA_MIN_SIZE : The minimum SGA size for the PDB.
  6. 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.
  1. The NONCDB_COMPATIBLE parameter is set to FALSE in the root container.
  2. The MEMORY_TARGET parameter is unset or set to "0" in the root container.
  3. 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.
  1. V$RSRCPDBMETRIC : A single row per PDB, holding the last of the 1 minute samples.
  2. V$RSRCPDBMETRIC_HISTORY : 61 rows per PDB, holding the last 60 minutes worth of samples from the V$RSRCPDBMETRIC view.
  3. DBA_HIST_RSRC_PDB_METRIC : AWR snaphots, retained based on the AWR retention period.
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;