Re: Dynamic SGA and pinned

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

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

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 Received on Mon Aug 09 2004 - 05:21:58 CDT