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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.