Friday, September 13, 2013

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> 

No comments:

Post a Comment

Feedback welcome