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 21:45:17 GMT
Message-ID: <NH0Bi.12361$3x.5021@newssvr25.news.prodigy.net>

<hjr.pythian_at_gmail.com> wrote in message news:1187675622.406144.322710_at_x35g2000prf.googlegroups.com...

> On Aug 21, 3:19 pm, "Bob Jones" <em..._at_me.not> wrote:

>> "Richard Foote" <richard.fo..._at_nospam.bigpond.com> wrote in message
>>
>> news:fgixi.22091$4A1.5979_at_news-server.bigpond.net.au...
>>
>>
>>
>>
>>
>> > "Bob Jones" <em..._at_me.not> wrote in message
>> >news:eB8xi.1326$i75.244_at_newssvr19.news.prodigy.net...
>> >>>> Why is BHCR meaningless? The answer should be short and simple. I
>> >>>> want
>> >>>> to hear your opinion.
>>
>> >>> One can not prove a negative.
>> >>> Where is your proof BCHR is a reliable indicator of GOOD performance?
>>
>> >> BCHR alone does not tell you about overall performance. It simply tell
>> >> you the disk I/O percentage. It is an indicator, a very meaningful
>> >> one.
>>
>> > If your "disk I/O percentage" is really really high, what does that
>> > actually indicate ? Does it indicate all is well with the database or
>> > does
>> > it indicate all might not be well ? If you have SQL nasties that use
>> > index
>> > scans inappropriately or incorrectly loop through full scans of cached
>> > tables again and again and again, you might have users experiencing
>> > extremely poor response times. Or you might have users that are happy
>> > with
>> > their instant response times. You can't really tell and so it doesn't
>> > really give you much of an indicator.
>>
>> > If your "disk I/O percentage" is really really low, what does that
>> > actually indicate ? Does it indicate all is well with the database or
>> > does
>> > it indicate all might not be well ? It might indicate SQL nasties that
>> > use
>> > index scans inappropriately or incorrectly loop through full scans of
>> > tables (both large or small) and have users experiencing extremely poor
>> > response times. Or you might have users that are happy with their
>> > instant
>> > response times as all their online transactions run instantaneously
>> > because the various large batch reports that are running and causing
>> > all
>> > the high "disk I.O percentage" don't directly impact them at all. Just
>> > the
>> > BCHR ...
>>
>> > Sometimes when the BCHR changes from one level to another, it might
>> > mean
>> > there's an issue. Sometimes it doesn't.
>>
>> > The one constant though is that when there are performance issues,
>> > response times suffer for those folk/processes experiencing the
>> > performance issues. That can happen if the BCHR is low or high. And the
>> > actual cause of a performance issue needs to be investigated whether
>> > the
>> > BCHR is high or low to determine an appropriate fix for the issue.
>>
>> > Now if there are performance issues relating to excessive "disk I/O
>> > percentage" bottlenecks for SQLs that are efficient either in terms of
>> > LIO
>> > counts or execution counts, then an increase in memory might be a
>> > reasonable cause of action. However, that requires looking at the cause
>> > of
>> > the issue, not the possible symptoms.
>>
>> > Therefore the best indicator, the most meaningful one, is whether
>> > response
>> > times are meeting business requirements or not. And if not why not,
>> > regardless of the BCHR because a low or high BCHR may or may not be
>> > contributing to the problem. If response times do meet business
>> > requirements, then who really cares what the BCHR might be ?
>>
>> 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.
>
> How about you first dealing with some of the issues that have been
> raised?
>
> THIS indicator is not worth caring about because its meaning is
> ambiguous and therefore it is non-prescriptive: it cannot tell you
> what to do to improve a problem, let alone whether you have a problem.
>
> OTHER indicators, however, are not so ambiguous. A low parse/execute
> ratio would indicate poorly-shareable SQL: use bind variables or
> switch on CURSOR_SHARING. A high count of RELOADS in v$librarycache
> would indicate an insufficiently-sized shared pool: increase it. And
> so on (and yes I'm over-simplifying the indicators and what they
> indicate for the purposes of this post).
>

I hope you are not telling me these 2 indicators cannot be inflated by the applications.
What does RELOADS tell me about wether the system is doing useful work? What number of RELOADS is good or bad? I have 30000 reloads. Do I need to increase my library cache? Received on Tue Aug 28 2007 - 16:45:17 CDT

Original text of this message

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