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