Re: Identify to which buffer pool a entry in v$bh belongs?

Things may have changed over the years, but there's a note of mine from 2006 which explains some of the details, and has a hacky little script to report some details.

Oracle has "working data sets" (x$kcbwds) - which correspond approximately to LRUs - and "buffer pool definitions/descriptions" (x$kcbbpd). There is no direct (parent/child) link between the two, but a buffer pool consists of a consecutive set of working data sets so it is possible to do a join through the min and max set ids from the bpds.

Things you will find: an object in the KEEP pool will almost certainly report a large number of buffers used in the DEFAULT or RECYCLE pools because Oracle usually creates read-consistent copies of blocks out of the KEEP pool (preferably in the RECYCLE pool, if it exists)

Regards
Jonathan Lewis

On Sat, 20 Jan 2024 at 08:18, Martin Berger <martin.a.berger_at_gmail.com> wrote:

> Dear friends,
>
> I was in a discussion about DEFAULT and KEEP pool.
> A small side question was about counting current buffered blocks for given
> objects, and in which pool they "really" are. The idea was: direct after I
> define a table BUFFER_POOL KEEP (away from previous default), can I somehow
> track if/when this table is using only the keep pool ?
> The discussion went on and I was not forced to go into details, but it
> showed my lack of detailed knowledge in this area.
> Any information - might it be conceptual information or queries to map
> x$bh to pools directly - is very appreciated!
>
>
> thank you,
> Martin
>
> --
> Martin Berger
> Oracle ♠
> martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
> ^∆x http://berxblog.blogspot.com
>

--
http://www.freelists.org/webpage/oracle-l