Home » RDBMS Server » Server Administration » SGA_TARGET & SGA_MAX_SIZE not equal (oracle 10.2.0.4 solaris 2.10)
SGA_TARGET & SGA_MAX_SIZE not equal [message #561847] Wed, 25 July 2012 13:11 Go to next message
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

Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561849 is a reply to message #561847] Wed, 25 July 2012 13:19 Go to previous messageGo to next message
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."
Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561850 is a reply to message #561847] Wed, 25 July 2012 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561851 is a reply to message #561847] Wed, 25 July 2012 13:30 Go to previous messageGo to next message
John Watson
Messages: 8919
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 Go to previous messageGo to next message
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?
Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561853 is a reply to message #561852] Wed, 25 July 2012 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 8919
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.
Re: SGA_TARGET & SGA_MAX_SIZE not equal [message #561855 is a reply to message #561854] Wed, 25 July 2012 14:22 Go to previous messageGo to next message
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.
SGA_TARGET & SGA_MAX_SIZE not equal [message #561861 is a reply to message #561855] Wed, 25 July 2012 17:20 Go to previous message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thank you all for your great input
Previous Topic: Profile file
Next Topic: ORA-00942: table or view does not exist
Goto Forum:
  


Current Time: Tue Mar 19 06:09:22 CDT 2024