Re: DB Buffer Cache Size

Prem K Mehrotra wrote:

> Let us say I have tuned PGA_AGGREGATE_TARGET and I still have RAM
> available.
> I would think increasing DB_CACHE_SIZE related parameters will help
> improve
> the performance of selects because physical disk i/o is reduced.

That's the whole problem. People think, but do not (a) either think enough or (b) test their thoughts out so that they can deal with facts rather than thoughts.

Even if we were to accept your statement at face value, you've totally ignored, forgotten or not realised that nothing in life comes free. As the cache increases in size, so the administrative overhead for Oracle increases. There comes a point where, for a particular 'unit' of hardware, the marginal cost of the increase in administrative overhead is greater than the marginal decrease in costs due to to less physical I/O.

If you buy me more CPUs and better hard disk arrays, as well as more RAM, I can probably push my caches bigger than before and not notice a cost being incurred. But if you just keep buying more and more RAM, and making your caches bigger and bigger, then you are going to overwhelm your existing CPUs with memory management issues, and kill DBWn with a workload he can't keep up with.

But I won't even accept your statement at face value, either. To say that increasing the buffer cache reduces physical disk I/O is simply another, slightly fancier, way of saying 'tune by getting a good buffer cache hit ratio'. Now the buffer cache hit ratio is known to be a lousy way to tune the buffer cache, and has been done to death here and elsewhere many, many times. Dress it up anyway you like, but your formulation simply seeks to re-instate it as being of some merit.

There are far easier and better ways of reducing physical I/O than piling on the RAM. Tuning your SQL is one of them.

> I
> thought reason Oracle
> provides 64 bit version, so one can take advantage of large memory by
> having large SGA's.

Look... if you *need* a large cache or a large SGA, then be my guest and configure one. And some people really, genuinely need massive caches, and for them 64-bit is truly required.

But so what? That doesn't mean we can all just throw in the towel and say 'bung more resource at it and the problems will go away'.

Do you honestly think that cache tuning comes down to making things as big as possible? If it were so, how in heaven's name has an entire industry been beguiled into paying considerable consulting fees to so many 'experts' for so long? Instead of shelling out a couple of grand to get Don in to tune for a day, why don't I just add a few noughts to the DB_CACHE_SIZE setting for free??

The reason we have experts is because the problem is a complex one. And no, just throwing more memory at the problem does NOT make it go away.

> People talk of main memory database where entire
> database can be
> placed in memory. The only way I know of doing that in Oracle is to do
> caching using db_cache_size related parameters.

People talk a lot of nonsense quite often, don't they?

> If I have an OLTP application where I do many inserts, deletes,
> updates in addition to selects, do you think performance of my
> inserts, deletes, updates will be reduced
> because of large db_cache_size.

No. Not necessarily. Quite often, absolutely the reverse will be the case. But the real point is: you cannot make sweeping generalisations like this. Particularly when the generalisation contains real *dangers*, not benefits.

HJR Received on Sat Aug 21 2004 - 23:37:04 CDT