Re: DB Buffer Cache Size

ImPrecise wrote:

> This is a silly question.
>
> Is there a point where the DB Buffer Cache can be sized too large?

Absolutely. A cache has to be managed. Make it too big, and management becomes all you do, and never mind serving up data.

Rather more technically, the bigger your cache, the larger your LRU and Dirty List becomes. That means you can spend longer looking for a free buffer (giving rise to a Free Buffer wait). And you can build up so many dirty buffers to be flushed that poor DBWn can't cope with the load.

There are other issues, but those two spring immediately to mind.

> One
> of the Oracle "luminaries" (who will remain nameless in this post) I
> used to speak with was somewhat adamant that proper SQL tuning, data
> placement, and SGA tuning would thus result in a reduction in the size
> of the data cache.

A bit of an ambitious claim without any context to it. But I suspect what he meant was, instead of throwing oodles of memory at the cache in the hope that your performance problem goes away, tune properly. The net result will be much less memory wasted on an over-inflated cache.

Look at the countless posts here over the years: "How do I go about caching ALL my tables and making sure they stay in memory"... there are a lot of people out there that assume bigger must mean better and throw memory at it accordingly.

> He would maintain that Oracle9i's new features (such
> as PGA_AGGREGATE_TARGET, WORKAREA_SIZE_POLICY, V$DB_CACHE_ADVICE, an
> entire Oracle SGA can be kept to a minimum, workable size.

That sentence doesn't make sense, so I don't know what you were saying for sure. If you were saying that the new features you list help you achieve an optimally-sized cache, I wouldn't disagree with that. (Incidentally, can we please use words like 'optimal' rather than 'minimal'. Tuning isn't about having a tiny cache, just as it isn't about having a huge one. It's about finding what sized cache is actually right for the demands you are going to place upon it, whether that means it ends up being big or small).

> Okay, maybe I am simplifying the case a bit. What got me to thinking
> about this was the way a customer had his instance configured. This was
> an Oracle 8.1.7.4 database, with over 5 terabytes of online storage.
> The SGA had been configured with several gigabytes of data cache. As I
> understand it, this is a financial system, not OLTP. I was just
> wondering if perhaps the database had been OVER-sized due to the
> perception that so much data would be better handled by a large buffer
> cache.

Sounds as though it could possibly have been. But there's no basis to tell from what you've written. We'd need to see his wait events before knowing. Which is how any DBA can tell, any time, for any instance.

> I am interested in hearing experts' opinions pro or con or otherwise on
> this question. TIA.
>
> Fred

HJR Received on Fri Aug 20 2004 - 14:10:28 CDT