Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Buffer Hit ratios - Oracle still hasn't got it

RE: Buffer Hit ratios - Oracle still hasn't got it

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 26 Apr 2007 05:58:56 -0400
Message-ID: <018401c787e9$82112580$1100a8c0@rsiz.com>


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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_cqu.edu.au
 Phone:  07 4923 2876
   Fax:  07 4923 2721

Mobile:  04 0209 4238  

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 26 2007 - 04:58:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US