Oracle Database Server - Dynamic SGA and pinned

Author

Dynamic SGA and pinned

Erik Hendrix

2004-08-02, 5:52 pm

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.

Howard J. Rogers

2004-08-02, 5:52 pm

"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

Mladen Gogala

2004-08-03, 8:15 am

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

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.

Howard J. Rogers

2004-08-03, 8:15 am

"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

Noons

2004-08-03, 8:15 am

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 dynamically
> 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 used
or not. Duh!
--
Cheers
Nuno Souto
wizofoz2k@yahoo.com.au.nospam

Connor McDonald

2004-08-03, 8:15 am

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

(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"

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

Jack

2004-08-03, 8:15 am

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

andre@xxx.mail.ee

2004-08-03, 5:52 pm

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

Andre

Bricklen

2004-08-03, 5:52 pm

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.

Howard J. Rogers

2004-08-03, 5:52 pm

<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

Daniel Morgan

2004-08-04, 2:49 am

andre@xxx.mail.ee wrote:

> Howard J. Rogers wrote:
>
>
> Please stop the demagogy, your statements and understanding in wrong,
> please stop spreading it unless you have evidence to back it up.
>
> Andre

If Howard says it is true ... I'd bet hard cash it is.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Noons

2004-08-05, 8:17 am

Jack apparently said,on my timestamp of 3/08/2004 11:54 PM:

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

Interesting. Which version? Mine doesn't do that in XP Pro
with 9ir2. 10g? Is that swap size only, or nothing at all allocated?

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

I think we established that. The issue is that in some OSs, it means nothing
as the SGA_MAX_SIZE gets allocated no matter what.
--
Cheers
Nuno Souto
wizofoz2k@yahoo.com.au.nospam

Howard J. Rogers

2004-08-05, 8:17 am

"Jack" <none@noe.com> wrote in message news: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 .

So?

I take it from your mention of "oracle.exe" that we are dealing with
Windows.

Further than that, I cannot go, since you provide *zero* information. 9i?
10g?

The little test I posted earlier, the results of which you so blythely
ignore, was Windows 2003 and 10g.

And let's try and compare apples with apples, shall we? Your post simply
says that there is more memory nabbed by oracle.exe than what is required
for the SGA. Which I won't argue with. My post says, if you set SGA_MAX_SIZE
to X, then X is allocated. Not V and a bit of W tucked away somewhere in
case of need.

Explain to me, please, why "Total System Global Area" increases when I
increase SGA_MAX_SIZE, despite *none* of the constituent parts of the SGA
having been increased in size.

HJR

Howard J. Rogers

2004-08-05, 8:18 am

"Daniel Morgan" <damorgan@x.washington.edu> wrote in message
news:1091597302.385615@yasure...

>
> If Howard says it is true ... I'd bet hard cash it is.

Oh, Lawd. Kind though that statement is, it's not one I'd proffer, offer nor
abide by. I make too many mistakes and take too many shortcuts for it to be
true.

"If Howard says it is true, ....I'd bet one cent that it might be, possibly"
is as far as I'd go.

In a world of Tom Kytes, Jonthan Lewises, and -dare I say- Richard Footes,
my contributions are pretty thin on the ground, to be honest.

It's rather like my favourite composer: Benjamin Britten. When asked if he
was any good,he pointed at Mozart and Beethoven and Bach, and said no. But
then his brow furrowed, he pointed at Stockhausen, Schoenberg and Messiaen,
and said, 'well, I'm at least as good as those guys'.

In a world of omlets, I have a use.

Regards
HJR

Jack

2004-08-05, 8:18 am

It is just showing so, but it does not use it.

There are some changes made and the meaning of those has also.
Sadly some documentation gays are not uptotime in Oracle but some day you
will get
*full* wiremodel. (by that time it will already be changed again)

> Explain to me, please, why "Total System Global Area" increases when I
> increase SGA_MAX_SIZE, despite *none* of the constituent parts of the SGA
> having been increased in size.
>
> HJR
>
>

Howard J. Rogers

2004-08-05, 8:18 am

"Jack" <none@noe.com> wrote in message news:LNnQc.102$V16.0@read3.inet.fi...
> It is just showing so, but it does not use it.

And your evidence for that statement is.... ??

> There are some changes made and the meaning of those has also.
> Sadly some documentation gays are not uptotime in Oracle but some day you
> will get
> *full* wiremodel. (by that time it will already be changed again)

I have no idea what anything in that paragraph means. We'll put it down to
English perhaps not being your native language, which is fine. What is not
fine is making sweeping statements such as "Although it shows it, it isn't
actually doing it" without *any* supporting evidence whatsoever.

I, on the other hand, can post you figures and screen captures indicating a
massive amount of swapping going on for a machine where sga_max_size is set
larger than physical RAM, indicating (to me at least) that Oracle is indeed
trying to make use of that memory.

Stick to specifics, please. And evidence.

I have shown you a 'total SGA' allocation increasing, according to SQL*Plus'
'show sga' command. (I can also show you that via Windows' Task Manager, if
you'd prefer). What is your evidence that the memory shown as being
allocated to the SGA is not actually allocated, as you claim?

HJR

Karsten Schmidt

2004-08-05, 5:54 pm

Hi there,

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

on a decent OS this is not entirely true.
Oracle will initially allocate the memory (using malloc and such).
But then as other processes come along, and want to use RAM, it will
get paged out. After a while, the allocated but un-used portion of the
SGA wil reside on disk in the swap area, and not occupy physical RAM.

unless, as the OP pointed out, the SGA is locked in phys memory, which
would prevent the virtual memory subsystem from paging it out. You can
use the lock_sga parameter to control this. (false by default, which
would allow paging out the SGA)

quote from the 9.2 manuals:[vbcol=seagreen]
LOCK_SGA locks the entire SGA into physical memory. It is usually
advisable to lock the SGA into real (physical) memory, especially if
the use of virtual memory would include storing some of the SGA using
disk space. This parameter is ignored on platforms that do not support
it.
<<

Karsten

Pete Finnigan

2004-08-05, 5:54 pm

>unless, as the OP pointed out, the SGA is locked in phys memory, which
>would prevent the virtual memory subsystem from paging it out. You can
>use the lock_sga parameter to control this. (false by default, which
>would allow paging out the SGA)
>
>quote from the 9.2 manuals:
>LOCK_SGA locks the entire SGA into physical memory. It is usually
>advisable to lock the SGA into real (physical) memory, especially if
>the use of virtual memory would include storing some of the SGA using
>disk space. This parameter is ignored on platforms that do not support
>it.
><<
Hi Karsten,

Just as an aside to this discussion...:-)

You cannot simply set this parameter on Solaris as only root can
generally lock shared memory segments and the Oracle software generally
doesn't run as root. Indeed its not a good idea to run Oracle as root!.
You can grant access to shared memory segments to other users, but i
cannot remember the exact syntax. There is another way to do it using a
simple C program to lock the shared memory. I posted a program a while
ago to this list. I added a copy of my post to my site, its at
http://www.petefinnigan.com/ramblings/lock_sga.htm if anyone is
interested.

kind regards

Pete
--
Pete Finnigan
email:pete@petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Howard J. Rogers

2004-08-05, 5:54 pm

"Karsten Schmidt" <groups@karsten-schmidt.com> wrote in message
news:c6711ac4.0408050721.64d5d81b@posting.google.com...
> Hi there,
>
unaware[vbcol=seagreen]
given[vbcol=seagreen]
operating[vbcol=seagreen]
the[vbcol=seagreen]
use[vbcol=seagreen]
>
> on a decent OS this is not entirely true.

That needs qualifying. Connor has already mentioned that it is probably true
for Solaris. Are you saying it is true for Linux and Windows? I have seen no
evidence of that. And evidence on the matter would be nice to have, one way
or the other, I don't mind.

> Oracle will initially allocate the memory (using malloc and such).

Which is what I've been saying.

> But then as other processes come along, and want to use RAM, it will
> get paged out. After a while, the allocated but un-used portion of the
> SGA wil reside on disk in the swap area, and not occupy physical RAM.

Which is what Connor and, I believe on re-reading, the original poster were
claiming to be the case for Solaris, and which I'm happy to accept is the
case (I can't test Solaris). Is it true for the two most popular platforms
on which Oracle finds itself installed? And "after a while" is sounding a
bit vague, in any case.

> unless, as the OP pointed out, the SGA is locked in phys memory, which
> would prevent the virtual memory subsystem from paging it out. You can
> use the lock_sga parameter to control this. (false by default, which
> would allow paging out the SGA)
>
> quote from the 9.2 manuals:
> LOCK_SGA locks the entire SGA into physical memory. It is usually
> advisable to lock the SGA into real (physical) memory, especially if
> the use of virtual memory would include storing some of the SGA using
> disk space. This parameter is ignored on platforms that do not support
> it.

So if we all followed Oracle's own advice on the matter, my original
statement that the entire SGA_MAX_SIZE is irretrievably pinched from *real*
memory and stays that way would be entirely correct without qualification,
would it not?

Whatever: we need some evidence.

Regards
HJR

> Karsten

Karsten Schmidt

2004-08-06, 7:50 am

Hi,

"Howard J. Rogers" <hjr@dizwell.com> wrote in message news:<41128fbb$0$15686> > > large shared memory segment has nevertheless been allocated to Oracle's
> use
>
> That needs qualifying. Connor has already mentioned that it is probably true
> for Solaris. Are you saying it is true for Linux and Windows? I have seen no
> evidence of that. And evidence on the matter would be nice to have, one way
> or the other, I don't mind.
>

I can not test this on linux or windows - but i would be surprised if
they do not page out unused memory. This sort of thing is impossible
to verify by using Oracle means. A testcase would look like that:

startup an oracle instance, over-allocate the SGA (i.e. sga_max_size
greater than the sum of what is actually used).

write a little c program that allocates and touches about the amount
of physical mem of the machine (this would cause serious swapping)

then run something in oracle.

then one would need to examine the shared memory, and find out, what
is paged out and what is not. Not sure how to do the last part on HPUX
(the platform that i am running on)

>
> Which is what I've been saying.
>
Yes i did not question that. (at least i did not mean to.)

>
> Which is what Connor and, I believe on re-reading, the original poster were
> claiming to be the case for Solaris, and which I'm happy to accept is the
> case (I can't test Solaris). Is it true for the two most popular platforms
> on which Oracle finds itself installed? And "after a while" is sounding a
> bit vague, in any case.

exactly, that is the original posters question.

>
>
> So if we all followed Oracle's own advice on the matter, my original
> statement that the entire SGA_MAX_SIZE is irretrievably pinched from *real*
> memory and stays that way would be entirely correct without qualification,
> would it not?
>

i agree if one wants to have some spare memeory to re-size one of the
SGA segments withou bouning the database, lock_SGA would be a bad
idea.

Christian Antognini

2004-08-08, 7:55 am

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Howard

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

It really depends on the OS. A good example is Linux.... here an example
taken from my SLES8-SP3 laptop (notice that in the output I just added some
NL for easier reading, otherwise is a 1:1 copy/paste from my terminal):

- the system has 1GB of physical memory and 512MB of swap, as you can see
few swap is in use (ca. 186MB)

linux:oracle:A1010 /u00/app/oracle> free -m
total used free shared buffers cached
Mem: 1008 995 13 0 10 646
-/+ buffers/cache: 338 669
Swap: 501 186 315

- 5 instances are running: 2 10g (A1010 and +ASM), 1 9iR2 (A920), 1 9iR1
(A901) and 1 8iR3 (A817)

linux:oracle:A1010 /u00/app/oracle> ps -ef | grep dbw
oracle 7532 1 0 Aug06 ? 00:00:43 ora_dbw0_A1010
oracle 7180 1 0 10:02 ? 00:00:00 asm_dbw0_+ASM
oracle 11874 1 0 11:53 ? 00:00:00 ora_dbw0_A901
oracle 11988 1 0 11:54 ? 00:00:01 ora_dbw0_A920
oracle 12114 1 0 11:58 ? 00:00:00 ora_dbw0_A817

- the 5 instances allocate about 1.7GB of shared memories! i.e. more than
the physical memory and swap (of course the OS and the Oracle executable
needs some memory as well...)

linux:oracle:A1010 /u00/app/oracle> ipcs -m

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0xce5d8780 1114112 oracle 640 100667392 7
0xaf68ad04 1212417 oracle 640 542130176 55
0xb39d0324 589826 oracle 640 536879104 30
0x301bd2bc 1245187 oracle 640 545259520 115
0x72a4a788 1277956 oracle 640 69578752 13

- let's the "real" SGA utilization of the instances

linux:oracle:A1010 /u00/app/oracle> a817
linux:oracle:A817 /u00/app/oracle> sq

SQL*Plus: Release 8.1.7.0.0 - Production on Sun Aug 8 12:03:16 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> show sga

Total System Global Area 68944032 bytes
Fixed Size 73888 bytes
Variable Size 51036160 bytes
Database Buffers 16777216 bytes
Redo Buffers 1056768 bytes

SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by
rollup(nvl(pool,name));

NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
db_block_buffers 16777216
fixed_sga 73888
java pool 16003072
log_buffer 1048576
shared pool 35015408
68918160

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

linux:oracle:A817 /u00/app/oracle> a901
linux:oracle:A901 /u00/app/oracle> sq

SQL*Plus: Release 9.0.1.4.0 - Production on Sun Aug 8 12:03:31 2004

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

SQL> show sga

Total System Global Area 538209912 bytes
Fixed Size 282232 bytes
Variable Size 520093696 bytes
Database Buffers 16777216 bytes
Redo Buffers 1056768 bytes

SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
---
sga_max_size big integer 538209912

SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by
rollup(nvl(pool,name));

NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
buffer_cache 16777216
fixed_sga 282232
java pool 16777216
log_buffer 1048576
shared pool 62914560
97799800

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

linux:oracle:A901 /u00/app/oracle> a920
linux:oracle:A920 /u00/app/oracle> sq

SQL*Plus: Release 9.2.0.5.0 - Production on Sun Aug 8 12:03:47 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.5.0 - Production

SQL> show sga

Total System Global Area 538516888 bytes
Fixed Size 454040 bytes
Variable Size 461373440 bytes
Database Buffers 75497472 bytes
Redo Buffers 1191936 bytes

SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
---
sga_max_size big integer 538516888

SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by
rollup(nvl(pool,name));

NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
buffer_cache 75497472
fixed_sga 454040
java pool 33554432
large pool 4194304
log_buffer 1180672
shared pool 83886080
198767000

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.5.0 - Production

linux:oracle:A920 /u00/app/oracle> a1010
linux:oracle:A1010 /u00/app/oracle> sq

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Aug 8 12:04:08 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options

SQL> show sga

Total System Global Area 536870912 bytes
Fixed Size 779724 bytes
Variable Size 488905268 bytes
Database Buffers 46137344 bytes
Redo Buffers 1048576 bytes
SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
---
sga_max_size big integer 512M

SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by
rollup(nvl(pool,name));

NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
buffer_cache 46137344
fixed_sga 779724
java pool 25165824
large pool 4194304
log_buffer 1048576
shared pool 88080852
165406624

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options

linux:oracle:A1010 /u00/app/oracle> +asm
linux:oracle:+ASM /u00/app/oracle> sq

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Aug 8 12:04:23 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options

SQL> show sga

Total System Global Area 100663296 bytes
Fixed Size 777616 bytes
Variable Size 99885680 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
---
sga_max_size big integer 96M

SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by
rollup(nvl(pool,name));

NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
fixed_sga 777616
java pool 25165824
large pool 12582912
shared pool 33554432
72080784

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Howard J. Rogers

2004-08-08, 5:49 pm

On Sun, 8 Aug 2004 12:19:09 +0200, Christian Antognini
<christian.antognini@trivadis.com> wrote:

>
[snip]
>

Let's just try and simplify, shall we? Because screen capture after screen
capture with little by way of explanation on the way through I find rather
difficult to work with.

You say your O/S reports (with ipcs -m) 5 memory allocations to Oracle
instances:

100667392 - 10g (+ASM)
542130176 - 9i of some sort
536879104 - 10g (A1010)
545259520 - 9i of some sort
69578752 - 8i

Without being able to tie up the precise memory allocation your O/S
reports to the specific instance whose details you then provide, the whole
exercise is more tricky than I'd like. I can't pin down the 9i examples,
because they are too similar, and and thus not distinguishable (I may have
missed the crucial clue in your post, of course). The 8i instance is
irrelevant, since it doesn't have a dynamic SGA feature. Which leaves us
the two 10g SGAs to discuss.

Well, the first one is also irrelevant. An ASM instance almost certainly
computes SGA_MAX_SIZE from the actually allocated pool sizes, rather than
the other way around, and hence one would expect the pools to match the
SGA_MAX_SIZE, and for both to be what's actually allocated (because the
pools themselves are fully in use). Your figures bear this out:

Total System Global Area 96M
pool totals about 72M
sga_max_size 96M
O/S reported allocation: 96M

So the ASM instance isn't a very good example to use one way or another,
though what the O/S reports matches the SGA_MAX_SIZE rather than the 'pool
totals' allocations. However, there's not enough in it to be significant,
I suspect. What about your other 10g instance?

Total System Global Area 512MB
sga_max_size 512M
pool totals about 158MB
O/S reported allocation: 512MB

So here we see the O/S reporting 512MB has been allocated... which is
precisely what SGA_MAX_SIZE says should be allocated. Yet only about 158MB
is actually allocated to the various caches. That disparity (between
what's actually in use, and what's allocated) is exactly what I was
talking about. My "claim" was that if you set SGA_MAX_SIZE to X, then X is
actually allocated, even though only Y is actually used. Precisely what
your figures report.

Now I may have misunderstood your figures and where they're coming from.
And if you were posting to suggest that what I'd claimed was not true for
Linux (it isn't clear to me what you were actually arguing for or
against), then I probably have misunderstood a lot.

But if the figures I've extracted are accurate and mean what I think they
mean, then you've just rather nicely proved that on Linux (as on Windows),
the dynamic SGA isn't.

Regards
HJR

Christian Antognini

2004-08-09, 2:47 am

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Howard

> Let's just try and simplify, shall we? Because screen capture after screen
> capture with little by way of explanation on the way through I find rather
> difficult to work with.

Sorry, in my opinion a screen capture is better than 1000 words. But if you
have a problem with it, let's start with another example... (I reconfigured
the instances, so, forget my last post!)

On my test system I give to Linux 1000MB of physical memory (+500MB of
swap). Presently the physical memory is almost full (free=99MB) and few swap
is in use (used=76MB).

linux:oracle:B1010 /u00/app/oracle> free -m
total used free shared buffers cached
Mem: 1008 908 99 0 39 640
-/+ buffers/cache: 228 780
Swap: 501 76 425

I started 3 10g instances:
- +ASM (has you wrote we can forgett this one... anyway it is small...)
- A1010
- B1010

Both A1010 and B1010 has SGA_MAX_SIZE=1536M. Since the management of the
shared memories is not dynamic, Oracle allocates 1.5GB of shared memories at
OS level.

linux:oracle:A1010 /u00/app/oracle> ipcs -m

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0xce5d8780 1114112 oracle 640 100667392 7
0x00000000 1310721 oracle 640 1830912 23
0x00000000 1343490 oracle 640 805306368 23
0xb39d0324 1376259 oracle 640 788537344 23
0x00000000 1441796 oracle 640 1044480 12
0x00000000 1474565 oracle 640 805306368 12
0x7088f7fc 1507334 oracle 640 788537344 12

As you can see more than one shared memory per instance has been created,
this is not a problem, it's just my configuration that prevents big shared
memories. Anyway, if I let generate a tracefile with ORADEBUG IPC I can map
the shared memories with the ID 1310721, 1343490, 1376259 to A1010 and the
shared memories with the ID 1441796, 1474565 and 1507334 to B1010. i.e. 3GB
of shared memory is allocated but my system has only 1GB of physical memory!

Now, what is important to understand is that Oracle is not able to
dynamically manage the shared memory segments, i.e. it always allocate the
full SGA_MAX_SIZE when a instance starts (you wrote it many times and we
agree about it...). But it is interesting that the allocated memory is
virtual memory and NOT physical memory. Therefore if the memory isn't used,
i.e. if the real SGA is much smaller than the shared memory, almost no
resources are used at OS level. This is not the case on all operating
systems. In fact on some OSs using very large SGA_MAX_SIZE is a drawback, on
others, like Linux, not.

That said, in my opinion it makes only seldom sense to set a very large
SGA_MAX_SIZE (compared to the real SGA). For example on my system I will
never be able to bump up the real SGA size to 1.5GB for any of my instances.
Therefore it makes no sense to set it....

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Howard J. Rogers

2004-08-09, 7:52 am

On Mon, 9 Aug 2004 10:30:26 +0200, Christian Antognini
<christian.antognini@trivadis.com> wrote:

> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi Howard
>
>
> Sorry, in my opinion a screen capture is better than 1000 words.

I have no problem with evidence, and screen captures are excellent for
providing such evidence. The difficulty I have is with *uncommented*
screen capture. This latest effort is much better in that respect.

> But if you
> have a problem with it, let's start with another example... (I
> reconfigured
> the instances, so, forget my last post!)

[snip]

> Both A1010 and B1010 has SGA_MAX_SIZE=1536M. Since the management of the
> shared memories is not dynamic, Oracle allocates 1.5GB of shared
> memories at
> OS level.

Good. As you go on to say, we have no problem in this respect.

> Now, what is important to understand is that Oracle is not able to
> dynamically manage the shared memory segments, i.e. it always allocate
> the
> full SGA_MAX_SIZE when a instance starts (you wrote it many times and we
> agree about it...).

Once again, good.

> But it is interesting that the allocated memory is
> virtual memory and NOT physical memory.

This is where we, apparently disagree. You regard an allocation from swap
as being trivial. I don't.

> Therefore if the memory isn't used,
> i.e. if the real SGA is much smaller than the shared memory, almost no
> resources are used at OS level.

Apart from a dirty great allocation out of swap.

It strikes me you can't have it two ways. Either Oracle grabs the lot
(which, as you say, we are agreed on). Or it grabs only what it needs as
it needs it, which is rather what I thought the original Solaris poster
and Connor were saying happens in Solaris. If it did that, that would be
truly low impact on the O/S, and truly dynamic. But if it grabs the lot,
from wherever I care not, then it's as I've been describing: not very
dynamic.

That it's swap and not physical is irrelevant to that conclusion, it seems
to me.

> This is not the case on all operating
> systems. In fact on some OSs using very large SGA_MAX_SIZE is a
> drawback, on
> others, like Linux, not.

Again, having acknowledged it takes the memory allocation from *somewhere*
in one hit, I honestly can't see how you can claim that setting SGA_MAX to
a large value is of no discernible consequence.

Never mind three issues that we haven't yet addressed. One was raised by
another poster here: what about LOCK_SGA? Which Oracle recommends you set,
but which in combination with SGA_MAX_SIZE would be disastrous. And two:
if you arrange for a *ridiculously* large SGA_MAX_SIZE, it doesn't
surprise me that a lot of it gets swapped out to disk. But what if it's
just a *bit* too big.... not big enough to induce excessive paging, but
bigger than it should be. I'll leave that one hanging because frankly I'm
not up on the subtelties of O/S paging algorithms. But it worries me.

The third issue is another worry: 10g. I haven't tested it, and therefore
it's me that has no evidence for this. And in any case, it's more by way
of thought-experiment than anything else. But with 10g's new workload
automation features, I wouldn't be too confident that memory allocations
that are "safely" on disk because, as you put it, "the memory isn't used",
will actually stay there. If you've told 10g that you have 1.5GB of memory
available to house the SGA, it wouldn't surprise me in the least to
discover that 10g decides it wants to make use of most of it at some
point... and then we're into a resource-hit for the O/S that even you I
think would accept as real, discernible and painful. And if not 10g, then
maybe 11z or 12.5t... who knows?

> That said, in my opinion it makes only seldom sense to set a very large
> SGA_MAX_SIZE (compared to the real SGA). For example on my system I will
> never be able to bump up the real SGA size to 1.5GB for any of my
> instances.
> Therefore it makes no sense to set it....

Now that's something we definitely can agree on. I would put it rather
stronger than that, and would suggest that doing otherwise is potentially
very detrimental indeed. But perhaps that is merely a matter of degree. We
agree that the memory allocation from the O/S is made in full, up-front.
We agree that it doesn't make sense to go beserk over it. I think we could
usefully leave it there.

Regards
HJR

Christian Antognini

2004-08-09, 5:52 pm

**** Post for FREE via your newsreader at post.usenet.com ****

Hi Howard

>
> This is where we, apparently disagree. You regard an allocation from swap
> as being trivial. I don't.

As you could see in the output of "free", on Linux it is not even allocated
in swap. In fact in my last example there is about 1GB of physical+swap that
in use, but the 2 SGA alone, allocates 3GB! i.e. 2GB are really not
allocated neither in physical memory nor in swap.

> It strikes me you can't have it two ways. Either Oracle grabs the lot
> (which, as you say, we are agreed on). Or it grabs only what it needs as
> it needs it, which is rather what I thought the original Solaris poster
> and Connor were saying happens in Solaris. If it did that, that would be
> truly low impact on the O/S, and truly dynamic. But if it grabs the lot,
> from wherever I care not, then it's as I've been describing: not very
> dynamic.

Even in Solaris with DISM the whole shared memory is allocated and takes
place in swap! The memory is just not locked... see description at
http://download-west.oracle.com/doc....htm#sthref830.

> Never mind three issues that we haven't yet addressed. One was raised by
> another poster here: what about LOCK_SGA? Which Oracle recommends you set,
> but which in combination with SGA_MAX_SIZE would be disastrous.

Where do you get the information that Oracle recommends to lock the SGA.
Anyway, the default value is FALSE and for some OS they say the opposite,
e.g. quote from the same URL as above: "Oracle recommends that you do not
set the LOCK_SGA parameter to TRUE in the server parameter file on Solaris
systems. If you do, Oracle Database 10g does not start up."

Personally I never locked an SGA. Good sizing is sufficient...

> And two:
> if you arrange for a *ridiculously* large SGA_MAX_SIZE, it doesn't
> surprise me that a lot of it gets swapped out to disk. But what if it's
> just a *bit* too big.... not big enough to induce excessive paging, but
> bigger than it should be. I'll leave that one hanging because frankly I'm
> not up on the subtelties of O/S paging algorithms. But it worries me.

*ridiculously* large SGA_MAX_SIZE makes definitively no sense.

> The third issue is another worry: 10g. I haven't tested it, and therefore
> it's me that has no evidence for this. And in any case, it's more by way
> of thought-experiment than anything else. But with 10g's new workload
> automation features, I wouldn't be too confident that memory allocations
> that are "safely" on disk because, as you put it, "the memory isn't used",
> will actually stay there. If you've told 10g that you have 1.5GB of memory
> available to house the SGA, it wouldn't surprise me in the least to
> discover that 10g decides it wants to make use of most of it at some
> point... and then we're into a resource-hit for the O/S that even you I
> think would accept as real, discernible and painful.

In 10g there is a new INIT.ORA parameter, i.e. SGA_TARGET. Its aim is to
tell to Oracle how much memory should be used. I'm running 10g since beta 1
with automatic SGA management (i.e. more than one year now) and I never saw
that Oracle increases the SGA larger than SGA_TARGET. Instead I have no
problem to generate ORA-04031 errors with it... even if lots of memory is
available inside (e.g. lots of free space in shared pool) or outside the SGA
(i.e. SGA_TARGET much smaller than SGA_MAX_SIZE).

>
> Now that's something we definitely can agree on. I would put it rather
> stronger than that, and would suggest that doing otherwise is potentially
> very detrimental indeed. But perhaps that is merely a matter of degree. We

> agree that the memory allocation from the O/S is made in full, up-front.
> We agree that it doesn't make sense to go beserk over it. I think we could
> usefully leave it there.

As I wrote usually it makes no sense. But saying the feature is useless is
too strong as well...

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Howard J. Rogers

2004-08-09, 5:52 pm

On Mon, 9 Aug 2004 18:09:07 +0200, Christian Antognini
<christian.antognini@trivadis.com> wrote:

> As I wrote usually it makes no sense. But saying the feature is useless
> is
> too strong as well...

Well, I didn't, actually. I sayed it wasn't actually dynamic, and hence
that 'dynamic SGA' doesn't actually exist as a feature. Which we've
convincingly demonstrated is indeed true, for Linux at any rate. That the
pools within the SGA are truly dynamic is also undoubtedly true, and I've
said nothing at all to knock that.

Let's not start putting words into others' mouths, eh?

HJR

Christian Antognini

2004-08-10, 2:52 am

**** Post for FREE via your newsreader at post.usenet.com ****

>
>
> Well, I didn't, actually. I sayed it wasn't actually dynamic, and hence
> that 'dynamic SGA' doesn't actually exist as a feature. Which we've
> convincingly demonstrated is indeed true, for Linux at any rate. That the
> pools within the SGA are truly dynamic is also undoubtedly true, and I've
> said nothing at all to knock that.
>
> Let's not start putting words into others' mouths, eh?

I agree, you didn't... but this is the impression I get reading your
posts... therefore I'm happy to heard that we agree on this point as well
:-)

Have a nice day.

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=