Buffer Cache free shows two different values

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 Received on Fri May 14 2004 - 11:42:53 CDT