Re: Dynamic SGA and pinned

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

Hi Howard

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

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/docs/html/B10812_01/appendix_e.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).

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

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