very low hit ratio

8 views
Skip to first unread message

JW

unread,
Mar 24, 2004, 4:10:30 PM3/24/04
to
we have a data warehouse type of database and I noticed that buffer
cache hit ratio is very low: db block gets 37,173,962; consistent gets
349,994,093; physical reads 888,341,652. Is this normal in a
datawarehouse environment? Thanks.

Fred DENIS

unread,
Mar 24, 2004, 4:15:36 PM3/24/04
to
Dont care about hit ratio; the questions is "do you have performance problem
?"

Read this : http://www.oradream.com/pdf/LIOs%20vs%20PIOs.pdf and this
http://www.oradream.com/pdf/Why%20a%2099%20Cahe%20Hit%20Ratio%20is%20Not%20OK.pdf


Fred

"JW" <j...@nctr.fda.gov> a écrit dans le message de
news:f4d9400.04032...@posting.google.com...

Brian Peasland

unread,
Mar 24, 2004, 4:18:02 PM3/24/04
to

It could be "normal" or it could not be. It depends on what your end
users are doing. Are they running tools which let them perform ad-hoc
queries? If so, then they could be accessing data that never gets
accessed by others, therefore blocks in the buffer cache don't get
reused like they would in an OLTP environment. Then again, if they are
running some software and querying the same tables over and over again,
then your buffer cache might need to be bigger. The big thing here is to
look at how the users are accessing the data and try to determine how to
best meet their requirements.

HTH,
Brian


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

Brian Peasland
dba@remove_spam.peasland.com

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


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

Master-dba

unread,
Mar 24, 2004, 5:26:10 AM3/24/04
to
we are an very experienced senior export consulting shop:

we offer our special-tuning-package:

- economy : hit ratio 90% minimum
- professional: hit ratio 99% minimum
-export : hit ratio 99,999% mininum

our software packages runs round the clock and improves the hit ration of
every database

we offer also:

memory- compressor
cpu-upspeeder

your managment will be very happy about the savings achieved with
our management software

murks

senior-master-expert-principle DBA

"JW" <j...@nctr.fda.gov> schrieb im Newsbeitrag
news:f4d9400.04032...@posting.google.com...

Howard J. Rogers

unread,
Mar 24, 2004, 7:58:37 PM3/24/04
to

"JW" <j...@nctr.fda.gov> wrote in message
news:f4d9400.04032...@posting.google.com...

Who cares?

Are your users complaining about performance?

If not, then your hit ratio is just fine and dandy, whatever it happens to
be.

The more general point here is that, Don Burleson and TUSC notwithstanding,
the buffer cache hit ratio is an absolutely abysmal way of tuning anything.
It can sometimes offer a useful corollary to other statistics; to allow you
to distinguish between two otherwise equally plausible causes for, for
example, free buffer waits. But as a performance tuning goal in its own
right? Furgedaboudit.

Regards
HJR


Daniel Roy

unread,
Mar 24, 2004, 10:45:23 PM3/24/04
to
Mr. "senior-master-expert-principle DBA",
could you please do the world a favor by vanishing forever.
Serious Oracle discussion groups such as this one are not the place
to:
1) Advertize your services
2) Especially when the services you offer are based on the absolutely
ridiculous goal of improving the hit ratio, without looking at
anything else in the database!
The OP clearly stated that he's dealing with a DATA WAREHOUSE
(i.e. full table scans all over the place). To let him think that his
problems are going to be solved with a better hit ratio has no more
credibility than the infomercials at 3 in the morning. The hit ratio
tuning methodology has been proven for years (since Oracle 7, I think)
to be a waste of time, and money (to improve your hit ratio, just go
buy more memory!)

Daniel

Hans Forbrich

unread,
Mar 25, 2004, 1:00:28 AM3/25/04
to
Daniel Roy wrote:

> Mr. "senior-master-expert-principle DBA",
> could you please do the world a favor by vanishing forever.
> Serious Oracle discussion groups such as this one are not the place
> to:
> 1) Advertize your services
> 2) Especially when the services you offer are based on the absolutely
> ridiculous goal of improving the hit ratio, without looking at
> anything else in the database!

Since there was absolutely no contact information, I suspect our
"senior-master-expert-principle DBA" was havin' a bit-o fun.

(BTW, I just noticed that re-arranged it becomes an expert-PMS DBA. Take
that any way you wnt <g>)

/Hans

JW

unread,
Mar 26, 2004, 4:21:09 PM3/26/04
to
Thank you all for offering your opinions. Users have not complained
about the performance. I was just checking around to prevent any
possible complaints. I guess I will just talk to users and developers
and see how the application is accessing the database and then decide
if the low hit ratio is supposed to be like that.

"Howard J. Rogers" <h...@dizwell.com> wrote in message news:<40622ec2$0$8357$afc3...@news.optusnet.com.au>...

Howard J. Rogers

unread,
Mar 26, 2004, 4:57:11 PM3/26/04
to

"JW" <j...@nctr.fda.gov> wrote in message
news:f4d9400.04032...@posting.google.com...
> Thank you all for offering your opinions. Users have not complained
> about the performance. I was just checking around to prevent any
> possible complaints. I guess I will just talk to users and developers
> and see how the application is accessing the database and then decide
> if the low hit ratio is supposed to be like that.


Concentrate on waits rather than ratios.
Make sure you're not using bind variables (you're a data warehouse after
all)
See whether you can reduce the number of physical reads by, for example,
denormalising things (think materialised views, index clusters) or by
partitioning things (assuming partitioning is an option).
Assess whether the developers have correctly implemented 'exotic segments'
such as Index Organised Tables or hash clusters or bitmap indexes. If 9i,
see whether your developers have heard of bitmap join indexes.
Depending on your version, you might be able to reduce physical reads by
implementing index and/or table compression.

That sort of thing.

The one thing that's not in the list is 'worry about my hit ratio'.

Regards
HJR

Harry Sheng

unread,
Mar 28, 2004, 8:23:53 PM3/28/04
to
I get a data buffer hit ratio of more than 90%. The users still yell at me
that the database is not responding !
So it really depends. If your users do not yell at you, forget about the
hit-ratio and have a good sleep.

"JW" <j...@nctr.fda.gov> wrote in message
news:f4d9400.04032...@posting.google.com...

Carl Kayser

unread,
Apr 16, 2004, 8:55:53 AM4/16/04
to
I don't know about DW (and I'm primarily a Sybase DBA) but 90% hit ratio
means a 10% miss ratio which I do not consider to be good. Consider chapter
7 of Alan Packers book which is available (along with the TOC) online at

http://www.amazon.com/gp/reader/0130834173/ref=sib_dp_rdr/104-8369762-3779959#reader-page


"Harry Sheng" <harrys...@hotmail.com> wrote in message
news:AUK9c.5955$1A6.2...@news20.bellglobal.com...

Niall Litchfield

unread,
Apr 16, 2004, 9:31:30 AM4/16/04
to
"Carl Kayser" <kays...@bls.gov> wrote in message
news:c5ol4r$b39$1...@blsnews.bls.gov...

> I don't know about DW (and I'm primarily a Sybase DBA) but 90% hit ratio
> means a 10% miss ratio which I do not consider to be good. Consider
chapter
> 7 of Alan Packers book which is available (along with the TOC) online at
>
>
http://www.amazon.com/gp/reader/0130834173/ref=sib_dp_rdr/104-8369762-3779959#reader-page
> >

I'm not convinced - but then since I consider hit-ratios to be misleading
perhaps I am biased.

This chapter talks about not setting a target, about concentrating on the
miss ratio to reduce physical reads. However at no point does it consider
whether the workload is optimal, nor does it mention - except in passing -
the end-user response time. If halving the miss ratio from 10% to 5% results
in a 0.05% change in response time what is the point. What happens if
halving the miss rate results in much *increased* response time -
inappropriately choosing indexed access paths will frequently do this.

It also begs the question what do you consider to be 'good' and why.

By contrast a response time focussed approach can ask of the end-users, or
the business management, questions like how long should it take to enter an
order/how many orders should we process per day, what should our batch
window be in hours. Much more productive.


--
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/


Howard J. Rogers

unread,
Apr 16, 2004, 12:38:36 PM4/16/04
to
Carl Kayser wrote:
> I don't know about DW (and I'm primarily a Sybase DBA) but 90% hit ratio
> means a 10% miss ratio which I do not consider to be good.

Why?

What's more important? That you get a hit and make your ratio look good;
or that your queries are efficient and don't need to access that block
at all?

A ratio without a workload is nonsense; a meaningless average. And it's
far more important to tune the workload than it is to worry about
particular numbers on a ratio.

> Consider chapter
> 7 of Alan Packers book which is available (along with the TOC) online at
>
> http://www.amazon.com/gp/reader/0130834173/ref=sib_dp_rdr/104-8369762-3779959#reader-page

I read it, and I note that even he says he hasn't a clue what the
"right" number for the buffer cache hit ratio should be, since three
different tuning books mentioned three different values. He then pulls
off quite a clever stroke, which is to concentrate on halving or
quartering the miss ratio... but however neat that is, it's just a
writer's trick that has you concentrating on improving the hit ratio at
the end of the day.

He also hints, but doesn't dwell on, the fact that the degree of index
access versus full table scans affects the ratio. The skewness of your
data affects the ratio. And, at one point, he comes right out and says it:

"The objective of monitoring the buffer cache hit ratio is not to
achieve some arbitrary number; it is to optiize performance by finding a
balance between memory consumption and disk I/O"

...which is one of the more intelligent statements on the subject I've
seen. Based on that statement, how can you -or anyone- say "10% miss
ratio... I do not consider that to be good"??? You've just elevated some
"arbitrary number" to exalted target status, and just lost site of the
'balance between memory and disk I/O' goal. Which is the perennial
danger of simplistic ratios.

What your author doesn't go on to say, perhaps because his book is a
generalist one, aimed at the Sybase and DB2 markets as well as the
Oracle one, is that there are *better* ways of monitoring this balance
between memory consumption and disk I/O in Oracle than a simplistic
ratio. If the balance is skewed badly and ill-advisedly towards disk
access, you will know all about it in Oracle because you'll suffer from
measurable free buffer waits, for example. So if you tune free buffer
waits out of your database, you can quite comfortably state that disk
I/O is not a problem without ever one having to calculate a buffer cache
hit ratio.

Oracle's wait events interface is a rich source of detailed and specific
tuning information that cannot be fudged in the way a ratio can. And
that's why it's far more sensible to concentrate on eliminating waits in
Oracle than it is to spend time worrying about a largely meaningless ratio.

Regards
HJR


Carl Kayser

unread,
Apr 17, 2004, 2:31:49 PM4/17/04
to

OK, OK - Niall & Howard. I made a lousy statement which is objectively
incorrect. In my environment I usually get a 98-99% hit ratio and that does
not mean, as both of you have pointed out, that it is a good target for
every system. I agree that there is no single measure that provides a
magic bullet. A high hit ratio value can be obtained by repeated table
scans in
memory by a poorly constructed query or queries. (I usually detect this by
a big jump in the number of hits.)

In the post that I was responding to:

"I get a data buffer hit ratio of more than 90%. The users still yell at me
that the database is not responding ! So it really depends. If your users
do not yell at you, forget about the hit-ratio and have a good sleep."

My take was that he was inferring "I get a high hit ratio and therefore the
buffer cache is adequate." I disagree with that position (if that was what
he
was inferring). Unfortunately, my posted response was very, very different
from my intent.

p.s. I was wondering if your comments were based upon the online chapter or
the whole book?


"Howard J. Rogers" <h...@dizwell.com> wrote in message

news:40800c10$0$25657$afc3...@news.optusnet.com.au...

Howard J. Rogers

unread,
Apr 17, 2004, 7:35:58 PM4/17/04
to
Carl Kayser wrote:

> p.s. I was wondering if your comments were based upon the online chapter or
> the whole book?

Merely the online chapter, I'm afraid. Actually, I've never even heard
of the author before. But as I say, he has some rather intelligent
comments to make about the hit ratio (my only grumble is that, for
Oracle, it would be even more intelligent not to mention it at all!),
and I was overall impressed, albeit on a very thin basis of just one
sample chapter.

Put it this way: unlike certain other authors that could be named, he
*doesn't* say that 'if you can increase the buffer cache hit ratio from
95 to 98%, your database will perform 400% faster' (though I suspect
that your author's reference to seeing "98%" advised in one or more
performance tuning books is a direct reference to that lunatic assertion).

Regards
HJR


Reply all
Reply to author
Forward
0 new messages