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

Home -> Community -> Usenet -> c.d.o.server -> Re: Cache Hit Ratio from system views

Re: Cache Hit Ratio from system views

From: <fitzjarrell_at_cox.net>
Date: Tue, 28 Aug 2007 13:01:45 -0700
Message-ID: <1188331305.449309.34890@k79g2000hse.googlegroups.com>


On Aug 28, 1:56 pm, "Bob Jones" <em..._at_me.not> wrote:
> "Richard Foote" <richard.fo..._at_nospam.bigpond.com> wrote in message
>
> news:seAAi.26732$4A1.22707_at_news-server.bigpond.net.au...
>
>
>
>
>
> > "Bob Jones" <em..._at_me.not> wrote in message
> >news:mOnAi.236$ZA5.16_at_nlpi068.nbdc.sbc.com...
>
> >> "Richard Foote" <richard.fo..._at_nospam.bigpond.com> wrote in message
> >>news:2_Wyi.24466$4A1.1328_at_news-server.bigpond.net.au...
>
> >>> "Bob Jones" <em..._at_me.not> wrote in message
> >>>news:kOtyi.50198$YL5.8637_at_newssvr29.news.prodigy.net...
>
> >>>> High BCHR is always better than low - provided everything else being
> >>>> equal. If BCHR is useless for the stated reasons, no other indicator
> >>>> would be useful.
>
> >>> This I'm afraid is where you're fundamentally incorrect.
>
> >>> A high BCHR can mean your database is on life support, struggling to
> >>> cope with exessive LIOs due to inefficient SQL with users staring at an
> >>> hourglass rather than returned data.
>
> >>> A BCHR that has increased can mean your database has suddenly hit
> >>> significant performance issues. Or it can mean things have improved. Or
> >>> it can mean response times remain unaffected.
>
> >>> A BCHR that has reduced can mean your database has suddenly hit
> >>> significant performance issues. Or it can mean things have improved
> >>> (yes, improved because that crippling transaction that was previously
> >>> performing poorly due to massively exessive LIOs has been fixed,
> >>> reducing the overall BCHR) . Or it can mean response times remain
> >>> unaffected.
>
> >>> Not much of an indicator is it ?
>
> >>> But saying that a BCHR is *always* better than a low is just plain wrong
> >>> wrong wrong ...
>
> >> Didn't I repeatedly say "provided everything else being equal"?
>
> > And how precisely do you determine that everything else indeed is equal ?
> > Most databases don't exactly remain equal ...
>
> No, they do not. That's why you do not look at BCHR alone, as I have said
> before.
>
> > And when precisely do you check if everything else is equal with this
> > "very meaningful indicator" of yours ? When the BCHR increases ? When the
> > BCHR decreases ? When the BCHR remains the same ?
>
> Try asking yourself the same questions about any other indicators you
> consider meaningful. The question here is not how to determine if everything
> else is equal. It is about whether BCHR means anything if everything else is
> equal.- Hide quoted text -
>
> - Show quoted text -

And the answer is 'probably not', an answer you choose to not accept. A high BCHR means ... not much in the real world as it could 'indicate' any number of things ranging from the good (the buffer cache is sized properly and is being used efficiently) to the bad (block contention, improperly sized undo tablespace, lots of data in the cache placed there by dismal SQL statements in dire need of tuning, response times shooting through the roof and end-users waiting interminably for results to appear). Simply because you have a 99% BCHR doesn't prove your database is functioning optimally. And this is what you choose to ignore. Your 'everything else is equal' noise means what, exactly? You've never explained that statement, and I don't expect you to do so any time in the near future as I expect you can't.

'All things being equal' means nothing in this context. Oracle isn't a cracker factory. Possibly you should think about not treating it as such.

David Fitzjarrell Received on Tue Aug 28 2007 - 15:01:45 CDT

Original text of this message

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