Re: Buffer Cache free shows two different values

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0405170003.53f46a31_at_posting.google.com>...
> steviehaston_at_hotmail.com (stevie) wrote in message news:<4092f6be.0405140842.30f9ba2b_at_posting.google.com>...
> > Hello
> > I'm currently trying to understand how to tune an oracle database.
> >
> > After tuning SQL, I have been looking at trying to retain as much of
> > the data blocks in memory as possible, to reduce physical reads.
> > Obviously this needs to be balanced with other aspects like the shared
> > pool and redo log buffer.
> >
> > My question is how do I calculate the amount of the Buffer Cache which
> > is actually in use.
> >
> > The following SQL is a straight query over x$bh - I get 75,979 blocks
> >
> > select count(*) from x$bh where state <> 0
> >
> > whereas inner joining to objects gives 10,731 blocks
> >
> > select sum(buffer_size) from
> > (SELECT o.owner, object_name, object_type, count(1) buffer_size
> > FROM SYS.x$bh, dba_objects o
> > WHERE
> > obj = o.object_id
> > GROUP BY o.owner, object_name, object_type)
> >
> > Any ideas?
> > TIA
> > Stevie
>
> Your join column in dba_objects is incorrect. It should be
> data_object_id instead of object_id
>
> Sybrand Bakker
> Senior Oracle DBA

Thanks for your help.

Using data_object_id now gives 67035 blocks vs 65038 for the state <> 0 SQL. What could cause this discrepancy?

Stevie Received on Wed May 19 2004 - 17:22:26 CEST