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;