Re: Dynamic SGA and pinned

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 4 Aug 2004 09:51:31 +1000
Message-ID: <411024d7$0$1901$afc38c87@news.optusnet.com.au>

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

SQL> 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 bytes

Database mounted.
Database opened.
SQL> alter system set sga_max_size=180m; alter system set sga_max_size=180m

                 *

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

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