OraFAQ Forum: Server Administration » SGA_TARGET & SGA_MAX

Home » RDBMS Server » Server Administration » SGA_TARGET & SGA_MAX_SIZE not equal (oracle 10.2.0.4 solaris 2.10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SGA_TARGET & SGA_MAX_SIZE not equal [message #561847]

Wed, 25 July 2012 13:11

BeefStu
Messages: 208
Registered: October 2011

Senior Member

I have the following setup


SQL> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3G
sga_target                           big integer 2G

from what I read I beleive this will initially grab 2GB of memory on startup and will grab up to to 3GB of memory total for the SGA. The "total" memory can be allocated to different peices of the SGA when needed but will never exceed 3GB.

Is this correct or would these settngs infringe on any available memory on a system that is already tight on memory?

Secondly, what happens if both these values are set to the same value?

Thanks to all who answer

Report message to a moderator

Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561849 is a reply to message #561847]

Wed, 25 July 2012 13:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams140.htm#REFRN10285

"Total memory usage can grow beyond the value of MEMORY_TARGET. For example, memory is allocated to PL/SQL tables and varrays regardless of the value of MEMORY_TARGET as long as memory is available at the operating system level."

Report message to a moderator

Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561850 is a reply to message #561847]

Wed, 25 July 2012 13:28

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you set SGA_TARGET then the SGA will not exceed this size unless you increase the value up to a maximum given by SGA_MAX_SIZE.

Regards
Michel

Report message to a moderator

Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561851 is a reply to message #561847]

Wed, 25 July 2012 13:30

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

The behaviour is very much platform specific, with many variations. I think that some platforms take the max_size, and swap out anything not used, for instance. It doesn't work at all with Linux if you have huge pages configured. With Solaris, you have to configure your project to use dynamic intimate shared memory.
I would love to see a conprehensive description of the behaviour on different platforms. If anyone wants to contribute detail, we can write it up, take the paper to Open World, and be famous.
--
Update:
Oh no! I didn't read the question properly! The above is relevant to 11g memory_target only.
I'm looking for an emoticon for "want to dig a hole and bury myself before getting flamed"
My only consolation is that the Black Swan made the same mistake.

[Updated on: Wed, 25 July 2012 13:46]

Report message to a moderator

Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561852 is a reply to message #561850]

Wed, 25 July 2012 13:54

BeefStu
Messages: 208
Registered: October 2011

Senior Member

so what is the benefit of setting them to the same value as opposed to different values. It seems to me, when different memory will only be grabbed when needed?

Report message to a moderator

Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561853 is a reply to message #561852]

Wed, 25 July 2012 14:04

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no benefit I can see as on most platforms max size is allocated at instance startup even if you don't use the difference with target.
On some latest os versions, Oracle is able to allocate only the target as the os allows it to allocate more afterwards but this was not the case on any os when 10g(R2) was released.

Regards
Michel

[Updated on: Wed, 25 July 2012 14:05]

Report message to a moderator

Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561854 is a reply to message #561852]

Wed, 25 July 2012 14:05

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

The whole point is that you set them to different values. Set the target to what you think you need, and the max to a higher value so that you can raise the target later if necessary. The target is a dynamic parameter, the max is static.

Report message to a moderator

Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561855 is a reply to message #561854]

Wed, 25 July 2012 14:22

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

I always set the sga_target less than sga_max_size so that
I can allocate memory to caches "on the fly" without having
to restart the db. In the follwing example I bump up sga_target
by 2 gigabytes and I allocate 1 gigabytes to the keep cache and
the other gigabyte to the recycle cache.

-- Parameters before changes

INSTANC VALUE                          NAME
------- ------------------------------ ---------------------
NDOCS1  15367929856                    sga_max_size
NDOCS1  12297699328                    sga_target
NDOCS1  2063597568                     db_keep_cache_size
NDOCS1  2063597568                     db_recycle_cache_size

ENDOCS1 > -- to make the change in memory (must be done on each instance):

ENDOCS1 > alter system set sga_target=14g scope=memory sid='NDOCS1';

System altered.

ENDOCS1 > alter system set db_keep_cache_size=3g scope=memory sid='NDOCS1';

System altered.

ENDOCS1 > alter system set db_recycle_cache_size=3g scope=memory sid='NDOCS1';

System altered.

ENDOCS1 > -- to make the changes permanent on next restart:

ENDOCS1 > alter system set sga_target=14g scope=spfile sid='*';

System altered.

ENDOCS1 > alter system set db_keep_cache_size=3g scope=spfile sid='*';

System altered.

ENDOCS1 > alter system set db_recycle_cache_size=3g scope=spfile sid='*';

System altered.

Report message to a moderator

SGA_TARGET & SGA_MAX_SIZE not equal [message #561861 is a reply to message #561855]

Wed, 25 July 2012 17:20

BeefStu
Messages: 208
Registered: October 2011

Senior Member

Thank you all for your great input

Report message to a moderator

Previous Topic:	Profile file
Next Topic:	ORA-00942: table or view does not exist

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 02:25:30 CST 2024