12c SGA memory distribution by PDBs

With 12c multi-tenant architecture, you could have  several Pluggable database in one Container Database(CBD).

The consolidated architecture is more efficient in using the server's CPU and memory among PDBs.

One question that arises is " can you tell what percent of total  buffers is used up by each PDBs ? "

Below script can tell you just that. You will have to run this at CDB level in your database.

 col PCT_BARCHART format a30

 set linesize 200

 col name format a10

 col con_id format 99

 col PCT format 999

    SELECT NVL(pdb.name,'CDB') name,b.con_id,

  ROUND(b.subtotal *

  (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size'

  )                        /(1024 *1024)) bufferCache_MB,

  ROUND(b.subtotal         /b.total * 100) PCT,

  rpad('*',ROUND(b.subtotal/b.total * 100),'*') PCT_BARCHART

FROM

  ( SELECT DISTINCT a.*

  FROM

    (SELECT con_id,

      COUNT(*) over (partition BY con_id ) subtotal ,

      COUNT(*) over () total

    FROM sys.gv$bh

    ) a

  )b, v$pdbs pdb

  where b.con_id=pdb.con_id(+)

ORDER BY con_id;

output.

NAME      CON_ID BUFFERCACHE_MB  PCT PCT_BARCHART

---------- ------ -------------- ---- ------------------------------

CDB 1              131   28 ****************************

PDB$SEED 2               14    3 ***

PDB1 3               18    4 ****

PDB2 4               89   19 *******************

PDB_2 5               41    9 *********

PDB_1 6               41    9 *********

PDB_4 7               39    8 ********

PDB_3 8               43    9 *********

PDB_5 9               44    9 *********

9 rows selected.

 SQL>