Since you must bounce the database to increase above SGA_MAX_SIZE, suppose you have the following situation:

- Three database instances on your machine;
- You machine has 2 GB RAM;
- Under normal operation, each database needs 500MB RAM to serve the users;
- Once a month one database (DB1) does extra-heavy batch processing that requires SGA of 1GB

Note that once a month the total SGA requirement would be 2GB, which matches or exceeds the machine RAM.

Therefore I would set
- my MAX_SGA on DB1 to 1.3GB and DB2 & DB3 to 800M
- my SGA_TARGET on DB1, DB2 and DB3 to 500M
- once a month I could simply change DB2 and DB3 to 350M and increase DB1 to 1GB, run the batch job with minimum swapping, and then restore the SGAs to regular operation WITHOUT bouncing the databases.

This is an idealistic scenario - it does not take into account the PGA, OS or other memory requirements, but it does give you the idea.

Hans,

Doesn't Oracle pre-allocate SGA_MAX_SIZE worth of physical memory? It seems to me that the actual size of the shared memory segment allocated (at least under a *nix system) is fixed so it's not as though I can create three instances with 1GB sga_max_size on a single box with 2GB total physical RAM. SGA_TARGET just seems to address the allocation of sga_max_size to service various SGA components.

That quote seems to support my question, to be honest. It doesn't say that SGA_MAX_SIZE is dynamic at all, and therefore merely by specifying it, aren't I allocating that that OS RAM to Oracle during instance startup?

Doesn't that mean that if my sga_target < sga_max_size then all I'm really doing is running a risk of wasting RAM?

Let's try this quote, from the Concepts manual, Chapter 8

Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter.

SGA_MAX_SIZE is not dynamic. You can NOT change the value.

However, as compared to previous versions of Oracle, in 10g SGA_MAX_SIZE does not define the size of memory allocated, but rather the MAXIMUM size that CAN be allocated.

Further, (although it's been over 6 months ago, I am assuming memory works correctly, as I have taught and taken many courses since them) in my Oracle 10g DBA Workshop classes, I actually demonstrated changing the SGA_TARGET and proving that the memory is actually released (using top) when the target is reduced. It is not instantaneous, as the buffers need to be cleaned.

Message was edited by:
Hans Forbrich

Just to clarify things up,

Oracle will allocate SGA size equal to SGA_MAX_SIZE from system initially.
SGA_TARGE can be smaller than SGA_MAX_SIZE because they are buffer caches, such as KEEP, RECYCLE, and other block sizes, not affected by SGA_TARGET.

See the result below,

SYS@etest> show sga



Total System Global Area  264241152 bytes

Fixed Size                  1301408 bytes

Variable Size             216540256 bytes

Database Buffers           46137344 bytes

Redo Buffers                 262144 bytes

SYS@etest> show parameter sga



NAME                                 TYPE        VALUE


-----------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     TRUE

sga_max_size                         big integer 252M

sga_target                           big integer 200M

Oracle SGA size is 264241152/1024/1024=252M equal to SGA_MAX_SIZE while SGA_TARGET is 200M
Since I don't have other buffers set, Where the 50M goes ?

SYS@etest> select * from v$sgainfo

  2  /



NAME                                  BYTES RES


---------- ---

Fixed SGA Size                      1301408 No

Redo Buffers                         262144 No

Buffer Cache Size                  46137344 Yes

Shared Pool Size                  150994944 Yes

Large Pool Size                     4194304 Yes

Java Pool Size                      4194304 Yes

Streams Pool Size                         0 Yes

Granule Size                        4194304 No

Maximum SGA Size                  264241152 No

Startup overhead in Shared Pool    37748736 No

Free SGA Memory Available          54525952



11 rows selected.

It went to Free SGA Memory Available. Hence you can dynamically increase SGA_TARGET without reboot instance.

An "ipcs -ma" reveal that Oracle did allocate 252M from OS

T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NATTCH      SEGSZ  CPID  LPID   ATIME    DTIME    CTIME 

Shared Memory:

m       5001   0x753f960c --rw-r-----   oracle      dba   oracle      dba     21  264249344 25248 25977 13:39:56 13:39:56 12:11:16

You, of course, have SGA_TARGET set to a small value, say 1G?