Buffer Cache free shows two different values
Date: 14 May 2004 09:42:53 -0700
Message-ID: <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
Received on Fri May 14 2004 - 18:42:53 CEST