Re: 3GB RAM usage by Oracle

Hi Don,

Comments embedded

"Don Burleson" <don_at_burleson.cc> wrote in message news:998d28f7.0408271627.660a0740_at_posting.google.com...
> "Richard Foote"
>
> > No, there's a third solution that's actually used by most shops.
> >
> > Have a DBA who actually knows *how* to tune a database and allocate
memory
> > *appropriately*. Requiring AWE is rarely required and suggesting that
memory
> > should simply be used because it's there, especially with the
*significant
> > overheads* that AWE entails is poor advice even by your standards.
>
> Believe it or not, most USA shops already have tuned their SQL and
> their high physical I/O is due to not enough data cache. Things must
> be different where you live.
>

Are you seriously suggesting when a site experiences performance problems in the US, it's never the result of SQL issues ? That it's never the result of a whole multitude of other problems that won't be remedied by adding extra memory ? Are you seriously suggesting that you tune databases by simply adding memory, "ALWAYS with great results" (quote) ?

Gee, you certainly have it easy in the US !! However, I don't believe you for one moment (which raises other suspicions).

> Besides, it's now easy with v$db_cache_advice and 10g AMM to see the
> marginal benefit of adding RAM. Are you suggesting that AMM
> recommendations are nonsense?

I think the key word here is "marginal". Oh, and by the way, yes I have performed research on the cache advisor in 9i and yes I do believe the "advice" is suspect in many configurations. We took snapshots of projected physical reads and compared with actual physical reads under similar loads and the results were disappointing. Way out. In fact, we detected an almost linear forecast of physical reads to % of buffer increase/decrease, not particularly sophisticated or accurate. Adding/subtracting 30% made practically no real difference in physical reads while the forecasts suggested otherwise. I've only just begun testing on 10g so we have to wait and see if things have improved.

>
> It's not uncommon to see working sets of frequently-referenced data of
> for than 30-gig for a large database. AWE is a great techniques for
> 32-bit Windows databases and I do it for dozens of databases every
> year, ALWAYS with great results.

"It's not uncommon" means that it's common. So are you now seriously suggesting that's it's common to have buffer sizes in excess of 30G ? . Are you seriously suggesting such configurations on 32bit windows with AWE ? Again, I don't believe you for a moment. Please some examples, love to hear them. And let's ask everyone out there in Oracle Land, both in the US and elsewhere, on windows or on other platforms; how many people have buffer caches set to 30G+ ? Please folks, let us know. Might ask the same question on the Oracle L-list. Let's see if it's truly "not uncommon" !!

BTW, have you worked out yet how to calculate the size of the buffer caches in 9i ?

>
> Also, you ignore the economic reality of database tuning. Time and
> time again, it's too costly (in both time and money) for a shop to
> tune their SQL. It's also an admission that the IT manager hired
> goofballs (usually a non-US provider) to write the original code.

Don, you ignore the common sense and basic scientific principles associated with database tuning. Again, let's go back to my experience 18 months ago. 1/2 the memory, no hardware upgrade, 2 days of "real" database tuning effort. And you consider the results "uneconomic" ? Oh please !! Fortunately my client didn't see it as being uneconomic ...

And let's go back to the OP of this thread which you replied to. DGK said "Now I have tuned few of the SQL statements and found that response time has increased". And you reply that he's "wasting RAM" and that he has two (crappy) solutions !!

You *still* suggest increasing memory and inflecting the system with the significant overheads of AWE (which you conveniently ignore again) even though DGK clearly suggests he has no current performance issues. *They have been resolved by fixing the root cause of the performance problem*. No extra memory required. Hope DGK doesn't come from the USA !!

Don, I posted here (in Jan 2003) the steps required to set-up AWE with a few more warnings than you're inclined to include. When we benchmark it (yes, we like to test things before chucking in more memory), we found the performance awful under load with CPU usage in particular sky rocketing, with the thing being totally unscaleable. This is why I *know* the dangers and stupidity associated with "not wasting RAM".

Don, that's the problem here. You suggest "not wasting RAM" on 32 bit windows without appreciating the dire dangers of such global advice. It's a nonsense piece of advice, one of your worst (and that's saying something).

> Like I said, I have no problem throwing hardware at crappy code when
> the client doesn't want to tune it.

Wait a minute, didn't you just say that in the US, shops already have tuned SQL ? The USA doesn't have "crappy code" !! Don you're contradicting yourself (again). The problem with "throwing hardware at crappy code" is that it's often not successful, for the simple reason that the fundamental problems are still there. You have a "lumpy carpet" that potentially costs a lot of additional monies and resources to implement that eventually has to be dealt with anyways. And this is economic ? Don, for your sake, I hope no clients of yours read this thread, they would be shocked.

Don, I crown you king of Method "D". Hail the king of Method "D" !!

Cheers ;)

Richard Received on Sat Aug 28 2004 - 05:59:48 CDT