Author Archives: SandeepSingh DBA

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

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;