Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SGA_MAX_SIZE on Solaris

Re: SGA_MAX_SIZE on Solaris

From: Tim Johnston <tjohnston_at_quallaby.com>
Date: Thu, 11 Mar 2004 21:32:29 -0500
Message-ID: <4051213D.3020706@quallaby.com> 





I hadn't played with this setting before so I decided to perform a quick 
test...  Here is my instance (on Solaris 9) before the parameter is set...




$ sqlplus "/ as sysdba"



SQL*Plus: Release 9.2.0.3.0 - Production on Thu Mar 11 20:35:29 2004



Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.



Connected to an idle instance.



SQL> startup


ORACLE instance started.



Total System Global Area  319902492 bytes

Fixed Size                   455452 bytes
Variable Size             234881024 bytes
Database Buffers           83886080 bytes
Redo Buffers                 679936 bytes


Database mounted.


Database opened.


SQL>



Now, I'll set sga_max_size to 1024M and bounce the instance...  Note, 
sga_max_size to 1024 M was the only change I made...




$ sqlplus "/ as sysdba"



SQL*Plus: Release 9.2.0.3.0 - Production on Thu Mar 11 20:38:20 2004



Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.



Connected to an idle instance.



SQL> startup


ORACLE instance started.



Total System Global Area 1074878116 bytes

Fixed Size                   456356 bytes
Variable Size             989855744 bytes
Database Buffers           83886080 bytes
Redo Buffers                 679936 bytes


Database mounted.


Database opened.


SQL>



That makes me think that that memory is allocated at startup...  A look 
at ipcs seems to confirm this (this is the only instance running on the 
box)...




$ ipcs -ma


IPC status from <running system> as of Thu Mar 11 20:41:45 EST 2004

T         ID      KEY        MODE        OWNER    GROUP  CREATOR   
CGROUP NATTCH      SEGSZ  CPID  LPID   ATIME    DTIME    CTIME
Shared Memory:
m        700   0          --rw-r-----   oracle      dba   oracle      
dba      6    4194304  2372  2388 20:38:28 20:39:12 20:38:21
m        102   0          --rw-r-----   oracle      dba   oracle      
dba      6  536870912  2372  2388 20:38:28 20:39:12 20:38:21
m        103   0          --rw-r-----   oracle      dba   oracle      
dba      6  536870912  2372  2388 20:38:28 20:39:12 20:38:21
m        104   0x3187837c --rw-r-----   oracle      dba   oracle      
dba      6    4194304  2372  2388 20:38:28 20:39:13 20:38:21


$




And finally top...  Here's when my instance is down...



load averages:  0.039062,  0.089844,  0.085938                    20:56:55
36 processes:  35 sleeping, 1 on cpu
CPU states:     % idle,     % user,     % kernel,     % iowait,     % swap


Memory: 1152M real, 752M free, 514M swap in use, 1517M swap free



   PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND


  2493 oracle     1  50    0 1592K 1184K cpu      0:00  0.68% top
  2307 oracle     1  59    0 4312K 3640K sleep    0:04  0.17% xterm
  2308 oracle     1  59    0 1872K 1360K sleep    0:00  0.04% ksh
     1 root       1  59    0 1208K  280K sleep    0:03  0.03% init
   211 root       1  59    0 1040K  688K sleep    0:00  0.01% utmpd
   393 root       7  59    0 2600K 2280K sleep    2:22  0.00% mibiisa
   366 root       1  59    0 2152K 1576K sleep    2:00  0.00% snmpdx
   227 root       1  59    0 4360K 1592K sleep    0:02  0.00% sendmail
   197 root      19  59    0 2992K 2344K sleep    0:01  0.00% nscd
   134 root       1  59    0 2560K 1864K sleep    0:01  0.00% inetd
 18767 oracle     1  59    0   15M 5320K sleep    0:00  0.00% tnslsnr
   158 root       3  59    0 4040K 2088K sleep    0:00  0.00% automountd
   174 root      17  59    0 4152K 2048K sleep    0:00  0.00% syslogd
   378 root       2  59    0 3536K 2040K sleep    0:00  0.00% snmpXdmid
   155 daemon     1  59    0 2520K 1736K sleep    0:00  0.00% statd






Now, I'll start it up...



load averages:  0.187500,  0.121094,  0.097656                    20:57:48
41 processes:  40 sleeping, 1 on cpu
CPU states:     % idle,     % user,     % kernel,     % iowait,     % swap


Memory: 1152M real, 567M free, 1570M swap in use, 460M swap free



   PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND


  2512 oracle     1   0    0 1592K 1184K cpu      0:00  0.68% top
  2505 oracle     1  59    0 1083M  184M sleep    0:00  0.57% oracle
  2501 oracle    11  59    0 1091M  182M sleep    0:00  0.17% oracle
  2499 oracle    15  59    0 1088M  183M sleep    0:00  0.16% oracle
  2307 oracle     1  59    0 4312K 3640K sleep    0:04  0.12% xterm
  2503 oracle    11  59    0 1086M  183M sleep    0:00  0.12% oracle
  2497 oracle     1  59    0 1084M  181M sleep    0:00  0.08% oracle
  2507 oracle     1  59    0 1083M  179M sleep    0:00  0.06% oracle
   366 root       1  59    0 2152K 1576K sleep    2:00  0.05% snmpdx
   197 root      19  59    0 2992K 2344K sleep    0:01  0.02% nscd
  2308 oracle     1  49    0 1872K 1360K sleep    0:00  0.01% ksh
 18767 oracle     1  59    0   15M 5320K sleep    0:00  0.01% tnslsnr
   393 root       7  59    0 2600K 2280K sleep    2:22  0.00% mibiisa
     1 root       1  59    0 1208K  280K sleep    0:03  0.00% init
   227 root       1  59    0 4360K 1592K sleep    0:02  0.00% sendmail






So...  The process size is immediate allocated but it doesn't need it 
all in real memory (RES)...  However, swap is allocated for the entire 
process size... 



Dennis...  Given that, you'll probably have issues doing what you want 
to do...



Mike...  Maybe this is an OS dependent behavior?



Tim





Michael Milligan wrote:



>SGA_MAX_SIZE tells Oracle the maximum allowed memory allocation for the SGA.

>You can dynamically alter the size of the SGA up to SGA_MAX_SIZE. Here's why

>you want to set it: it has a default value. The default value of

>SGA_MAX_SIZE is the value for sga size on startup - not too much. Typically

>not a lot more than the shared_pool_size value on startup (maybe 20MB more).

>Efictively, this leaves you no room to increase the size of the sga. So set

>SGA_MAX_SIZE to a larghe value, like 256M, or whatever. It doesn't load any

>more memory at start up, but allows you to increase up to this value.

>

>-----Original Message-----

>From: DENNIS WILLIAMS [mailto:DWILLIAMS_at_LIFETOUCH.COM]

>Sent: Thursday, March 11, 2004 3:08 PM

>To: 'oracle-l_at_freelists.org'

>Subject: SGA_MAX_SIZE on Solaris

>

>

>Has anyone experimented with SGA_MAX_SIZE? Does Oracle request that amount

>of memory on startup? If memory of the components like SHARED_POOL_SIZE and

>DB_CACHE_SIZE are smaller than SGA_MAX_SIZE, does Oracle only request enough

>memory for the components? If the size of one of the components is reduced

>dynamically, does Solaris eventually reclaim that space?

>   I have a test system with many Oracle9i instances. Most are little used,

>and I allocate only a small amount of memory. However, now and then a

>development group will start pounding an instance. Often they run out of

>memory. So I have to audit the memory settings of all instances, then notify

>developers on other instances that I need to bounce their instance to

>retrieve some memory. A hassle for everyone concerned.

>   I'm wondering if I can set the SGA_MAX_SIZE high on all instances, but

>then just allocate a small amount of memory. Then if an instance needs more

>memory, I could allocate it dynamically, and also dynamically reduce the

>memory allocated in other instances.

>   Any thoughts appreciated.

>

>Dennis Williams

>DBA


>Lifetouch, Inc.

>dwilliams_at_lifetouch.com 

>

>----------------------------------------------------------------

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>----------------------------------------------------------------

>To unsubscribe send email to:  oracle-l-request_at_freelists.org

>put 'unsubscribe' in the subject line.

>--

>Archives are at http://www.freelists.org/archives/oracle-l/

>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

>-----------------------------------------------------------------

>

>

>This e-mail, including attachments, may include confidential and/or

>proprietary information, and may be used only by the person or entity to

>which it is addressed. If the reader of this e-mail is not the intended

>recipient or his or her authorized agent, the reader is hereby notified that

>any dissemination, distribution or copying of this e-mail is prohibited. If

>you have received this e-mail in error, please notify the sender by replying

>to this message and delete this e-mail immediately.

>----------------------------------------------------------------

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>----------------------------------------------------------------

>To unsubscribe send email to:  oracle-l-request_at_freelists.org

>put 'unsubscribe' in the subject line.

>--

>Archives are at http://www.freelists.org/archives/oracle-l/

>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

>-----------------------------------------------------------------

>  

>



-- 
Regards,
Tim Johnston
Tel: 978-322-4226
Fax: 978-322-4100


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Thu Mar 11 2004 - 20:29:06 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US