Re: 9i multi cache buffer

"Geoff Ingram" <geoff_at_dbcool.com> wrote in message news:84d1c3a9.0211150140.31bd2f6e_at_posting.google.com... [snip]
> TPC-C gives it a certain credibility.

We'll agree to disagree then. TPC-C shows how you can achieve a good TPC rating. If I threw vast amounts of memory at a database, disabled logging, never checkpointed and so on, would you say I'd tuned that database?

> > >This
> > > was achieved using multiple buffer caches:
> > >
> > > db_cache_size = 3500M
> > > db_8k_cache_size = 512M
> > > db_16k_cache_size = 3000M
> > > db_keep_cache_size = 37750M
> >
> > Just hold on right there. In this "real world" test, it would appear we
have
> > getting on for 43 GIGAbytes of RAM in use. Mmmmm. This is not going to
be
> > telling us an awful lot that is of any relevance.
>
> ??? Show me any Oracle app that doesn't want to keep as much data
> cached as possible. What's wrong with that? If you had $400K to spend,
> wouldn't you do the same to get best performance. As you probably
> don't have $400K, you would use a smaller cache.
>
> Why are you overlooking the other caches:
>
> db_8k_cache_size = 512M
> db_16k_cache_size = 3000M
>
> Either these are wasted (your view?) or they benefit performance.

Er, 43 gigabytes includes those other caches. Or maybe my maths is just poor.

>

[snip]
>
> Of course that's not the only goal in the real world which is just as
> well.
> You probably wouldn't choose redo logs that are several GB in size.

Actually, I would. ;-)

> It's disappointing that you consider that to be "fairy-land",
> considering some of the very best Oracle performance engineers work on
> that stuff, each TPC-C result takes several months end-to-end, and
> typically costs OEMs > $1million. Personally, i've learnt a huge
> amount from reading about TPC.

No-one questions the competence of those putting the database through the benchmarks. That's not the point.

[snip]
>
> Net result: the DBA obsession with blocksize/buffersize resulting in
> poor I/O is a myth a lot of the time, especially for storage used by
> most large commercial organizations these days.
>

There's a qualification in there. "A lot of the time". IE, not always. We can agree to disagree on the balance. I wouldn't rule out adopting a different blocksize *after* trying the alternatives. It's a tool that we now have, and like all tools should be used where appropriate. I just think (and my testing indicates) that it's just not very appropriate very much of the time.

My troubles start when Oracle introduces a new feature (such as multiple blocksizes or ASSM) which then get touted as the latest cure-all and hence get implemented without due consideration.

There are good physical reasons for adopting the "right" blocksize. There may be application-specific reasons for wanting to vary that at the margins. That doesn't mean any old blocksize is now "right". That's all.

[snip]

Regards
HJR Received on Fri Nov 15 2002 - 12:59:43 CST