Cache Hit Ratio from system views

42 views
Skip to first unread message

Teresa Masino

unread,
Aug 8, 2007, 11:57:33 AM8/8/07
to
We have acquired a software package that sends alerts based on defined
thresholds and/or events. We are working on a simple alert like when
the cache hit ratio falls below a given threshold. The package came
with a query that uses the V$BUFFER_POOL_STATISTICS view. We have
typically used a query based on the V$SYSSTAT view. We left the query
in place that came with the product, but it reports low cache hit
ratios pretty frequently whereas putting another alert in place that
uses V$SYSSTAT does not.

We only have one pool -- DEFAULT. So we would expect the values to be
at least close, if not the same. They are usually the same, but
several times in the course of a day they are VERY different.

In addition to be different enough to trigger the alert, the values in
V$BUFFER_POOL_STATISTICS are sometimes a negative value. What's up
with that?

Can anyone explain to me why V$BUFFER_POOL_STATISTICS sometimes has
negative values or has very different values from V$SYSSTAT? We'd
like to know if we really have an issue with the size of our cache, or
one of those views isn't the right one to use.

Here are the queries being:

SELECT ROUND(((SUM(cur.value) + SUM(con.value) - SUM(phy.value)) /
(SUM(cur.value) + SUM(con.value))) * 100, 2) AS
CLUSTER_CACHE_HIT_RATIO
FROM v$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'


SELECT DECODE(NAME, 'DEFAULT', DECODE(block_size, 2048, 'CACHE_2K',
4096, 'CACHE_4K', 8192, 'CACHE_8K', 16384, 'CACHE_16K', 'CACHE_32K'),
NAME) NAME , PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
( 1 - ((DECODE (PHYSICAL_READS, 0, 1, PHYSICAL_READS)) /
DECODE((DB_BLOCK_GETS + CONSISTENT_GETS),0, DECODE(PHYSICAL_READS, 0,
1, PHYSICAL_READS) ,(DB_BLOCK_GETS + CONSISTENT_GETS)))) * 100 AS
BUFFER_RATIO
FROM V$BUFFER_POOL_STATISTICS

Any assistance in helping us make sense of this is greatly
appreciated.

Teresa Masino

fitzj...@cox.net

unread,
Aug 8, 2007, 12:22:33 PM8/8/07
to

Which release of Oracle is this?


David Fitzjarrell

Teresa Masino

unread,
Aug 8, 2007, 12:30:19 PM8/8/07
to
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

duh, sorry. We're running 10g. Specifically:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 on one
server and Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
on another.


Teresa

fitzj...@cox.net

unread,
Aug 8, 2007, 12:53:49 PM8/8/07
to
> Teresa- Hide quoted text -

>
> - Show quoted text -

Does this negative output occur on both releases? It appears to work
fine on my 10.2.0.2 database.


David Fitzjarrell

Teresa Masino

unread,
Aug 8, 2007, 1:36:00 PM8/8/07
to

Most of the time the queries return expected results for us too. If I
run the queries in a loop that sleeps for 30 seconds or so and let it
go for a while, they eventually report different results. It can take
10 minutes to see a discrepancy, it can take over an hour. But at
some point, they do report different values. Which leads us to wonder
which one we should pay attention to. One of them tells us things are
fine and the other says they aren't. And then there's the wild part
where the BUFFER_POOL_STATISTICS view has negative values. That part
alone makes me question the validity of those values, but I thought
I'd check here to see if anyone knows for sure.

Thanks
Teresa

fitzj...@cox.net

unread,
Aug 8, 2007, 2:21:14 PM8/8/07
to

Metalink reports the view was buggy up until 9.2.0.2, however it may
be a platform-specific issue. On which O/S are you running Oracle?


David Fitzjarrell

DA Morgan

unread,
Aug 8, 2007, 2:31:12 PM8/8/07
to
Teresa Masino wrote:
> We have acquired a software package that sends alerts based on defined
> thresholds and/or events. We are working on a simple alert like when
> the cache hit ratio falls below a given threshold.

Because you wanted to hurt yourself and there weren't any brick in the
office you could drop on your toes? <g>

Here ...
http://www.oracledba.co.uk/tips/choose.htm
have any hit ratio you want any time you want.

Hit ratio's are meaningless.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Teresa Masino

unread,
Aug 8, 2007, 2:32:29 PM8/8/07
to

It's the server running 10.1.0.5.0 where we see the differences and
that's on Linux. I saw some old postings saying things like that too
and wondered. Afterall, we are counting on somebody somewhere writing
code to populate that data. FYI, we also opened a ticket with
Oracle, so if I get an answer from them, I'll let you know. But that
case has been open for a couple of weeks now, so I'm not very
optimistic.

Thanks
Teresa

Teresa Masino

unread,
Aug 8, 2007, 3:02:46 PM8/8/07
to
On Aug 8, 2:31 pm, DA Morgan <damor...@psoug.org> wrote:
> Teresa Masino wrote:
> > We have acquired a software package that sends alerts based on defined
> > thresholds and/or events. We are working on a simple alert like when
> > the cache hit ratio falls below a given threshold.
>
> Because you wanted to hurt yourself and there weren't any brick in the
> office you could drop on your toes? <g>

:-) yea, basically

> Here ...http://www.oracledba.co.uk/tips/choose.htm


> have any hit ratio you want any time you want.
>
> Hit ratio's are meaningless.

Alone, yea, but they can sometimes be an indicator that an instance
needs attention. I wouldn't change anything based on just this, but
it would tell us to maybe take a closer look and run AWR or
statspack. For us, this was supposed to be an easy thing to test
setting up alerts -- at least we thought it was. Then this boondoggle
began. Then I got curious, which is just another way of saying I
started looking for a brick. :-)

Thanks
Teresa

> Daniel A. Morgan
> University of Washington

> damor...@x.washington.edu (replace x with u to respond)

sybr...@hccnet.nl

unread,
Aug 8, 2007, 3:38:00 PM8/8/07
to
On Wed, 08 Aug 2007 12:02:46 -0700, Teresa Masino
<teresa...@peninsula.org> wrote:

>
>Alone, yea, but they can sometimes be an indicator that an instance
>needs attention.

Do you really think so? Yesterday I had a customer spouting flames
because of a RAC database coming down to a crawl due to buffer busy
waits in conjunction with both instances updating the same table over
and over again.
Nothing to be seen from the BCHR.

But then of course BCHR is a MIPS (Meaningless Indicator of the
Performance of the System).
Only good old Don Burleson makes people erroneously believe they can
tune their database by throwing memory at the problem to crank up the
BCHR.
Savy professionals (a favorite phrase of Don) know better.

--
Sybrand Bakker
Senior Oracle DBA

fitzj...@cox.net

unread,
Aug 8, 2007, 3:53:59 PM8/8/07
to

10.1.0.x was fairly 'buggy' to begin with. And depending upon which
'flavor' of Linux this is it could be platform-specific, as I said
earlier.

There's not much more I can tell you. Well, except that I hope your
brick is fairly soft. :)


David Fitzjarrell

Niall Litchfield

unread,
Aug 8, 2007, 4:11:14 PM8/8/07
to

enough of you will recognise where I stand on ratios. Indeed I believe
that I presented on the effectiveness or in fact otherwise of the BCHR
on its own nearly 5 years ago.

I have some sympathy withe Teresa's sentiment above though. Imagine a
system that has had a truly horrible burleson concerned hopelessness
ratio of 75% since 2002. Today the BCHR is 25% (or indeed 98%). I
venture to suggest that it's a change worth determining the reasons for,
either way. Ratios don't mean anything much, abrupt changes in them
often do.

--
Niall Litchfield
Oracle DBA
http://www.orawin.info/services

DA Morgan

unread,
Aug 8, 2007, 4:14:29 PM8/8/07
to

On the other hand monitoring things that matter would save you the
trouble of chasing after smoke in a house of mirrors.

There are real things that can be monitored. And if Theresa has the
license to use AWR as it seems she'd be far better served looking at
things that matter.
--

Daniel A. Morgan
University of Washington

damo...@x.washington.edu (replace x with u to respond)

Niall Litchfield

unread,
Aug 8, 2007, 4:51:15 PM8/8/07
to
On Aug 8, 9:14 pm, DA Morgan <damor...@psoug.org> wrote:
> Niall Litchfield wrote:
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

>
> - Show quoted text -

Indeed. And since EM will do alerts based on user defined metrics,
it's possible the whole thing is moot.

Niall

DA Morgan

unread,
Aug 9, 2007, 11:31:52 AM8/9/07
to

Nothing prevents you from driving your car into a telephone pole.
Smart people stay between the lines.


--
Daniel A. Morgan
University of Washington

damo...@x.washington.edu (replace x with u to respond)

Frank van Bortel

unread,
Aug 9, 2007, 2:32:43 PM8/9/07
to
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

DA Morgan wrote:

>
> Nothing prevents you from driving your car into a telephone pole.
> Smart people stay between the lines.

Lines - what lines; telephone lines?
- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
Blind woman forced off the road; license expired:
http://www.blikopnieuws.nl/bericht/55525
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGu13LLw8L4IAs830RAiuHAJ4pzy/jexVdvegtjDYT2fjQBfXQXQCcDwE1
E2EcwbTlWxUcpO1cvl+8JwQ=
=aDMK
-----END PGP SIGNATURE-----

Bob Jones

unread,
Aug 9, 2007, 9:59:52 PM8/9/07
to
>>Alone, yea, but they can sometimes be an indicator that an instance
>>needs attention.
> Do you really think so? Yesterday I had a customer spouting flames
> because of a RAC database coming down to a crawl due to buffer busy
> waits in conjunction with both instances updating the same table over
> and over again.
> Nothing to be seen from the BCHR.
>
> But then of course BCHR is a MIPS (Meaningless Indicator of the
> Performance of the System).
> Savy professionals (a favorite phrase of Don) know better.
>

That is a common mistake of "savy professionals" - overlooking the basics
and thinking they know better.


sybr...@hccnet.nl

unread,
Aug 10, 2007, 1:22:15 AM8/10/07
to

Assuming you are such a 'savy professional' 'Mr' Jones, why didn't you
notice throwing memory at the problem doesn't help?
On another note: If you think *you* know better, why don't you
contribute a *working* example of the Burleson strategy?
You have a track record of lurking and only chiming in when you can
flame someone with the drivel like you posted above.
I recall your flame wars with Daniel Morgan, which only served to
establish *you* are NOT a 'savy professional' but just some *arrogant
idiot* (of which we have already way too many here).

Bob Jones

unread,
Aug 10, 2007, 10:03:59 PM8/10/07
to
>>>>Alone, yea, but they can sometimes be an indicator that an instance
>>>>needs attention.
>>> Do you really think so? Yesterday I had a customer spouting flames
>>> because of a RAC database coming down to a crawl due to buffer busy
>>> waits in conjunction with both instances updating the same table over
>>> and over again.
>>> Nothing to be seen from the BCHR.
>>>
>>> But then of course BCHR is a MIPS (Meaningless Indicator of the
>>> Performance of the System).
>>> Savy professionals (a favorite phrase of Don) know better.
>>>
>>
>>That is a common mistake of "savy professionals" - overlooking the basics
>>and thinking they know better.
>>
>
> Assuming you are such a 'savy professional' 'Mr' Jones, why didn't you
> notice throwing memory at the problem doesn't help?

It can help sometimes. But what does that have to do with anything?

> On another note: If you think *you* know better, why don't you
> contribute a *working* example of the Burleson strategy?

Did I say anything about his strategy? Yet another assumption.

> You have a track record of lurking and only chiming in when you can
> flame someone with the drivel like you posted above.

I clearly disagree with the statement that BHCR is meaningless. This has
been discussed several times before. I won't waste any more energy.

> I recall your flame wars with Daniel Morgan, which only served to
> establish *you* are NOT a 'savy professional' but just some *arrogant
> idiot* (of which we have already way too many here).
>

What did we discuss? Was I wrong? I have no interest in becoming what you
call a "savy professional", because that really amount to exactly, an
arrogant idiot.


DA Morgan

unread,
Aug 11, 2007, 1:13:17 AM8/11/07
to
Bob Jones wrote:

> I clearly disagree with the statement that BHCR is meaningless. This has
> been discussed several times before. I won't waste any more energy.

Disagree all you wish Connor proved this clearly and decisively many
years ago. And it should come as no surprise that Connor is one of the
most capable, and respected, Oracle technologists on the planet for a
reason.

>> I recall your flame wars with Daniel Morgan, which only served to
>> establish *you* are NOT a 'savy professional' but just some *arrogant
>> idiot* (of which we have already way too many here).
>
> What did we discuss? Was I wrong? I have no interest in becoming what you
> call a "savy professional", because that really amount to exactly, an
> arrogant idiot.

Beats heck out of being one an not being a savvy professional. <g>

If you wish to be taken seriously, and you think Connor's proof invalid,
by all means demonstrate your case with a replicable test environment
and metrics.

If you can produce it then you don't have a case and should buy lots
of books from Rampant.
--

Daniel A. Morgan
University of Washington

damo...@x.washington.edu (replace x with u to respond)

Bob Jones

unread,
Aug 15, 2007, 11:42:16 PM8/15/07
to
>> I clearly disagree with the statement that BHCR is meaningless. This has
>> been discussed several times before. I won't waste any more energy.
>
> Disagree all you wish Connor proved this clearly and decisively many
> years ago. And it should come as no surprise that Connor is one of the
> most capable, and respected, Oracle technologists on the planet for a
> reason.
>

I do not know who this Connor guy is. Did he really say BHCR is meaningless?
I would love to see his proof.

>>> I recall your flame wars with Daniel Morgan, which only served to
>>> establish *you* are NOT a 'savy professional' but just some *arrogant
>>> idiot* (of which we have already way too many here).
>>
>> What did we discuss? Was I wrong? I have no interest in becoming what you
>> call a "savy professional", because that really amount to exactly, an
>> arrogant idiot.
>
> Beats heck out of being one an not being a savvy professional. <g>
>

Not really given the meaning of "savvy professional" here.

> If you wish to be taken seriously, and you think Connor's proof invalid,
> by all means demonstrate your case with a replicable test environment
> and metrics.
>
> If you can produce it then you don't have a case and should buy lots
> of books from Rampant.

I think the burden is on you to prove BHCR is meaningless. Maybe the issue
here is reading too many books and articles.


sybr...@hccnet.nl

unread,
Aug 16, 2007, 1:50:26 AM8/16/07
to
On Thu, 16 Aug 2007 03:42:16 GMT, "Bob Jones" <em...@me.not> wrote:

>>> I clearly disagree with the statement that BHCR is meaningless. This has
>>> been discussed several times before. I won't waste any more energy.
>>
>> Disagree all you wish Connor proved this clearly and decisively many
>> years ago. And it should come as no surprise that Connor is one of the
>> most capable, and respected, Oracle technologists on the planet for a
>> reason.
>>
>
>I do not know who this Connor guy is. Did he really say BHCR is meaningless?
>I would love to see his proof.

This 'Connor guy' (a label intended as disrespectful) is Connor Mc
Donald, a member of the OakTable Network. His site is
http://www.oracledba.co.uk. The proof is on this site including a
demonstration.


>
>I think the burden is on you to prove BHCR is meaningless. Maybe the issue
>here is reading too many books and articles.
>

I think the burden is on you to prove it is meaningfull and to
contradict the figures provided by Connor McDonald.
I think the issue here is you have read to many fairy tales composed
by Donald K Burleson, Mike R Ault, and other Rampant authours.
May be you get paid by Burleson to bash 'savy professionals'.
But most likely you are someone who like Burleson's 'savy
proefessionals' just doesn't know what he is talking about.
I would love to see your apologize here once you have read the article
on www.oracledba.co.uk, but I'm not holding my breath: you have
established yourself here as an arrogant incompetent troll.

DA Morgan

unread,
Aug 16, 2007, 3:33:22 PM8/16/07
to
Bob Jones wrote:
>>> I clearly disagree with the statement that BHCR is meaningless. This has
>>> been discussed several times before. I won't waste any more energy.
>> Disagree all you wish Connor proved this clearly and decisively many
>> years ago. And it should come as no surprise that Connor is one of the
>> most capable, and respected, Oracle technologists on the planet for a
>> reason.
>>
>
> I do not know who this Connor guy is. Did he really say BHCR is meaningless?
> I would love to see his proof.

This Connor guy?

You mean THIS Connor guy don'you?
http://www.oracledba.co.uk/index.html
http://www.oaktable.net/pageServer.jsp?body=members.jsp

Next are we going to see "This Mogens guy" or "Who the heck is this
Kyte guy? BHCR is meaningless. Totally meaningless. The only reason
anyone pays any attention to it is that it has been promoted ad nauseum
by the self-anointed to the clueless.

Bob Jones

unread,
Aug 16, 2007, 9:37:27 PM8/16/07
to

<sybr...@hccnet.nl> wrote in message
news:30p7c39h7e4nj7t1b...@4ax.com...

Do I really need to respond to this moron?


Bob Jones

unread,
Aug 16, 2007, 9:46:32 PM8/16/07
to

"DA Morgan" <damo...@psoug.org> wrote in message
news:11872928...@bubbleator.drizzle.com...

> Bob Jones wrote:
>>>> I clearly disagree with the statement that BHCR is meaningless. This
>>>> has been discussed several times before. I won't waste any more energy.
>>> Disagree all you wish Connor proved this clearly and decisively many
>>> years ago. And it should come as no surprise that Connor is one of the
>>> most capable, and respected, Oracle technologists on the planet for a
>>> reason.
>>>
>>
>> I do not know who this Connor guy is. Did he really say BHCR is
>> meaningless? I would love to see his proof.
>
> This Connor guy?
>
> You mean THIS Connor guy don'you?
> http://www.oracledba.co.uk/index.html
> http://www.oaktable.net/pageServer.jsp?body=members.jsp
>
> Next are we going to see "This Mogens guy" or "Who the heck is this
> Kyte guy? BHCR is meaningless. Totally meaningless. The only reason
> anyone pays any attention to it is that it has been promoted ad nauseum
> by the self-anointed to the clueless.

Well, all you have to do is to give one reason? Not people's names or a
bunch of links.

Why is BHCR meaningless? The answer should be short and simple. I want to
hear your opinion.


hjr.p...@gmail.com

unread,
Aug 16, 2007, 10:10:07 PM8/16/07
to
On Aug 17, 11:37 am, "Bob Jones" <em...@me.not> wrote:
> <sybra...@hccnet.nl> wrote in message
> > onwww.oracledba.co.uk, but I'm not holding my breath: you have

> > established yourself here as an arrogant incompetent troll.
>
> Do I really need to respond to this moron?

When in Rome, jump in feet first, I always say. So here goes...

Whether Connor McDonald is respected, famous, useless, brilliant or
whatever is irrelevant. People really shouldn't appeal to authority
like that as if doing so is an answer in itself.

The fact remains that in 2002, he wrote this paper:
http://www.oracledba.co.uk/tips/choose.htm which contains a nice piece
of PL/SQL that performs a repeated set of consistent gets until such
time as your hit ratio goes up to any number you desire. Specify any
hit ratio you like, you'll get it (so long as you're not already in
excess of it).

That piece of code conclusively proves... nothing very much except
that if you do a lot of consistent gets, your ratio will go up. Pretty
obvious stuff, really -though Connor's demonstration of the point is
very neat.

Smart code doesn't make a realistic argument, though. At least, not
for some. So what, in real life, would cause lots of consistent gets
and thus inflate the Buffer Cache Hit Ratio? Well, for one thing,
contention for the head of the free list will mean the segment header
block gets repeatedly 'hit' in the buffer cache. If you don't have
enough rollback segments (or undo segments), then your undo segment
headers will be forever being hit in the cache. If you have hot blocks
on one or two of your tables, you'll be forever hitting those blocks
in cache. If you rebuild your indexes every ten minutes, you'd
probably get lots of hits in the cache. Lots of hits in the cache
means a better hit ratio.

So a good buffer cache hit ratio is certainly of diagnostic value: if
it's high, it may well indicate that you're suffering from performance
**problems** caused by contention, insufficient undo or poor DBA
maintenance practices.

Of course, on the other hand, it may not indicate those things at all,
but may indicate a healthy cache doing its job properly.

Trouble is, an indicator that flashes red when things are bad and also
flashes red when things are good isn't exactly much use, is it? Apart
from telling you "something", you can't really work out what that
"something" is. Which means the "something" is actually 'nothing very
much'!

I'm looking at 9 databases right now that all have 99% - 100% hit
ratios (the databases are only 15GB in size, and each has a 5GB buffer
cache, so that ratio is not exactly surprising). But whilst Quest's
Spotlight is forever displaying my hit ratio in green, it is also
flashing warnings at me that my buffer busy waits are 36% averaged in
the last 30 seconds. I have a nasty performance problem there (caused
by really poor physical IO distribution, actually), but the hit ratio
is looking great.

I think that is the point about the uselessness of the BCHR that is
being made.

I know of only one mildly effective use for the BCHR. When I am told I
am suffering from buffer busy waits, there are really two main
potential causes: one, the cache is too small and therefore nearly
every query has to hang around waiting for physical reads from disk;
two, the IO subsystem is awful and blocks can't be fetched quickly
enough when needed. How do you know whether its the 'small cache' or
'bad IO' cause for your particular set of nasty buffer busy waits? You
could check the hit ratio. If the cause was 'small cache', one would
expect the hit ratio to be poor as well as having the busy waits. If
the cause was 'bad IO', you can't really predict whether the hit ratio
will be good or bad, but chances are its going to be reasonable. As
such the hit ratio can help in distinguishing between two equally
probable causes of a problem identified from wait statistics, and
even then it's all a bit woolly. Some indeed might reasonably argue
that inspecting the init.ora and seeing what DB_CACHE_SIZE is set to
might resolve the doubts about cache size being a factor in causing
your high busy waits rather more simply, and I'm inclined to agree.

Ana C. Dent

unread,
Aug 16, 2007, 10:34:34 PM8/16/07
to
"Bob Jones" <em...@me.not> wrote in
news:Y57xi.245$LL7...@nlpi069.nbdc.sbc.com:

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

Bob Jones

unread,
Aug 16, 2007, 11:21:34 PM8/16/07
to

<hjr.p...@gmail.com> wrote in message
news:1187316607.7...@q3g2000prf.googlegroups.com...

BCHR can be manipulated. That is nothing new. All stats can be inflated in
similar manners.
But that doesn't make them all meaningless. Given everything else being
equal, high BCHR is always better than low BHCR.

> Smart code doesn't make a realistic argument, though. At least, not
> for some. So what, in real life, would cause lots of consistent gets
> and thus inflate the Buffer Cache Hit Ratio? Well, for one thing,
> contention for the head of the free list will mean the segment header
> block gets repeatedly 'hit' in the buffer cache. If you don't have
> enough rollback segments (or undo segments), then your undo segment
> headers will be forever being hit in the cache. If you have hot blocks
> on one or two of your tables, you'll be forever hitting those blocks
> in cache. If you rebuild your indexes every ten minutes, you'd
> probably get lots of hits in the cache. Lots of hits in the cache
> means a better hit ratio.
>

From systems point of view, performance is the amount of work done in a
specific interval, regardless of the type or usefulness of the work.

>
> Of course, on the other hand, it may not indicate those things at all,
> but may indicate a healthy cache doing its job properly.
>
> Trouble is, an indicator that flashes red when things are bad and also
> flashes red when things are good isn't exactly much use, is it? Apart
> from telling you "something", you can't really work out what that
> "something" is. Which means the "something" is actually 'nothing very
> much'!
>

BCHR alone is not meant to tell performance. If it does, we would not have
to look at anything else.

> I'm looking at 9 databases right now that all have 99% - 100% hit
> ratios (the databases are only 15GB in size, and each has a 5GB buffer
> cache, so that ratio is not exactly surprising). But whilst Quest's
> Spotlight is forever displaying my hit ratio in green, it is also
> flashing warnings at me that my buffer busy waits are 36% averaged in
> the last 30 seconds. I have a nasty performance problem there (caused
> by really poor physical IO distribution, actually), but the hit ratio
> is looking great.
>
> I think that is the point about the uselessness of the BCHR that is
> being made.
>

The issue here is again not isolating the performance factors. It has
nothing to do with the meaningfulness of BCHR. It still tells you the disk
I/O percentage.That is all it does.

A weight scale measures a person's weight. Of course, someone can push the
person down to inflate the reading. However, we don't say the scale is
useless because it doesn't always tell a person's true body weight.


Bob Jones

unread,
Aug 16, 2007, 11:28:10 PM8/16/07
to
>> 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.


Ana C. Dent

unread,
Aug 16, 2007, 11:47:41 PM8/16/07
to
"Bob Jones" <em...@me.not> wrote in
news:eB8xi.1326$i75...@newssvr19.news.prodigy.net:

HUH? BCHR does NOT come close to measure disk I/O;
so by what stretch of imagination does it measure "I/O percentage"?
BCHR measure RAM activity says absoluting NOTHING about disk activity.

You said, "It (BCHR) is an indicator, a very meaningful one."

Please answer each below as a standlone measure of performance
System A has a BCHR of 22. What does it indicate?
System B has a BCHR of 42. What does it indicate?
System C has a BCHR of 62. What does it indicate?
System D has a BCHR of 82. What does it indicate?

Bob Jones

unread,
Aug 17, 2007, 12:28:26 AM8/17/07
to

"Ana C. Dent" <anac...@hotmail.com> wrote in message
news:xT8xi.82911$kK1....@newsfe14.phx...

> "Bob Jones" <em...@me.not> wrote in
> news:eB8xi.1326$i75...@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.
>>
>
> HUH? BCHR does NOT come close to measure disk I/O;
> so by what stretch of imagination does it measure "I/O percentage"?
> BCHR measure RAM activity says absoluting NOTHING about disk activity.
>

Allow me to clarify, the percentage of reads from disk.

> You said, "It (BCHR) is an indicator, a very meaningful one."
>
> Please answer each below as a standlone measure of performance
> System A has a BCHR of 22. What does it indicate?
> System B has a BCHR of 42. What does it indicate?
> System C has a BCHR of 62. What does it indicate?
> System D has a BCHR of 82. What does it indicate?
>

22% of reads are from memory.
42% of reads are from memory.
62% of reads are from memory.
82% of reads are from memory.

I hope you are trying to make a point here.


hjr.p...@gmail.com

unread,
Aug 17, 2007, 1:35:39 AM8/17/07
to
On Aug 17, 2:28 pm, "Bob Jones" <em...@me.not> wrote:
> "Ana C. Dent" <anaced...@hotmail.com> wrote in messagenews:xT8xi.82911$kK1....@newsfe14.phx...

The point is, of what use are you going to put this information. No-
one would deny that the BCHR tells you "something". I said earlier
it's a flashing red light: flashing red lights tell you something. But
do they tell you your nuclear powerplant is about to reach meltdown or
that it's fine and healthy? If the mere fact of flashing tells you
neither or both, it's not of any *practical* value, and its undoubted
information content ("I am flashing") is of no use.

It's whether that something is of any **use** that's the issue.

So if you see that 22% of your reads are from memory, is that good or
bad? Do you need to increase the memory or not?
If you see 100% of your reads are from memory, is that good or bad?
Are you hitting block contention issues and thus inflating the BCHR,
or not?

You cannot tell from the ratio itself. The ratio therefore has no
prescriptive value: it doesn't tell you to increase this, reduce that,
change this piece of code, move that table, rebuild that index... or
indeed anything else.

The ratio is a number. The number has an ambiguous meaning in terms of
actually telling anyone anything about how to tune a database.
Therefore, the ratio is meaningless.

Elsewhere, you say, "Given everything else being equal, high BCHR is
always better than low BHCR". I gave you examples of where a high
ratio indicates a performance *problem*. Where a high ratio would be
WORSE, not better, than a low ratio. You just sort of sailed over that
one saying, "performance is the amount of work done in a specific
interval, regardless of the type or usefulness of the work". On that
basis, you could just sit there with an infinite loop calculating
primes for no reason at all and chewing up all your CPU. Your database
will run like crap, but your machine -on your definition- is
"performing". Alternatively you could just sit there rebuilding
indexes that don't actually need it because otherwise your CPU cycles
will be wasted. Either way, your machine is "performing" according to
you and wasting its time according to me (and, I suggest, most
people's view of what constitutes 'performance').

A more rational approach is to say that "performance" is the ability
of a system to carry out USEFUL work. Hammering an undo segment header
block to death because the DBA hasn't sized the undo tablespace
properly doesn't, on that definition count, but it will make your hit
ratio higher. Meanwhile, the high ratio won't be telling the DBA
'increase the size of your undo tablespace', but an analysis of the
blocks constantly subjected to buffer busy waits would.

If one were to accept that there is useful and non-useful work that a
database can perform; if one were to accept that the non-useful work
can inflate a hit ratio; it must therefore follow that you cannot
legitimately say 'a higher ratio is always better than a low one'. And
if you can't say that, then the ratio is useless.

hpuxrac

unread,
Aug 17, 2007, 10:22:00 AM8/17/07
to

Lots and lots of words in this thread by multiple people on a subject
that is dead and buried many years ago.

Must be a slow day somewhere.

Richard Foote

unread,
Aug 17, 2007, 10:28:27 AM8/17/07
to

"Bob Jones" <em...@me.not> wrote in message
news:eB8xi.1326$i75...@newssvr19.news.prodigy.net...

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 ?

Cheers

Richard

fitzj...@cox.net

unread,
Aug 17, 2007, 10:46:51 AM8/17/07
to
On Aug 16, 8:46 pm, "Bob Jones" <em...@me.not> wrote:
> "DA Morgan" <damor...@psoug.org> wrote in message
> hear your opinion.- Hide quoted text -

>
> - Show quoted text -

http://oratips-ddf.blogspot.com/2007/07/bchr-follies-my-two-cents.html


David Fitzjarrell

DA Morgan

unread,
Aug 17, 2007, 12:45:52 PM8/17/07
to
hjr.p...@gmail.com wrote:

> When in Rome, jump in feet first, I always say. So here goes...

Well done Howard.

> Whether Connor McDonald is respected, famous, useless, brilliant or
> whatever is irrelevant. People really shouldn't appeal to authority
> like that as if doing so is an answer in itself.

I agree. But mentioning Connor is not an appeal to authority. It is
an appeal to someone, if they were truly interested in learning
something which is not the case here it would seem, to use google to
search for "Hit Ratio" and "Connor" which would bring back 924 hits
including the paper you referenced.

hjr.p...@gmail.com

unread,
Aug 17, 2007, 8:29:10 PM8/17/07
to

Actually, the subject's not dead and buried at all. I wish it were,
but only 6 weeks ago, I recently prepared an 8 page, detailed
explanation of why the BCHR was not a reliable guide to anything and
the boss to whom it was presented came back to me within 4 minutes and
said, "Well, it's interesting from a theoretical point of view, but
practically, a high hit ratio has got to be better than a low one,
no?'

It's a very common fallacy, even today. And even amongst those who do
DBAing for real and have done so for some time. Why, even the world's
Greatest Oracle Expert still bangs on about this stuff.

I therefore have no problem spending a bit of time trying to kill this
particular dragon one more time, and I don't consider it a waste of
time doing so.

joel garry

unread,
Aug 18, 2007, 3:15:51 AM8/18/07
to

Others have shown why it is not a meaningful indicator. I would just
like to add the observation for those who might not already know, this
is one of the prime examples of how oversimplifying a metric can leach
into the education of Oracle, becoming enshrined in performance tuning
scripts and propagated into myth. It is worse than meaningless, it is
actually misleading when people think it is showing disk I/O
percentage. It may have some corrrelation to such a percentage on a
very simple system with a very simple test (and can just as simply be
bogosified), but most production systems actually have multiple users
sharing and updating data, with hardware that may not be _telling the
truth to Oracle_ as to whether it is actually doing I/O. Throw an SSD
in there and it is all lies.

I don't have any argument with the idea that response times meeting
business requirements is a, or even the most reasonable metric. But I
feel compelled to point out how common it is to not define those
requirements properly, so making an assumption that there are already
properly defined response time requirements is jumping ahead too far
in a performance methodology. And yet, by the time there is a
perceived problem, it is already too late.

jg
--
@home.com is bogus.
Isn't it ironic...they didn't even deal with the subrime market...
http://www.firstmagnus.com//

Bob Jones

unread,
Aug 21, 2007, 12:24:48 AM8/21/07
to

<hjr.p...@gmail.com> wrote in message
news:1187328939.0...@i13g2000prf.googlegroups.com...

I don't know of a performance stat which is capable of that.

> It's whether that something is of any **use** that's the issue.
>
> So if you see that 22% of your reads are from memory, is that good or
> bad? Do you need to increase the memory or not?

Without any other information? I will need my crystal ball.

> If you see 100% of your reads are from memory, is that good or bad?
> Are you hitting block contention issues and thus inflating the BCHR,
> or not?
>

I will say it again. BCHR is not the only thing to look at. Overall
performance depends on many factors. I can also ask similar questions. Is my
performance good or bad if I have low block contention? Does it tell me if
my buffer cache is too small?

> You cannot tell from the ratio itself. The ratio therefore has no
> prescriptive value: it doesn't tell you to increase this, reduce that,
> change this piece of code, move that table, rebuild that index... or
> indeed anything else.
>
> The ratio is a number. The number has an ambiguous meaning in terms of
> actually telling anyone anything about how to tune a database.
> Therefore, the ratio is meaningless.
>

That may be expecting too much from a single performance stat.

> Elsewhere, you say, "Given everything else being equal, high BCHR is
> always better than low BHCR". I gave you examples of where a high
> ratio indicates a performance *problem*. Where a high ratio would be
> WORSE, not better, than a low ratio.

With everything else being equal?

>You just sort of sailed over that
> one saying, "performance is the amount of work done in a specific
> interval, regardless of the type or usefulness of the work". On that
> basis, you could just sit there with an infinite loop calculating
> primes for no reason at all and chewing up all your CPU. Your database
> will run like crap, but your machine -on your definition- is
> "performing". Alternatively you could just sit there rebuilding
> indexes that don't actually need it because otherwise your CPU cycles
> will be wasted. Either way, your machine is "performing" according to
> you and wasting its time according to me (and, I suggest, most
> people's view of what constitutes 'performance').
>

There is no performance stat I know can tell whether the system is doing
useful work from human's perspective. Just like a speedometer, it can tell
you the speed, but it cannot tell if you are circling the same block.

> A more rational approach is to say that "performance" is the ability
> of a system to carry out USEFUL work. Hammering an undo segment header
> block to death because the DBA hasn't sized the undo tablespace
> properly doesn't, on that definition count, but it will make your hit
> ratio higher. Meanwhile, the high ratio won't be telling the DBA
> 'increase the size of your undo tablespace', but an analysis of the
> blocks constantly subjected to buffer busy waits would.
>

What if the DBA has sized the undo tablespace correctly? Does buffer busy
waits tell you anything about performance?

> If one were to accept that there is useful and non-useful work that a
> database can perform; if one were to accept that the non-useful work
> can inflate a hit ratio; it must therefore follow that you cannot
> legitimately say 'a higher ratio is always better than a low one'. And
> if you can't say that, then the ratio is useless.
>

A machine cannot possibly know whether it is doing "useful" work. Non-useful
work inflates many things not just hit ratio.
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.


Bob Jones

unread,
Aug 21, 2007, 1:19:02 AM8/21/07
to

"Richard Foote" <richar...@nospam.bigpond.com> wrote in message
news:fgixi.22091$4A1....@news-server.bigpond.net.au...

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.


hjr.p...@gmail.com

unread,
Aug 21, 2007, 1:53:42 AM8/21/07
to
On Aug 21, 3:19 pm, "Bob Jones" <em...@me.not> wrote:
> "Richard Foote" <richard.fo...@nospam.bigpond.com> wrote in message

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

There are plenty of GOOD indicators in the database. The BCHR just
happens not to be one of them.

DA Morgan

unread,
Aug 21, 2007, 2:18:01 PM8/21/07
to
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.

DA Morgan

unread,
Aug 21, 2007, 2:21:57 PM8/21/07
to
Bob Jones wrote:

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

You seem to really invest yourself more fully in having an argument than
you do in considering that the other person, in this case a member of
the Oak Table, might actually know what he's talking about.

Expert after expert in the Oracle database is telling you that you are
wrong. And all you want to do is argue.

No metrics to support you.
No evidence of having read and understand what you are responding to.
Just synapse-free mindless argument for the sake of argument.
To what end?
The hole you are standing in is already well over your head.

Steve Howard

unread,
Aug 21, 2007, 2:57:18 PM8/21/07
to
On Aug 21, 2:18 pm, DA Morgan <damor...@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...@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.

sybr...@hccnet.nl

unread,
Aug 21, 2007, 3:26:47 PM8/21/07
to
On Tue, 21 Aug 2007 18:57:18 -0000, Steve Howard
<steved...@gmail.com> wrote:

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

This is not the issue.
The issue is whether you meet your SLA, and/or whether end-users
complain.
In one particular system I rectified all untuned statements. Then an
'expert' developer of the 3rd party vendor came in. He reverted
*everything* and now no statement is using any index *by design*.
BCHR is 20 percent.

End-users don't complain!

Brian Peasland

unread,
Aug 21, 2007, 3:34:48 PM8/21/07
to

I don't know that I could answer yes or no to that question. What do
those numbers say about end user performance? Nothing. The DBA may have
incorrectly sized the buffer cache...or it may be just right. The above
numbers do not tell us which case is true.

By not being able to answer yes or no, haven't we said something about
the usefulness of the BCHR? I think we have. We hae said that the BCHR
is not a reliable indicator of anything useful.

Cheers,
Brian

--
===================================================================

Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

DA Morgan

unread,
Aug 21, 2007, 5:11:51 PM8/21/07
to

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

damo...@x.washington.edu (replace x with u to respond)

hjr.p...@gmail.com

unread,
Aug 21, 2007, 9:54:28 PM8/21/07
to

The qualification is meaningless. If everything else is equal, then
the two hit ratios will by definition be identical too. If you've got
two identical machines, with their OSes configured identically, with
the same instance configuration, the same physical database
configuration, and both performing exactly the same set of SQL
statements performed by the same number of users in the same way and
same order, then inevitably the BCHR is going to have to end up
essentially the same between the two of them.

So, to have a 50% ratio on one and 99% on another, SOMETHING must be
different and not 'otherwise equal' between the two machines.

The only meaningful question is, therefore, WHAT is different, and -
more relevant to your query- does the ratio help you find out what is
different?

Is it different because one database is doing a massive full table
scan and the other one isn't? Is it because the buffer cache is
differently-sized between the two databases? Is it that the undo
tablespace is differently sized between the two databases? Is it that
one database is being used to do lots of flashback queries and the
other one isn't? Is it indeed that your buffer caches are unequally-
sized? Indeed: has Connor McDonald logged onto the one machine and not
onto the other? Any or all of those things could be skewing the
ratio.

The specific answer to your specific question is therefore 'No',
because it isn't an indication of a mis-sized buffer cache, but an
indication that something, somewhere might or might not be right or
wrong. More than that it doesn't tell you.

The proposition can be simplified even further: suppose on ONE
database the BCHR is always and consistently 70% (meaning nothing in
and of itself) and then one day becomes 97%. That is indeed an
indication of a problem -or, at least, that something or some aspect
of database behaviour has changed, perhaps for the better, perhaps
not. If I was reading 10,000 blocks and finding 7000 of them in
memory, that would account for the 70% ratio. If I suddenly have to
issue a query that reads 1,000,000 blocks of which 970,000 are in
memory, that would account for the 97% ratio... but the 97% ratio
means I have to do 30,000 physical reads and the 70% ratio only means
I have to read 3,000 blocks... so you can't even tell whether 97% is
"better" than than 70%.

Does that mean the BCHR is a useful indicator? Depends on your
definition of 'useful', I suppose. But if I ran into a building and
screamed "There is or isn't a fire! Evacuate the building or stay
where you are!!", I wouldn't expect others to find that particularly
useful!

Charles Hooper

unread,
Aug 21, 2007, 10:27:02 PM8/21/07
to
On Aug 21, 2:21 pm, DA Morgan <damor...@psoug.org> wrote:
> Bob Jones wrote:
> > 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.
>
> You seem to really invest yourself more fully in having an argument than
> you do in considering that the other person, in this case a member of
> the Oak Table, might actually know what he's talking about.
>
> Expert after expert in the Oracle database is telling you that you are
> wrong. And all you want to do is argue.
>
> No metrics to support you.
> No evidence of having read and understand what you are responding to.
> Just synapse-free mindless argument for the sake of argument.
> To what end?
> The hole you are standing in is already well over your head.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)

> Puget Sound Oracle Users Groupwww.psoug.org

This is an interesting topic. I was prepared to state that a change
in the buffer cache hit ratio might mean *something*. After a couple
days thinking about it, I thought I remembered reading an article that
stated that a decrease in the buffer cache hit ratio might actually be
a positive change... "Oracle Insights: Tales of the Oak Table" is an
interesting book. One suggestion in the book, in a section written by
Cary Millsap, suggested that fixing a poorly performing SQL statement
may/will likely cause a decrease in the buffer cache hit ratio, while
improving performance. If you have the book, read pages 159-161.
That section of the book is also available online:
http://www.apress.com/ApressCorporate/supplement/1/314/1590593871-2036.pdf

There is a more detailed write up by the same author, which can be
found here:
http://www.oradream.com/pdf/Why%20a%2099%20Cahe%20Hit%20Ratio%20is%20Not%20OK.pdf

A brief write up by Gaja Krishna Vaidyanatha:
http://www.quest-pipelines.com/newsletter-v3/0302_F.htm

A write up by Jonathan Lewis in 2001:
http://www.jlcomp.demon.co.uk/myths.html

Tom Kyte briefly mentioned the ineffectiveness of tuning based on the
buffer cache hi ratio in his "Expert Oracle Database Architecture"
book on page xii in the Forward. That section is available online
here:
http://asktom.oracle.com/pls/ask/z?p_url=download_file%3Fp_file%3D3962675907440142460&p_cat=5300FMcmp1.pdf&p_company=10

Just some additional food for thought in addition to what has already
been mentioned in this thread. Personal experience: when the previous
DBA left here 5+ years ago, the database was reporting a 99.9%+ buffer
cache hit ratio (this was the primary tuning statistic in use), yet
performance was poor. When that DBA left, I threw out Toad and the
buffer cache hit ratio and switched to the method suggested by Gaja
Krishna Vaidyanatha in his performance tuning book. The results were
impressive. It is nice to look at metrics, but it is often better to
find ways to fix problems that affect the critical business
applications - this is one of the things stressed in Cary Millsap's
book.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

hjr.p...@gmail.com

unread,
Aug 22, 2007, 1:46:45 AM8/22/07
to
On Aug 21, 2:24 pm, "Bob Jones" <em...@me.not> wrote:
> <hjr.pyth...@gmail.com> wrote in message

Well, you obviously have a closed mind on the subject, so it's now
pointless debating you in particular any further on the matter.

For anyone coming later, who has more of an inquiring (and logical)
view of things:

1. If the BCHR is useless, that says nothing at all about the
usefulness of other indicators. Asserting otherwise is equivalent to
saying, 'because I am allergic to penicillin, I must also be allergic
to aspirin". It simply doesn't follow that if one statistic is
useless, others must be too.
2. The point about 'useful' work was that BCHR cannot distinguish
between lots of hits because all your juicy data is being cached
nicely and lots of hits because you've got massive undo segment header
contention (for example). When, however, you measure buffer busy waits
on undo segment header blocks; or if you measure excessive waits for
the US enqueue, you've got very good evidence that your database is
encountering a poor undo configuration issue and a pretty good clue as
to how to go about fixing it. It's the specificity of the measure
that's at issue. Good statistics have focus. They don't conflate many
different ('useful' and 'useless') factors into one meaningless
number.
3. There are lots of performance stats in Oracle that are highly
focussed and very prescriptive. A high reloads/pins ratio in your
library cache tells you your library cache is too small, for example;
a poor parse/execute ratio tells you you should use more bind
variables in your code. And so on. The Oracle database is extremely
well-instrumented with highly-detailed statistics that permit one to
focus very closely on problem areas. The BCHR just happens not to be
one of them

Richard Foote

unread,
Aug 22, 2007, 9:16:30 AM8/22/07
to
"Bob Jones" <em...@me.not> wrote in message
news:aBuyi.50201$YL5....@newssvr29.news.prodigy.net...

Correct, we don't really need to care about any indicator that's as
ambigious as the BCHR.

However, response times is an idicator that isn't quite so ambigious and
hence is something you should care about ...

Cheers

Richard

Richard Foote

unread,
Aug 22, 2007, 9:37:02 AM8/22/07
to

"Bob Jones" <em...@me.not> wrote in message
news:kOtyi.50198$YL5....@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 ...

Do yourself a favour and read this
www.hotsos.com/e-library/abstract.php?id=6 ?

Cheers

Richard

Brian Peasland

unread,
Aug 22, 2007, 9:49:56 AM8/22/07
to
Richard Foote wrote:
>
> "Bob Jones" <em...@me.not> wrote in message
> news:kOtyi.50198$YL5....@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.
>

<<snip>>

This is what I've said for many years now. If you know your instance
typically shows a BCHR of X% and today it is showing a BCHR of Y% (where
Y is not in the neighborhood of X), then all you can say is that
something has changed. You cannot tell what has changed and you cannot
tell if things are better or worse. If X%>Y%, the only thing you can
positively state is that X%>Y% and that's it!

Richard Foote

unread,
Aug 22, 2007, 5:25:12 PM8/22/07
to
"Brian Peasland" <d...@nospam.peasland.net> wrote in message
news:46cc3306$0$16330$8826...@free.teranews.com...

> Richard Foote wrote:
>>
>> "Bob Jones" <em...@me.not> wrote in message
>> news:kOtyi.50198$YL5....@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.
>>
>
> <<snip>>
>
> This is what I've said for many years now. If you know your instance
> typically shows a BCHR of X% and today it is showing a BCHR of Y% (where Y
> is not in the neighborhood of X), then all you can say is that something
> has changed. You cannot tell what has changed and you cannot tell if
> things are better or worse. If X%>Y%, the only thing you can positively
> state is that X%>Y% and that's it!
>
>

And of course if X still equals Y, something could have changed making
performance better or worse as well, for all or some of your users.

Therefore whatever database checks one makes still need to be made
regardless of the BCHR.

Again, it's not much of an indicator ...

Cheers

Richard

Brian Peasland

unread,
Aug 22, 2007, 10:47:18 PM8/22/07
to
> And of course if X still equals Y, something could have changed making
> performance better or worse as well, for all or some of your users.

I've never thought of X=Y in this case..but now that you make me think
about it, you are absolutely correct! If X=Y things could still have
changed.

DA Morgan

unread,
Aug 22, 2007, 11:35:31 PM8/22/07
to
Brian Peasland wrote:
>> And of course if X still equals Y, something could have changed making
>> performance better or worse as well, for all or some of your users.
>
> I've never thought of X=Y in this case..but now that you make me think
> about it, you are absolutely correct! If X=Y things could still have
> changed.
>
> Cheers,
> Brian

Tom Kyte has done a spectacular job of finally, I think, killing of the
non-use of bind variables.

Perhaps finally killing of the practice of promoting hit ratios could
become the next foe he should subdue.


--
Daniel A. Morgan
University of Washington

damo...@x.washington.edu (replace x with u to respond)

sybr...@hccnet.nl

unread,
Aug 23, 2007, 1:24:12 AM8/23/07
to
On Wed, 22 Aug 2007 20:35:31 -0700, DA Morgan <damo...@psoug.org>
wrote:

>Tom Kyte has done a spectacular job of finally, I think, killing of the
>non-use of bind variables.

Yet everyone is still not using bind variables.
To be precise: I don't think my department maintains any database
where the app does use bind variables. Heck, even Oracle doesn't use
bind variables!!! (Should I refer to 'Oracle Applications' and
various other monsters?)
As everyone still subscribes to DKB's 'Silver Bullets'
Especially those developing 'database agnostic apps'.
Maybe you should relocate to the 'Real World'?
(Which of course means: anything outside the US of A ;)

DA Morgan

unread,
Aug 23, 2007, 12:35:51 PM8/23/07
to
sybr...@hccnet.nl wrote:
> On Wed, 22 Aug 2007 20:35:31 -0700, DA Morgan <damo...@psoug.org>
> wrote:
>
>> Tom Kyte has done a spectacular job of finally, I think, killing of the
>> non-use of bind variables.
>
> Yet everyone is still not using bind variables.

I agree. There are still people who can't figure out how to brush
their teeth in the morning too. But least there is no one arguing
it is unnecessary.

Brian Peasland

unread,
Aug 23, 2007, 7:37:30 AM8/23/07