Re: Dynamic SGA and pinned

Hi,

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<41128fbb$0$15686> > > 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.
>
> 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)

> > Oracle will initially allocate the memory (using malloc and such).
>
> Which is what I've been saying.
>

Yes i did not question that. (at least i did not mean to.)

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

exactly, that is the original posters question.

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

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. Received on Fri Aug 06 2004 - 05:58:29 CDT