Skip to content

Memory Management 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.
    • 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 ws 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;