buffer_pool_keep

[steeve123]

hi, i need a select that gives the % of memory left in my buffer_pool_keep.

[sanrak]

Hi

which version of Oracle are u using.

If you are using 8.0.5 then following will give blocks used By keepd objects

select
'KEEP' POOL, o.name, count(buf#) BLOCKS
from obj$ o, x$bh x
where o.dataobj# = x.obj
and x.state !=0
and o.owner# !=0
and buf# >= (select lo_bnum from v$buffer_pool where name='KEEP' and buffers > 0)
and buf# <= (select hi_bnum from v$buffer_pool where name='KEEP' and buffers > 0)
group by 'KEEP',o.name
/

This query will not work in 8i becuase in 8i the lo_bnum and hi_bnum columns in v$buffer_pool are not used and set to 0.

A workaround is create a new view like the one given below and replace the v$buffer_pool view name with this and re-execute the previous query.

create or replace view v$buffer_pool_lo_hi_bnum as
select name ,
min(START_BUF#) lo_bnum,
max(END_BUF#) hi_bnum,
max(END_BUF#)-min(START_BUF#) buffers
from v$buffer_pool v, x$kcbwds s
where (LO_SETID=SET_ID or HI_SETID=SET_ID) and buffers > 0
group by name

This should work . The v$buffer_pool_statistics view is based on x$kcbwds

HTH
Santosh

[sanrak]

Hi

A small correction

Use the following query after creating the view

select o.name, COUNT(buf#) BLOCKS
from obj$ o, x$bh x
where o.dataobj# = x.obj
and x.state != 0
and o.owner# !=0
and buf# >= (select lo_bnum from v$buffer_pool_lo_hi_bnum where
name = 'KEEP' and buffers > 0)
and buf# <= (select hi_bnum from v$buffer_pool_lo_hi_bnum where
name = 'KEEP' and buffers > 0)
group by o.name

IF you are 8.0.5 user v$buffer_pool instead of the view

Sorry for the mistake.

HTH
Santosh