Re: SGA_MAX_SIZE on Solaris

From: Tim Johnston <tjohnston@xxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Thu, 11 Mar 2004 21:32:29 -0500

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@xxxxxxxxxxxxx]
Sent: Thursday, March 11, 2004 3:08 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

References:
- RE: SGA_MAX_SIZE on Solaris
  - From: Michael Milligan

Re: SGA_MAX_SIZE on Solaris

Other related posts: