Check the memory usage for PDB database in Oracle

Monitor and manage the Memory Usage of PDBs databases in Oracle.

Check the memory usage of PDB databases

COLUMN PDB_NAME FORMAT A10

SELECT r.CON_ID, p.PDB_NAME, 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;

Check the history usage of PDB databases

select * from V$RSRCPDBMETRIC_HISTORY;

OR

SELECT r.CON_ID, p.PDB_NAME, 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;

Change or alter memory parameter in PDBs:

Best way is to limit the sum of the SGA_MIN_SIZE values for all PDBs to 50% or less of the SGA size of the CDB.

General recommendations for setting SGA_MIN_SIZE (minimum guaranteed SGA) parameter is:

• It must be less than or equal to 50% of the setting for the SGA_TARGET in the CDB root.
• It must be less than or equal to 50% of the setting for the SGA_TARGET in the PDB.
• The sum of the SGA_MIN_SIZE settings for all PDBs must be less than or equal to 50% of the setting for the SGA_TARGET in the CDB root.

Note: Both SGA_TARGET and SGA_MIN_SIZE setting in the PDB is enforced only if the SGA_TARGET initialization parameter is set to a non-zero value in the CDB root.

alter session set container=PDB1;

ALTER SYSTEM SET SGA_TARGET = 1500M SCOPE = BOTH;

ALTER SYSTEM SET SGA_MIN_SIZE = 500M SCOPE = BOTH;