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: Bob Jones <email_at_me.not>
Date: Tue, 28 Aug 2007 22:02:04 GMT
Message-ID: <wX0Bi.12362$3x.7841@newssvr25.news.prodigy.net>

<fitzjarrell_at_cox.net> wrote in message
news:1188336562.896245.105300_at_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.

I don't disagree with that, but what does that prove? BHCR is meaningless? So far, we have just been debating about the concept. In pratice, you will observe it over a period of time.

> 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.
>

That proves BCHR is meaningful. It is ironic we are debating about something many consider meaningless.
Do you ever adjust the buffer cache size? What do you use as reference? Received on Tue Aug 28 2007 - 17:02:04 CDT

Original text of this message

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