Re: 10G - editing SPFILE

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 21 Oct 2004 13:48:26 +0200
Message-ID: <cl87mb$i8j$1@news.BelWue.DE>

Howard J. Rogers wrote:
[...]

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

I didn't mean to say setting SGA_MAX_SIZE will functionally influence SGA_TARGET, or the other way round.

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

Right. And while it's going to make the life a little easier because your SGA will be able to support batchprocessing during the night and OLTP during the day without DBA action, however, it also leads to more "I'll through as much RAM as a can to Oracle, because with automatic memory allocation it won't hurt anyone" attitude. If SGA_TARGET is set, the sum of all configured *_POOL_SIZE parameters or SGA_TARGET will be the effective size of your SGA. Now consider a *thoughtful* DBA that hadn't had the time to go to a 10g course but heard about SGA_TARGET and thinks: "well I know I need about 120M for buffer cache, about the same for library cache and the other pools are smallish so I only need say 300M for my SGA. But automatic allocation is cool and new, and who knows, maybe I'll need more RAM later so I'll just set SGA_TARGET to 2GB and leave it to Oracle".

Wow, now I've got a massive buffer cache of more than 1.5G because as far as I did test, what cannot be sensibly assigned to the other pools will end up in the buffer cache. 100% BCHR. Great.

Or did I miss something?

Regards,
Holger

PS: Do you ever sleep? ;-) Received on Thu Oct 21 2004 - 06:48:26 CDT