Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DB Buffer Cache Size

Re: DB Buffer Cache Size

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 24 Aug 2004 10:32:07 +1000
Message-ID: <412a8c87$0$8833$afc38c87@news.optusnet.com.au>


Joel Garry wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:<4128f1c0$0$3928$afc38c87_at_news.optusnet.com.au>...

>> 
>> It's funny: the Oracle Performance Tuning course has for years been
>> drumming into people (correctly, IMO) the order of events: Design,
>> Application, Memory, I/O, Contention, Operating System. Any other order
>> of events is likely to result in 'loop tuning', where you fix problem A,
>> move onto problem B, and find that fixing problem B has re-blown problem
>> A.
>> 
>> No doubt it's lucrative for the consultant. But it's not efficient.

>
> It's not efficient if you are looking at the whole life-cycle from the
> beginning, but that is not the problem being solved when you go in to
> fix a messed-up system, is it?

See my reply to Niall. It makes no difference. Even a system that's already messed up needs fixing in a *methodical* manner... not just dive in all over the place, correcting anything that happens to come to mind in the order they happen to come to mind. There has to be a sensible starting point, and that's got to be the application design every time.

> The efficiency must be measured from
> the correct starting point, and Design is not the starting point in a
> running system.

Again, see my reply to Niall. Yes, the design IS the correct starting point, even in a shrink-wrapped application, because one needs to know the efficiency of the design you're working with before starting to come to meaningful conclusions. Is the problem bad SQL? Is it lack of indexes?

If you determine the design has implemented hundreds of foreign keys but not indexed them at all; and if you further determine that the application code never just updates one field at a time, but updates on a row-by-row basis, you have just determined that it is pointless trying to throw more memory at your buffer cache or your shared pool. Your tuning issue is exclusive locking occasioned by unindexed foreign keys and primary key updating on parent tables. By studying the design and the code, you have eliminated many possible frustrating hours of trying to find a magic parameter to set to make the issue go away.

That's a simple example, of course. But it's indicative nonetheless.

On another level, whilst you may not be able to alter what the primary keys have been declared as; or re-write the code; you may nevertheless be able to contemplate partitioning or creating some materialised views or setting cursor_sharing=similar to rectify or alleviate the problem. The vendor may not approve, of course. But then again, he may. But at least you know what questions to ask the vendor.., and that can only be because you assessed the quality of the design and the code.

Or, finally, suppose you notice that every table in your application has actually been implemented as an IOT... with heaps of secondary indexes on them all. Suddenly, you've just explained why the application seems to enjoy doing large table scans all the time... the secondary indexes keep getting out of date. So you know how to improve performance on that application: rebuild (or refresh them, in 9iR2+) your secondary indexes on a regular basis to keep them accurate. To me, that's another example of how a look at the application design tells you how to tune -and that one happens to come from a real example.

"Design" in that post of mine, in short, doesn't mean "How would I design this application if I were writing it from scratch". It means: consider the efficiency of the design you're stuck with. Knowing where even a set-in-concrete design is INefficient has got to be your first port of call.

> If your tire goes flat in the middle of Wisconsin do
> you ship the Nissan back to Japan? How efficient is that? (Some
> Nissan sports cars have an apparent design defect that causes
> premature wear on tires).

The art of using analogy is to pick an appropriate one!

If your tyre is flat, it helps to know it's the tyre that's the problem and not your cylinder head or distributor cap. Not much point me getting the grommet-levers and flupblingers out when actually all I needed was a car jack.

> It's not pretty cavalier to spend even more money doing a "correct"
> fix?

I simply don't understand the basis of the question. I offer you a bodge-job that fixes nothing, but makes things appear OK on the surface for $10,000; and a comprehensive, thorough and "correct" fix of the underlying issues for $15,000; and the gist of your question implies you'd pick the $10,000 option?

That just doesn't compute for me. You go for the right fix every time. Or ought to. Whatever the "right" fix happens to be -and sometimes it will indeed be additional hardware resource. But as a 'reflex' reaction to a tuning issue in the way Don implied (or seemed to) it should be? You can't be serious, surely?

> Speaking as someone who deals more with the low end, I have to agree
> with Don, when costs are evaluated, quick-and-cheapness wins. I agree
> with you, the view should be long term, cheapness has its own costs,
> but the US reality is quarterly accounting. Efficiency for its own
> sake is not necessarily an ideal solution.

The term "efficient" was shorthand for something which actually addresses the issues appropriately and provides a fix which is reasonable, durable and affordable.

Buying new hardware when it is not actually required strikes me as an unreasonable (as in, irrational) thing to do. It may not fix the problem up in the long-term, and is therefore not actually truly affordable.

Even the lowest end of town must known that 10K today, plus 10K next year, plus 10K the year after is not a 'quick-and-cheap' alternative to paying 15K-right-now-and-that's-all.

Regards
HJR Received on Mon Aug 23 2004 - 19:32:07 CDT

Original text of this message

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