Re: Cache Hit Ratio from system views

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 21 Aug 2007 14:11:51 -0700
Message-ID: <1187730710.209797@bubbleator.drizzle.com>

Steve Howard wrote:
> On Aug 21, 2:18 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> Bob Jones wrote:
>>> High BCHR is always better than low - provided everything else being equal.
>> Nonsense. Sorry but this is total mythological nonsense. A high BCHR may
>> be an indicator of nothing more than you write really lousy code.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> Correct, but he has consistently stated that "all other things
> equal...". In other words, let's assume that you have read every book
> by Jonathan Lewis, Tom Kyte, all the Oracle documentation (I know that
> is a sore spot for some people), ensured your hardware is properly
> configured, fill in whatever else you want...
>
> If your BCHR is 50% on one system and 99% on another (once again,
> identically coded, hardware, etc.)...
>
> is that not an indication that your DBA *may* have forgotten a couple
> of zeroes for db_cache_size parameter?
>
> I am truly curious, as this should be a yes or no answer. If the
> answer is anything other than no, then it does have *some* value.

I would say that in and of itself it would not be.

It would be an indicator that something is different but it might just be the usage pattern.

If I find something in the cache it is an indicator that it was put there previously and not aged out. Did it not age out because the cache is too large hurting performance? Possibly. Is it not there because the system is not using bind variables or because system usage dictates that similar queries are not being run? Perhaps it is a matter of cursor sharing and has nothing to do with the cache size.

In other words it is a number. One of many numbers. And considered with other metrics may lead a good DBA to narrow their search. But a larger number is not indicative of anything by itself.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Aug 21 2007 - 16:11:51 CDT