Memory Resource Management for PDBs in Oracle Database 12c Rel.2 (12.2)

Memory Resource Management for PDBs in Oracle Database 12c Rel.2 (12.2)
  • 13
    Giu

Memory Resource Management for PDBs in Oracle Database 12c Rel.2 (12.2)

twittergoogle_pluslinkedin

Nelle versioni precedenti non era modo per controllare la quantità di memoria

utilizzata da un singolo PPB. Potrebbe utilizzare molta memoria e l’impatto sulle

prestazioni degli altri PDB all’interno della stessa istanza.

Oracle 12c Rel.2 (12.2) permette di controllare la quantità di memoria utilizzata da un PPB.

PDB Memory Parameters

I seguenti parametri possono essere impostati a livello PDB :

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

Setting PDB Memory Parameters

Il settaggio dei parametri di memoria e’ identico a quello del settaggio dei parametri di una istanza. L’esempio seguente utilizza il parametro di SGA_TARGET.

Controlliamo l’impostazione del parametro del root container :

CONN / AS SYSDBA

SHOW PARAMETER sga_target;

NAME TYPE VALUE

— — — — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — — — — — — —

sga_target big integer 2000M

SQL>

Controlliamo l’impostazione del pluggable database:

CONN / AS SYSDBA

ALTER SESSION SET CONTAINER=pdb1;

SHOW PARAMETER sga_target;

NAME TYPE VALUE

— — — — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — — — — — — —

sga_target big integer 0

SQL>

Impostiamo il parametro SGA_YARGET per il PDB corrente:

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>

Monitoring Memory Usage for PDBs

Oracle ora rende disponibili viste per controllare le risorse (CPU, I/O, parallel

execution, memory) in uso al PDB.

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

  • DBA_HIST_RSRC_PDB_METRIC : AWR snaphots, retained based on the AWR

    retention period.

  • L’interrogazione che segue e’ un esempio del loro utilizzo:

    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;