Re: MTS and Memory

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 11 Sep 2004 08:27:40 +1000
Message-Id: <41422a69$0$6954$afc38c87@news.optusnet.com.au>

ctcgag_at_hotmail.com wrote:

> hjr_at_dizwell.com (Howard J. Rogers) wrote:

>>
>> But in any case, that doesn't get you off the hook. You asked a
>> generic question about how far you can 'bump up' your SGA. You were
>> told, I think, that the official recommendation from Oracle is that
>> the SGA should never consume more than 50% of available physical RAM.

>
> I've heard this many times here, but I've not seen it in Oracle
> documentation.

I have, but I can't remember where. Sybrand used to say it all the time, so one day I checked. I will have another hunt around (actually, I think it might have been said on Metalink).

> So I spent more time than I'd care to admit on tahiti
> searching for their SGA sizing recommendations (9.2), and I couldn't find
> such advice. Usually they just say that it should be set "appropriately"
> (Gee thanks, guys.) or they tell you to go look in some other document
> (which of course just refers you to yet another document). The most
> specific advice I could find is that it should not be so large as to cause
> swapping.
>

>> You were also told (by me) that unless you are suffering lots of
>> ORA-4031 errors (indicating that you are running out of memory in the
>> shared and/or large pools) you don't (probably) need to increase the
>> size of your SGA (specifically the large or shared pools) in the first
>> place.

>
> If there are excessive disk sort or hash operations, would that also
> indicate that more memory is needed?
> IOW, if I can't grab my
> sort_area_size or hash_area_size worth of memory from the large pool,
> would I throw an error or would I make do with less memory and go to disk?
>
> Xho

That is an excellent question, and one I hesitate to answer without testing it. The nice thing about the large pool is that it is binary in nature: either the free memory is there, or it isn't. It doesn't, in other words, age things out to make room for new things, as the shared pool does.

But, the sorting business is such that if you can't find the memory you want, you swap to TEMP.

So, my initial thought is that, yes: the ratio of disk to memory sorts would worsen.

But it all gets a bit more difficult to predict when the pga_aggregate_target is in town.

Maybe Jonathan can enlighten us?

Regards
HJR Received on Fri Sep 10 2004 - 17:27:40 CDT