Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic SGA Oracle 10G problems on 11.23?

SOLVED
Go to solution
CFI-beheer
Advisor

Dynamic SGA Oracle 10G problems on 11.23?

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
8 REPLIES
Patti Johnson
Respected Contributor

Re: Dynamic SGA Oracle 10G problems on 11.23?

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
Honored Contributor

Re: Dynamic SGA Oracle 10G problems on 11.23?



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

Re: Dynamic SGA Oracle 10G problems on 11.23?

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
Esteemed Contributor
Solution

Re: Dynamic SGA Oracle 10G problems on 11.23?

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
Honored Contributor

Re: Dynamic SGA Oracle 10G problems on 11.23?

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
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: Dynamic SGA Oracle 10G problems on 11.23?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)

Re: Dynamic SGA Oracle 10G problems on 11.23?

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
Advisor

Re: Dynamic SGA Oracle 10G problems on 11.23?

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.