Monitoring Buffer Pool size usage

Checked the advisory for Buffer Pool Size:
Monitoring  Buffer Pool size usage

set pagesize 1000
select size_for_estimate c1, buffers_for_estimate c2, estd_physical_read_factor c3,
estd_physical_reads c4
from v$db_cache_advice
where name = 'DEFAULT'
and block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
and advice_status = 'ON';

C1 C2 C3 C4
---------- ---------- ---------- ----------
832 98254 4.8586 1857233
1664 196508 2.1248 812217
2496 294762 1.5018 574072
3328 393016 1.2198 466260
4160 491270 1.0807 413095
4992 589524 1.0107 386358
5824 687778 1 382256
6656 786032 1 382256
7488 884286 1 382256
8320 982540 1 382256
8448 997656 1 382256
9152 1080794 1 382256
9984 1179048 1 382256
10816 1277302 1 382256
11648 1375556 1 382256
12480 1473810 1 382256
13312 1572064 1 382256
14144 1670318 1 382256
14976 1768572 1 382256
15808 1866826 1 382256
16640 1965080 1 382256

Checked the usage of Buffer Pool:

SELECT DECODE(kcbwbpd.bp_name, 'DEFAULT', 'db_cache_size', 'RECYCLE',
'db_recycle_cache_size', 'KEEP', 'db_keep_cache_size')
buffer_pool_name, ROUND((COUNT(*)*8192)/(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 7651.7

Checked the buffer cache loaded Object:

SELECT BUFF_POOL.NAME POOL, D.OWNER, O.NAME OBJECT, D.OBJECT_TYPE, SUM(CT) BLOCKS
FROM (SELECT SET_DS, OBJ, COUNT(*) CT FROM X$BH GROUP BY SET_DS, OBJ) BH, OBJ$ O,X$KCBWDS
KCBW,V$BUFFER_POOL BUFF_POOL, DBA_OBJECTS D
WHERE O.DATAOBJ# = BH.OBJ AND D.OBJECT_ID = O.DATAOBJ#
AND O.OWNER# > 0 AND BH.SET_DS = KCBW.ADDR
AND KCBW.SET_ID BETWEEN BUFF_POOL.LO_SETID AND
BUFF_POOL.HI_SETID AND BUFF_POOL.BUFFERS != 0
AND D.OWNER <> 'SYSTEM'
GROUP BY BUFF_POOL.NAME, D.OWNER, O.NAME, D.OBJECT_TYPE, O.SUBNAME
ORDER BY BUFF_POOL.NAME, D.OWNER, O.NAME, D.OBJECT_TYPE, O.SUBNAME;

http://dbaworks-sunny.blogspot.in/2012/10/buffer-pool-size-estimate-with-advisory.html

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.