Buffer Pool used
                                                 Last update (2011-04-14 13:55:26)
                                                                                                                   Date added (2009-04-08 10:02:01)

Summary
You know that you can define the size of each buffer pool in the buffer_cache as you like, but how much of them is used? Use this script first to find how buffer pools are set.
SELECT SUBSTR(NAME, 0, 22) NAME, SUBSTR(display_value, 0,10) VALUE
FROM v$parameter 
WHERE NAME IN ('db_keep_cache_size', 'db_recycle_cache_size', 'db_cache_size');

NAME                   VALUE
---------------------- ----------
db_cache_size          30G
db_keep_cache_size     1G
db_recycle_cache_size  1G
The following script shows how much of each pool is used
SELECT DECODE(kcbwbpd.bp_name, 'DEFAULT', 'db_cache_size', 'RECYCLE', 
'db_recycle_cache_size', 'KEEP', 'db_keep_cache_size') 
buffer_pool_name, ROUND((COUNT(*)*:block_size)/(1024*1024),2) USED_MB 
FROM x$kcbwds kcbwds, x$kcbwbpd kcbwbpd , x$bh bh 
WHERE kcbwds.set_id >= kcbwbpd.bp_lo_sid 
AND kcbwds.set_id <= kcbwbpd.bp_hi_sid 
AND kcbwbpd.bp_size != 0 
AND kcbwds.addr = bh.set_ds 
AND bh.state !=0 
GROUP BY kcbwbpd.bp_name;

BUFFER_POOL_NAME         USED_MB
--------------------- ----------
db_cache_size           30358.94
db_recycle_cache_size       2.66
db_keep_cache_size          7.09
Where :block_size is you block size, for example 8192

Other links
SGA pools size
Buffer cache loaded objects
Buffer cache Flush in Oracle9i
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search