Re: Dynamic SGA and pinned

"Erik Hendrix" <hendrix_erik_at_hotmail.com> wrote in message news:3UxPc.134326$vJ6.98459_at_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 Received on Mon Aug 02 2004 - 16:08:39 CDT