Memory parameters in 11g - MEMORY_TARGET and MEMORY_MAX_TARGET

Few days back, I was checking metalink and came across few good notes on 11g memory management.
Though we are not using 11g in our development environment, i have downloaded a copy of 11g rel1 on my windows machine.

Testing out these new paarmeters gave me an insight on how Oracle has improvised its memory management.

Oracle has introduced 2 new parameters (along with many others!) - MEMORY_TARGET and MEMORY_MAX_TARGET

Using these parameters, you can manage SGA and PGA together rather than managing them separately (using SGA_TARGET, SGA_MAX_SIZE , PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY in 10g)

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_SIZE to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.

For instance :
If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

• SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
• SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.
• SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
• PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).

With this version, oracle has become smart as in exchanging memory between SGA and PGAs. This is a huge achievement.
When starting up, Oracle takes up memory equal to MEMORY_TARGET (or MEMORY_MAX_TARGET if mentioned) from Operating System RAM and manage its reqources within itself.
This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.


I tested out few of these experiments mentioned above. Here are the results:

Test 1: When Memory_target is 1G

init.ora parameter
> memory_target=1073741824
> sga_max_size=0

SQL> startup
ORACLE instance started.

Total System Global Area 640303104 bytes
Fixed Size 1335024 bytes
Variable Size 226492688 bytes
Database Buffers 406847488 bytes
Redo Buffers 5627904 bytes
Database mounted.
Database opened.

>> It seems around 600M is used for SGA. The decesion of using 600 MB of SGA is of Oracle itself.

SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- -------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 612M
sga_target big integer 0

Test 2: When MEMORY_TARGET is 2g (with no change in any other parameter)

SQL> alter system set memory_target=2048m scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1288978432 bytes
Fixed Size 1339344 bytes
Variable Size 226492464 bytes
Database Buffers 1052770304 bytes
Redo Buffers 8376320 bytes
Database mounted.
Database opened.

SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1232M
sga_target big integer 0

Test 3: When sga_max_size/sga_target=700M and memory_target=1G

SQL> startup
ORACLE instance started.

Total System Global Area 732352512 bytes
Fixed Size 1335696 bytes
Variable Size 192941680 bytes
Database Buffers 532676608 bytes
Redo Buffers 5398528 bytes
Database mounted.
Database opened.
SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 700M
sga_target big integer 700M
SQL>
SQL> sho parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1G
memory_target big integer 1G
shared_memory_address integer 0

This time, oracle has acknowledged the SGA_MAX_SIZE parameter.