Re: Dynamic SGA and pinned

From: Jack <none_at_noe.com>
Date: Wed, 04 Aug 2004 07:33:23 GMT
Message-ID: <7j0Qc.73$TO4.40@read3.inet.fi>

When
SQL> sho sga

Total System Global Area 1888562788 bytes

Fixed Size                   458340 bytes
Variable Size            1803550720 bytes
Database Buffers           83886080 bytes
Redo Buffers                 667648 bytes

SQL> Oracle.exe size is about 400Mb so it DOES NOT steals all of  its memory from the operating system .

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:411024d7$0$1901$afc38c87_at_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 Wed Aug 04 2004 - 02:33:23 CDT