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

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;

Potrebbero interessarti anche...