Re: 10G - editing SPFILE

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 21 Oct 2004 21:19:09 +1000
Message-Id: <41779b25$0$25924$afc38c87@news.optusnet.com.au>

Holger Baer wrote:

> Howard J. Rogers wrote:

>> Holger Baer wrote:
>> 
>> 
>> 
>>>In addition to what HJR said, lowering your sga_target changes nothing
>>>with regard to the memory that gets allocated on instance startup.
>> 
>> 
>> I don't think that's quite right, Holger. Not entirely, anyway. Here's a
>> quick test:
>> 

> [...]
> Nice demonstration and thanks for that. I didn't realize you could reset
> sga_max_size, which effectivly puts it to a value identical to sga_target.
> What I was meaning though, is what you describe in the next paragraph:

>> 
>> So the "Total System Global Area" matches the 132M of the MAX_SIZE and
>> not the 64M of the TARGET.

>
> Exactly. The "Total System Global Area" is more or less the size of the
> shared memory segment that get's allocated at instance startup according
> to the value of sga_max_size. You can see that using ipcs. If you reset
> sga_max_size, the value of sga_max_size is effectively set to sga_target.
>

>> 
>> I find that confusing myself, since I would have thought TARGET was what
>> you would originally get allocated, and MAX_SIZE just sets a ceiling on
>> what you can expand to, dynamically, over time.

>
> I'm not a system programmer, but I guess that resizing a shared memory
> segment is either not possible or nothing you wan't to do in a hurry. And
> allocating several different sized shared mem segments is understandably
> also not desireable.
>

>> 
>> I've still to get my head around all this 10g automation, I guess!
>> 
>> It does mean that your original statement is likely to be practically
>> true for the OP. But as well as lowering his SGA_MAX_SIZE, he could
>> abolish it altogether to allow the TARGET to kick in.

>
> Ok, once again, I've learned my lesson :-) I was under the impression,
> that you had to specify sga_max_size and set or leave sga_target at your
> leisure, not the other way round. At least, that's how I understood it
> from the 10g New Features Seminar.
>
> Cheers,
>
> Holger

My understanding from SGA_MAX_SIZE's first appearance in 9i is that it is a conservative parameter: if it's not explicitly set, then it defaults to whatever the explicitly set various pools and caches actually add up to (giving you no 'growth room', because you're already at your allocation ceiling)..

That isn't, I don't think, any different from 10g. All SGA_TARGET is doing is specifying the starting size for those same various pools and caches, with the precise division of the total amongst each pool to be sorted out by Oracle itself automatically. So if TARGET is set, and MAX_SIZE isn't, the old 9i "I am the total of the pools and caches" behaviour still kicks in. It ends up *looking* as though TARGET is set to MAX_SIZE, but they aren't functionally linked, actually (or as you put it, they are "effectively" set to the same).

My further understanding was that TARGET doesn't have to be set at all, since it is an optional replacement for setting individual pool and cache sizes, and allows Oracle to dynamically and automatically shuffle memory around amongst those pools and caches (ie, it's the thing that makes automatic memory allocation happen).

And then just to confuse the matter even further, it is possible to set all the old manual parameters AND the TARGET, because in that case the old parameters simply define the *minimum* allocations that can be made to the various pools when under otherwise automatic management.

Regards
HJR Received on Thu Oct 21 2004 - 06:19:09 CDT