Re: DB Buffer Cache Size

> Mr. Burleson, am I right in saying that when you can reduce the I/O of
> a particular statement to 10 percent by simply tuning the statement,
> you shouldn't do that, but instead you should increase the buffer
> cache?

Please, call me Don! No, of course not! Tuning the SQL is always the best remedy, and by reducing unnecessary consistent gets you often reduce PIO too! But it makes sense to me to tune at the system-level b y adjusting CBO parms (OIAC and optimizer_index_caching) and by improving the CBO statistics:

http://www.dba-oracle.com/oracle_tips_cost_adj.htm

http://technet.oracle.com/oramag/webcolumns/2003/techarticles/burleson_cbo_pt1_pt2.html

> If so, would you please explain why I have a database running at a
> BCHR of 99.9 and performance is still abysmal?

Easy. Too many LIO's (sub-optimal SQL) or high library cache contention (excessive parsing).

Ah, but you miss this point. Up to the point where the marginal benefit of adding blocks to the buffer declines (the second derivative of the 1/x curce of buffer utilization), tuning with BCHR makes sense.  Beyond that point (where the working set is cached), I agree with you completely.

http://www.dba-oracle.com/art_builder_buffers.htm

Have you looked at 10g AMM? What do you think of it?

> How would you resolve that, simply crank up memory further so I can
> cache the entire database, knowing the database runs on Win2k.
> You are not selling that philosophy to others, don't you?

No, not at all. I simply advocate tuning at the system-level FIRST!

On the other had, I never had a problem throwing hardware (cache, faster CPU) at a messed-up system. My client's often demand it because they don't want to pay a fortune for SQL tuning. In many cases it's faster and cheaper. It makes sense.

There are many system-level "silver bullets" to Oracle tuning, and IMHO you would be foolish not to try them. For example, if you could tune 500 DSQL statements by adding an index or building an MV, why not? Consulting client demand that you tune the whole system before tuning individual SQL statements:

http://www.orafaq.com/articles/archives/000027.htm

However, this will be a moot issue in 24 months when SSD makes those stupid magnetic platter obsolete. Once RAM-Disk is as cheap as platter disk we will need to find something else to debate!

> 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'?

Actually my personal slogan is "Anything worth doing is worth doing to excess", but hey, that's just me! Take care . . . Received on Sun Aug 22 2004 - 09:28:49 CDT