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 14:29:22 -0700
Message-ID: <1188336562.896245.105300@r34g2000hsd.googlegroups.com>


On Aug 28, 4:05 pm, "Bob Jones" <em..._at_me.not> wrote:
> <fitzjarr..._at_cox.net> wrote in message
>
> news:1188331305.449309.34890_at_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.
>
> Which word of "everything else being equal" don't you understand? By your
> logic, can you think of any performance stat that is meaningful?
>
> > '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.
>
> Again, how do you compare any 2 performance numbers when everything else
> being unequal.- Hide quoted text -
>
> - Show quoted text -

The contents of the buffer cache at time X may not be equal to the contents of the buffer cache at time Y, even if the BCHR values are exactly the same. Yet you still insist that the two ratios can be compared 'all else being equal'. You haven't told us what, exactly, that proves, except that you can calculate a ratio consistently.

David Fitzjarrell Received on Tue Aug 28 2007 - 16:29:22 CDT

Original text of this message

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