Hi,

I have a question regarding Oracle 10g and it's dynamic SGA. We are
currently evaluating different hardware platforms as well as database
platform. Dynamic configuration is an important part for us.
I understand that on Solaris, one can set a Oracle parameter (SGA_MAX_SIZE?)
to a maximum value. Oracle can then use DISM to pin into memory what it
currently needs and leave in swap space what it does not need.
This would allow us to set it for example to 32GB but only use 4GB. When we
then give the host more memory, we can then increase Oracle's active SGA and
it will start pinning more memory into RAM.

I'm wondering what the deal is on AIX and HP-UX? Does Oracle pin the SGA
into RAM on these platforms? If it does, how does it work with dynamic
configuration? Will it like on Sun allocate all the memory but only pin what
is currently required?

Thanks.

[ Post a follow-up to this message ]

"Erik Hendrix" <hendrix_erik@hotmail.com> wrote in message
news:3UxPc.134326$vJ6.98459@cyclops.nntpserver.com...
> Hi,
>
> I have a question regarding Oracle 10g and it's dynamic SGA. We are
> currently evaluating different hardware platforms as well as database
> platform. Dynamic configuration is an important part for us.
> I understand that on Solaris, one can set a Oracle parameter
(SGA_MAX_SIZE?)
> to a maximum value. Oracle can then use DISM to pin into memory what it
> currently needs and leave in swap space what it does not need.
> This would allow us to set it for example to 32GB but only use 4GB. When
we
> then give the host more memory, we can then increase Oracle's active SGA
and
> it will start pinning more memory into RAM.
>
> I'm wondering what the deal is on AIX and HP-UX? Does Oracle pin the SGA
> into RAM on these platforms? If it does, how does it work with dynamic
> configuration? Will it like on Sun allocate all the memory but only pin
what
> is currently required?
>
> Thanks.


There may be subtleties about the way Solaris uses memory that I am unaware
of, but as far as I know, if you set SGA_MAX_SIZE on any platform to a given
value, then that amount of RAM is immediately 'stolen' from the operating
system and allocated to Oracle's own exclusive use. That large chunks of
that shared memory segment are not *actually* used, because your
shared_pool_size or db_cache_size are set to low amounts is irrelevant: the
large shared memory segment has nevertheless been allocated to Oracle's use
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.

If you set SGA_MAX_SIZE to 32GB, which seems awfully ambitious btw, then
32GB is what will be used in the sense of 'Oracle's pinched the lot and
nothing else can make use of it'. If your actual cache sizes then happen
only to chew up 4GB of that allocation, that simply means that 28GB of RAM
sit there, idly, being (effectively) wasted.

There is nothing actually dynamic about Oracle's SGA, in other words. Caches
*within* the SGA can be resized dynamically (and automatically in 10g). But
the overall memory allocation within which that happens is static and
manual. Still.

Regards
HJR

[ Post a follow-up to this message ]

On Tue, 03 Aug 2004 07:08:39 +1000, Howard J. Rogers wrote:

> There may be subtleties about the way Solaris uses memory that I am unawar
e
> of, but as far as I know, if you set SGA_MAX_SIZE on any platform to a giv
en
> value, then that amount of RAM is immediately 'stolen' from the operating
> system and allocated to Oracle's own exclusive use. That large chunks of
> that shared memory segment are not *actually* used, because your
> shared_pool_size or db_cache_size are set to low amounts is irrelevant: th
e
> large shared memory segment has nevertheless been allocated to Oracle's us
e
> 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.
--
A city is a large community where people are lonesome together.

[ Post a follow-up to this message ]

"Mladen Gogala" <gogala@sbcglobal.net> wrote in message
news:pan.2004.08.03.10.11.20.936762@sbcglobal.net...
> On Tue, 03 Aug 2004 07:08:39 +1000, Howard J. Rogers wrote:
> 
unaware[vbcol=seagreen] 
given[vbcol=seagreen] 
operating[vbcol=seagreen] 
the[vbcol=seagreen] 
use[vbcol=seagreen] 
>
> 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.

Regards
HJR

[ Post a follow-up to this message ]

Howard J. Rogers apparently said,on my timestamp of 3/08/2004 9:35 PM:
 
>
>
>
> 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 dynamical
ly
> resize the various pools within the SGA) is not to be sniffed at, and I
> wouldn't call it futile.

It may actually be a little more than that.  IIRC the original presentation,
Oracle will "reserve" a chunk of shared memory equal to SGA_MAX_SIZE but it
will only mark to the OS as "non-pageable" the portion that it uses.
Not that it matters much: it's still a kludge to make the SGA "re-sizable",
as Mladen pointed out.  It's not really: it's a fixed size that gets all use
d
or not.  Duh!
--
Cheers
Nuno Souto
wizofoz2k@yahoo.com.au.nospam

[ Post a follow-up to this message ]

Howard J. Rogers wrote:
>
> "Erik Hendrix" <hendrix_erik@hotmail.com> wrote in message
> news:3UxPc.134326$vJ6.98459@cyclops.nntpserver.com... 
> (SGA_MAX_SIZE?) 
> we 
> and 
> what 
>
> There may be subtleties about the way Solaris uses memory that I am unawar
e
> of, but as far as I know, if you set SGA_MAX_SIZE on any platform to a giv
en
> value, then that amount of RAM is immediately 'stolen' from the operating
> system and allocated to Oracle's own exclusive use. That large chunks of
> that shared memory segment are not *actually* used, because your
> shared_pool_size or db_cache_size are set to low amounts is irrelevant: th
e
> large shared memory segment has nevertheless been allocated to Oracle's us
e
> 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.
>
> If you set SGA_MAX_SIZE to 32GB, which seems awfully ambitious btw, then
> 32GB is what will be used in the sense of 'Oracle's pinched the lot and
> nothing else can make use of it'. If your actual cache sizes then happen
> only to chew up 4GB of that allocation, that simply means that 28GB of RAM
> sit there, idly, being (effectively) wasted.
>
> There is nothing actually dynamic about Oracle's SGA, in other words. Cach
es
> *within* the SGA can be resized dynamically (and automatically in 10g). Bu
t
> the overall memory allocation within which that happens is static and
> manual. Still.
>
> Regards
> HJR

(Without proof...) I think Solaris is the exception to the rule.
sga_max_size I think has to be allocated within swap but not within
physical memory thus allowing a "genuine" resize facility.

hth
connor
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------

[ Post a follow-up to this message ]

Hi!

Actually you can set SGA_MAX_SIZE  greater than fysical memory and it will
allocate
as much as you define in SGA sizes. And it uses for that amount when needed
on Wintel.

Resizesing SGA_MAX_SIZE  need instance restart but other SGA-parameters can
be changed dynamic.
.


"Noons" <wizofoz2k@yahoo.com.au.nospam> wrote in message
news:410f7ceb$0$16425$afc38c87@news.optusnet.com.au...
> Howard J. Rogers apparently said,on my timestamp of 3/08/2004 9:35 PM:
> 
dynamic.[vbcol=seagreen] 
dynamically[vbcol=seagreen] 
>
> It may actually be a little more than that.  IIRC the original
presentation,
> Oracle will "reserve" a chunk of shared memory equal to SGA_MAX_SIZE but
it
> will only mark to the OS as "non-pageable" the portion that it uses.
> Not that it matters much: it's still a kludge to make the SGA
"re-sizable",
> as Mladen pointed out.  It's not really: it's a fixed size that gets all
used
> or not.  Duh!
> --
> Cheers
> Nuno Souto
> wizofoz2k@yahoo.com.au.nospam

[ Post a follow-up to this message ]

Howard J. Rogers wrote:
 
>
>
>
> 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 dynamical
ly
> 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.

Andre

[ Post a follow-up to this message ]

andre@xxx.mail.ee wrote:
<snip>
>
> Please stop the demagogy, your statements and understanding in wrong,
> please stop spreading it unless you have evidence to back it up.
>
> Andre
Take your own advice and prove that Howard is wrong, please.

Also,I'm more inclined to believe HJR, who usually provides
reproduceable tests that validate his statements, than you.

[ Post a follow-up to this message ]

<andre@xxx.mail.ee> wrote in message news:410fe207_1@news.estpak.ee...
> Howard J. Rogers wrote:
> 
not[vbcol=seagreen] 
dynamic.[vbcol=seagreen] 
dynamically[vbcol=seagreen] 
>
> 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

[ Post a follow-up to this message ]