Re: Buffer Cache free shows two different values

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 Received on Mon May 17 2004 - 10:00:24 CEST