Re: Help with query to find buffer pool usage

do this

select kcbwbpd.bp_name, o.name, count(*) BLOCKS from sys.x$kcbwds kcbwds, sys.x$kcbwbpd kcbwbpd , sys.x$bh bh, sys.obj$ o where kcbwds.set_id >= kcbwbpd.bp_lo_sid and kcbwds.set_id <= kcbwbpd.bp_hi_sid
and kcbwbpd.bp_size != 0
and bh.indx between start_buf# and end_buf# and o.dataobj# = bh.obj
and bh.state !=0
and o.owner# !=0
and bp_name = 'KEEP'
group by kcbwbpd.bp_name, o.name

thanks
"Richard Piasecki" <ogo_at_mailcity.com> wrote in message news:n582cu84g3gc1cbnu7rg0nv7ainn3ol61k_at_4ax.com...
>
> Greetings.
>
> I am currently using the following query to find the
> objects that are cached in the block buffer pool.
>
> select a.status, b.object_name, count(*) total
> from v$bh a, dba_objects b
> where a.objd = b.object_id(+)
> group by a.status, b.object_name;
>
> It works fine. But, I'd really like to include the pool
> in the output (keep, recycle, default). However, there
> doesn't seem to be a field in v$bh (or even x$bh) that
> corresponds to the pool.
>
> Does anyone know how to get this information? I assume
> I need to join with another table. Which one is it?
>
>
> --- Rich
>
Received on Sat Apr 20 2002 - 17:39:44 CDT