AskTom Oracle:SGA Size

Hi

I am configuraring a new Oracle 10g instance for production purposes. I read from Oracle 10g new 
features for administrators exam guide (from Oracle Press) that when we use sga_target we should 
not set any parameters for the 4 dynamic tunable components, buffer cache, shared pool, java pool 
and large pool to reduce database's ability to adapt to database wrkload changes. How true is this? 
If I set sga_target to 1000M how much memory will these 4 components consume to start?

I have another question about sga_target & sga_max_size. If we set sga_max_size bigger than 
sga_target will sga_target always be lower than sga_max_size? For example

sga_target = 1200M
sga_max_size = 1600M

Does this make sense? Will Oracle allocate memory up to 1600M sometime or maximum it will reach 
1200M. What's the point combining these two parameters, shouldnt we just use sga_target and forget 
about sga_max_size? 

Followup December 20, 2004 - 8am Central time zone:

how true is it?

100% true, if you set the sga_target, the other 4 components are set for you.


if you set the SGA_TARGET to 1000m, the 4 components will be sized to consume 1000m.  consider:

SQL> show parameter sga_target
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------- sga_target big integer 1000M


SQL> show sga
 
Total System Global Area 1048576000 bytes
Fixed Size                   782424 bytes
Variable Size             259002280 bytes
Database Buffers          788529152 bytes
Redo Buffers                 262144 bytes


SQL> show parameter pool
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 12373196
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0
SQL>


basically, Oracle will setup reasonable initial sized pools (if you know how to peek at _ parameters, you'll see them: __java_pool_size 4194304 __large_pool_size 4194304 __shared_pool_size 247463936 ) and will put the rest in the buffer cache. Over time, if the pools need more, it'll steal from the buffer cache and increase them.


sga_target has to be less than or equal to sga_max_size.  It depends on the OS how the memory is 
reserved, but basically your 1200/1600 would have you start with an SGA of 1,200 meg that could be 
grown by you to 1600m (using alter system)


SQL> show parameter sga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1200M
sga_target                           big integer 1008M

SQL> alter system set sga_target = 1100m;
 
System altered.
 
SQL> alter system set sga_target = 1300m;
alter system set sga_target = 1300m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size