|
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 9008M
db_keep_cache_size 16G
db_recycle_cache_size 8G
The following script shows how much of each pool is used
SELECT kcbwbpd.bp_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 USED_MB
-------------------- ----------
DEFAULT 8919.56
RECYCLE 1910.28
KEEP 8022.31
Where :block_size is you block size, for example 8192
Other links
SGA pools size
Buffer cache loaded objects
Buffer cache Flush in Oracle9i
|
