Dynamic SGA Oracle 10G problems on 11.23?

[CFI-beheer]

Whenever an oracle 10G instance is started with, for example a max sga size of 900 MB, and a SGA Start size of 200 MB,
HPUX just boldy reserves a segment size of 900 MB. (ipcs -b shows that).
We decreased the max SGA size, and restarted the instances, and the segment size also decreased...

How it this possible? This will overide the ability from Oracle to dynamically assign memory.

Are there patches available that address this issue?

I could not find a definitive answer, and we also created a TAR @ oracle.

Any help is apreciated

[Patti Johnson]

The dynamic SGA size it not truly dynamic, it is constrained by the sga_max_size parameter. You can increase / decrease elements of the SGA, but the actual size will not grow or shrink.

I'd be interested in Oracles response to your tar, but I know in 9i the only changes you can make are to move memory from one component to another. For example reduce db cache and increase shared pool.

If you set sga_max_size, then that is you limit, it you do not set it, then the total size of all components (db cache, shared pool, large pool, etc) is your max size.

Patti

[Hein van den Heuvel]

>> How it this possible? This will overide the ability from Oracle to dynamically assign memory.

It will be able to dynamically assign
memory WITHIN the SGA.

Like how much buffer space versus shared pool vs ... based on needs.

As such it is more of an Oracle param then an OS param as one might think at first glance.

Google: +oracle +sga_max_size +10g
Gives several notes in this space.


Hope this helps some,
Met vriendelijke groetjes,
Hein.




+oracle +sga_max_size +10g

[CFI-beheer]

thanks for the replies.
It made my gut feeling stronger... :-)

Indeed you can find lots of this on Google.
We also found in a oracle Press Book about the new features of 10g, that the dynamically assigned memory of the SGA is not dynamic on all OS'es. (although they do not mention which os).

[Julio Yamawaki · Accepted solution]

Hi,

What happened to you is exactly what Oracle does, it doesn´t matter if you use 9i or 10g.
When you set SGA_MAX_SIZE, Oracle reserves all this memory, SGA is dynamic but memory allocation is not.
It seems good if you have a lot of memory and want to manage Oracle without stopping/starting, but, if you just don´t have so much memory, it is not.

Regards,

[Indira Aramandla]

Hi,

The dynamic SGA infrastructure allows for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown the database. The Oracle Server can modify the physical address space use to respond to the operating system's use of physical memory.

Some SGA parameters have been defined as 'Dynamic' parameters as one can grow / shrink their current values. The whole SGA size is limited by the SGA_MAX_SIZE parameter that is the defined upper bound.

Each SGA parameter is allocated in terms of Granules that is the Allocation Unit. The Granule size will depend on the SGA_MAX_SIZE value and hardware platform. The SGA_MAX_SIZE parameter is used to limit the dynamic sizing of SGA parameters. If the SGA_MAX_SIZE parameter value in the spfile is smaller than the addition of all SGA components, then the dynamic value of SGA-MAX_SIZE instance parameter is automatically set by Oracle to the SGA size.



Indira A

[Yogeeraj_1]

hi,

to add to IA's reply above, see also:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c08memor.htm#19799

kind regards
yogeeraj

[Martin Decker]

Hello,

theoretically, you should not be worried. In your case, over time the unused parts of the SGA should get paged out to swap and as soon as you increase sga_target, it will be paged in again.

The truly dynamic SGA exists for example in Sun Solaris with DISM (dynamic intimite shared memory). But also there, if you shrink sga_target, the shrinked amout is not returned and ipcs does not show the shrinked amount. However, also here the unused parts should be paged out over time.

Regards,
Martin

[CFI-beheer]

Well,
on HP it seems to allocate memory, based on the SGA MAX Size.
After some tests this was confirmed.

Our customer tried to run 15 database with a SGA size of 900 mb each.
With 8 GB memory this leads to mem problems.