Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SGA and pinned
<andre_at_xxx.mail.ee> wrote in message news:410fe207_1_at_news.estpak.ee...
> Howard J. Rogers wrote:
>
> >>>from the total pool of available physical RAM, and hence that RAM is
not
> >>>available for any other programs running on that server to make use of.
> >>
> >>In other words, the whole "dynamic" thing is pointless. It's like making
> >>a bowl of cereal and then having half of the bowl covered , so you don't
> >>see it. An exercise in futility.
> >
> >
> >
> > Well, as I said... it's entirely true that the SGA is not actually
dynamic.
> > But the feature that they call 'dynamic SGA' (ie, the ability to
dynamically
> > resize the various pools within the SGA) is not to be sniffed at, and I
> > wouldn't call it futile.
> >
>
> Please stop the demagogy, your statements and understanding in wrong,
> please stop spreading it unless you have evidence to back it up.
OK, you want evidence. Try this:
SQL> show sga
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes Variable Size 145750508 bytes Database Buffers 25165824 bytes Redo Buffers 262144 bytesSQL> alter system set sga_max_size=250m scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 788448 bytes Variable Size 238024736 bytes Database Buffers 25165824 bytes Redo Buffers 262144 bytesDatabase mounted.
*
Which proves two things. First, if you set SGA_MAX_SIZE to 250M then the next time you start your instance, a full 250MB of RAM is used by the SGA (give or take a bit of rounding up to the next actual granule border). Yet I changed nothing to do with my shared pool, my buffer cache or my large pool... so clearly, the *used* bit of my SGA can only be the same as it was before... as is evidenced by the before and after values displayed for the "Database Buffers" and "Redo Buffers" components, for example.
Secondly, how "dynamic" is the SGA when the parameter which sizes it, SGA_MAX_SIZE, can't actually be dynamically altered, as I demonstrate at the end with an attempt to dynamically set it back to 180M, but can only be modified with the 'scope=spfile' clause tacked on (or by editing an init.ora) -thus requiring an instance re-start before the new value is read. That's what we call a *static* initialisation parameter.
Ergo: SGA_MAX_SIZE is not actually dynamic. And SGA_MAX_SIZE steals all of its memory from the operating system regardless of what your caches and pools are actually set to.
Now Connor suggests that Solaris is an exception and does things differently. Fine... my very first statement in the thread was "There may be subtleties about the way Solaris uses memory that I am unaware of". That means we all learn something from the discussion, and that this is definitely not an appeal to prejudice or emotion. Which means it isn't demagoguery.
By the way, the phrase you were looking for was "understanding IS wrong", not "IN wrong".
Regards
HJR
Received on Tue Aug 03 2004 - 18:51:31 CDT