Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: DB Buffer Cache Size

From: Paul Drake <>
Date: 22 Aug 2004 17:43:50 -0700
Message-ID: <> (Don Burleson) wrote in message news:<>...
> > Or might that be the reason why the performance of US commercially
> > available software (I won't mention vendors) is often so abysmal, and
> > the only thing you can do is throwing memory at the problem?
> > Because your fellow Americans actually subscribe to your 'More is
> > better Religion'?

Hey - I think that Moore is better. Does that make me a US idiot? :)

> Actually my personal slogan is "Anything worth doing is worth doing to
> excess", but hey, that's just me! Take care . . .

horseshoes and hand-grenades?
duct tape?

once you have identified the current bottleneck, recommended a remediation, tested, implemented it, its it no longer the bottleneck. I can see allocating say a 5-10 percent excess so that it does not become the bottleneck again immediately, but beyond that, I see increasing that resource to excess as a waste.

"In excess" is certainly what one would want to use in a chemical process where the most expensive component is completely converted in the main reaction, one would not likely want to be wasting that component in the purge stream. But there is still overhead on having to process the excess of other reagents, separating them, returning them into the reactor feed.
Why size the reactor to carry 5 times what it needs? Why dilute the concentration of the main reagent with excess of the other components?

HJRs point about finding an "optimal" solution - with derivatives if your data is good enough that you can fit a spline to it - with graph paper if your data points aren't really good enough for a fit - to me is inherently more attractive than taking a backgammon doubling die out, rolling it and hiking the cache by the lucky number that is facing up.

If a system has a bottleneck, the bottleneck should be fixed. If performance is acceptable after step, with a sufficient amount of breathing room (as specified in the scope of work) you're done.

If there is still more time left on the consulting assignment such that a sensitivty analysis can be performed (brush up on your linear algebra - again, marginal gains being important here) then great, do so. But if your objective function is not well-behaved, and you shoot right past your potential optimal solution and down into the next valley - you won't have fixed the problem of overall system performance. You'll simply have removed one bottleneck and allowed for the next one to take over.

no one is asserting that sometimes there isn't a case of low-hanging fruit of default parameters still being in the init.ora (no onsite DBA) - where a small number of changes could have a tremendous benefit.
Sometimes, you get lucky and find that one statement executed 3 times for > 1 terabyte of logical IOs. If you change the code, great. Sometimes there isn't time or a budget for a code re-write, or the code change is going to take 3 months to get through development/qa, and in the meantime, performance is not acceptable. So you reach for the duct tape.
One might allocate sufficient memory to the keep pool, alter tables to the keep pool,
alter tables as cache and cache the tables until the code change is available.
Reducing the phyiscal IOs did nothing to remove the logical IOs.

Maybe increasing the shared pool to something larger will kick in additional child latches (9.2) and it really isn't the size of the shared pool that is reducing the pain of parsing, its the distribution of it across a parent and 3 child latches, that provides some overall benefit, even though much more garbage SQL is being stored and managed in memory that need not be there.

The problems with duct tape are that it is ugly and when you attempt to pull it off, it leaves behind a nasty adhesive residue - and it might take with it some of the materials of the original structure.

As a professional, isn't it fundementally more satifying to isolate the root cause, target it, properly fix it and declare victory?

-bdbafh Received on Sun Aug 22 2004 - 19:43:50 CDT

Original text of this message