Cache Hit Ratio from system views
Teresa Masino
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
Which release of Oracle is this?
David Fitzjarrell
Teresa Masino
>
> - 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
>
> - 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
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
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
> 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
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
> 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
<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
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
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
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
> Niall Litchfield wrote:
> 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
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
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
>>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).
>
That is a common mistake of "savy professionals" - overlooking the basics
and thinking they know better.
sybr...@hccnet.nl
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
>>>>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
> 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
>> 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
>>> 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
>>> 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
<sybr...@hccnet.nl> wrote in message
news:30p7c39h7e4nj7t1b...@4ax.com...
Do I really need to respond to this moron?
Bob Jones
"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
> <sybra...@hccnet.nl> wrote in message
> > 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
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
<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
>> 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
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
"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
> "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
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
"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