Re: DB Buffer Cache Size

Thank you, Howard. You are also correct, and I will attempt to modify my "minimal" to mean "optimal." :-)

In article <41263e99$0$25143$afc38c87_at_news.optusnet.com.au>,  "Howard J. Rogers" <hjr_at_dizwell.com> wrote:

> 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.

This was the first issue that came to my 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.

Yes, I believe that is what he was saying. I take full responsibility for misstating his meaning. That was the primary reason I did not wish to give his name. :-)

> 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).

Again, you are right on target. I suspect that one or more of my colleagues is going to be called in to help this company tune their database. When I proposed to them the possibility that TOO MUCH memory was being allocated to the buffer cache, perhaps sacrificing some efficiency in other arenas, I was treated somewhat like a pariah.

> > 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.

Absolutely. And using OWI is one of the primary ways of determining this. Again, my interest in this is more academic than anything else. When I was first presented with this scenario, my mind went "whoa..."

> > I am interested in hearing experts' opinions pro or con or otherwise on
> > this question. TIA.
> >
> > Fred
>
> HJR
Received on Fri Aug 20 2004 - 16:06:15 CDT