buffer_pool_keep
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: buffer_pool_keep

  1. #1
    Join Date
    Nov 2000
    Posts
    440
    hi, i need a select that gives the % of memory left in my buffer_pool_keep.

  2. #2
    Join Date
    Nov 2000
    Posts
    25
    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

  3. #3
    Join Date
    Nov 2000
    Posts
    25
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width