RE: Buffer Hit ratios - Oracle still hasn't got it
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <p.mclarty@xxxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 26 Apr 2007 05:58:56 -0400
At least it says "investigate" rather than making a blanket recommendation
to always increase it. It is in fact possible to have buffer cache set way
too low, so aside from the comment about tuning to avoid performing full
table scans as if that is always a good idea (which may or may not be a good
idea depending on your data and application) I cannot disagree with the
notion that investigation of the buffer cache size is worth doing if the hit
ratio is very low. Of course this is very different from saying that your
database is not well configured if you have a low cache hit ratio or that
the cache should be unconditionally raised if the ratio is low. Several of
us over the years have presented scripts to set your cache hit ratio to a
desired number, demonstrating that it is certainly not a universal metric,
and it is certainly not the first place to look for resolution of an
individual problem, unless of course the trace showed substantial waits for
free buffers, the query in question is not wasteful, and the query needed to
re-read blocks many times to resolve relational references.
You can run a simple test: Set the buffer cache very low and run something
that needs to re-read a lot of buffers that will no longer fit in the buffer
cache.
This is very different from taking on the order of ten years to add the
adjective "minimum" before the words "recommended settings" to the kernel
settings table for Unix systems running Oracle.
Since the days when heated arguments over cost took place to get a measly 5
or 10 meg of buffer cache are in the ancient past and most folks start out
with a generous buffer cache size to take it out of the equation, the
recommendation may seem a bit silly. On the other hand, you can still put
your whole system in a world of hurt by setting it much too low.
But you are right. They should add a bit there to explain that it is
probably not the problem unless it is pathologically low and give some
guidance on how to establish that the buffer cache is in a reasonable range
considering the trade offs.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Peter McLarty
Sent: Thursday, April 26, 2007 1:30 AM
To: oracle-l
Subject: Buffer Hit ratios - Oracle still hasn't got it
I was just reading the 10.2 Performance Tuning Guide and found this little
gem. I thought it should have been written out by now
"7.2.3.1 Increasing Memory Allocated to the Buffer Cache
As a general rule, investigate increasing the size of the cache if the cache
hit ratio is low and your application has been tuned to avoid performing
full table scans."
Clearly no one in Oracle has updated the Tech Writers for some time
Cheers
--
Peter McLarty
Database Administrator
Student System Upgrade Project
Central Queensland University
Email: p.mclarty@xxxxxxxxxx
Phone: 07 4923 2876
Fax: 07 4923 2721
Mobile: 04 0209 4238
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
