Re: Cache Hit Ratio from system views

On Aug 21, 2:21 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Bob Jones wrote:
> > If that's the case, we don't really need to care about any indicator. Your
> > argument is basically the same as others here. Please read my earlier
> > postings.
>
> You seem to really invest yourself more fully in having an argument than
> you do in considering that the other person, in this case a member of
> the Oak Table, might actually know what he's talking about.
>
> Expert after expert in the Oracle database is telling you that you are
> wrong. And all you want to do is argue.
>
> No metrics to support you.
> No evidence of having read and understand what you are responding to.
> Just synapse-free mindless argument for the sake of argument.
> To what end?
> The hole you are standing in is already well over your head.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

This is an interesting topic. I was prepared to state that a change in the buffer cache hit ratio might mean *something*. After a couple days thinking about it, I thought I remembered reading an article that stated that a decrease in the buffer cache hit ratio might actually be a positive change... "Oracle Insights: Tales of the Oak Table" is an interesting book. One suggestion in the book, in a section written by Cary Millsap, suggested that fixing a poorly performing SQL statement may/will likely cause a decrease in the buffer cache hit ratio, while improving performance. If you have the book, read pages 159-161. That section of the book is also available online:   http://www.apress.com/ApressCorporate/supplement/1/314/1590593871-2036.pdf

There is a more detailed write up by the same author, which can be found here:
  http://www.oradream.com/pdf/Why%20a%2099%20Cahe%20Hit%20Ratio%20is%20Not%20OK.pdf

A brief write up by Gaja Krishna Vaidyanatha:   http://www.quest-pipelines.com/newsletter-v3/0302_F.htm

A write up by Jonathan Lewis in 2001:
  http://www.jlcomp.demon.co.uk/myths.html

Tom Kyte briefly mentioned the ineffectiveness of tuning based on the buffer cache hi ratio in his "Expert Oracle Database Architecture" book on page xii in the Forward. That section is available online here:
  http://asktom.oracle.com/pls/ask/z?p_url=download_file%3Fp_file%3D3962675907440142460&p_cat=5300FMcmp1.pdf&p_company=10

Just some additional food for thought in addition to what has already been mentioned in this thread. Personal experience: when the previous DBA left here 5+ years ago, the database was reporting a 99.9%+ buffer cache hit ratio (this was the primary tuning statistic in use), yet performance was poor. When that DBA left, I threw out Toad and the buffer cache hit ratio and switched to the method suggested by Gaja Krishna Vaidyanatha in his performance tuning book. The results were impressive. It is nice to look at metrics, but it is often better to find ways to fix problems that affect the critical business applications - this is one of the things stressed in Cary Millsap's book.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Aug 21 2007 - 21:27:02 CDT