Re: 10G - editing SPFILE

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 21 Oct 2004 22:38:39 +1000
Message-Id: <4177adc8$0$2216$afc38c87@news.optusnet.com.au>

Holger Baer wrote:

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

I didn't mean to imply that you did. Hence the comment in parentheses. Apologies if it came across otherwise.

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

I am feeling old and fogeyish. I am not yet convinced either about all this automation, but it almost certainly beats coughing up the cash for a trained, thoughtful DBA.

Certain "leading Oracle authors" have already sniffed which way the wind is blowing on this one -a wind blowing straight out of Redmond's automation labs, I think.

The analogy I sometimes use is that I might not have your University degree, your Oracle expertise, or your thoughtful insight and patience... but hey! I drive a Ferrari and I get all the chicks!! So eat my shorts, nerdy!

In short, flash, crass, flesh-creeping tuning methods though they are, they impress the gullible (ie, management :-|). The analogy ultimately breaks down, of course: Ferraris are expensive. Automation isn't. Which is the heart of the problem.

I am still in a bit of a quandary about the whole thing (and not just because I would like a Ferrari one day. Preferably before I'm 90 and can't enjoy it). I hate the dumbing down, but agree it shouldn't be a work of art or a labour of big science to tune a database. I hate the 'it's good enough' school of thought. I hate the 'let's over-provision everything so the users won't notice any bottlenecks' approach. But I like the idea of simplicity and ease of use.

> Regards,
> Holger
>
> PS: Do you ever sleep? ;-)

Not if I can help it.

Regards
HJR Received on Thu Oct 21 2004 - 07:38:39 CDT