11g New Features - Automatic Memory Management

Memory management in Oracle database is getting better rather I would say is becoming easier now. There was a time when each memory component of the Oracle had to be defined using parameters. Oracle first introduced dynamic memory resizing in 9i, moved to Automatic Shared memory management in 10g reducing the number of major parameters to size memory to 2 - SGA was controlled using a single parameter (SGA_TARGET) and PGA portion controlled by PGA_AGGREGATE_TARGET. Now oracle has gone one step ahead - 11g gives you the option of defining only one parameter "MEMORY_TARGET" to control both SGA and PGA. Now Oracle can dynamically exchange memory between SGA and PGA. Isn't that great...

Starting 11g, Oracle by default uses this new memory management feature known as "Automatic Memory Management". Now you should not be worried about whether PGA got over-allocated or has SGA got over-allocated; You can simply set MEMORY_TARGET and relax!!!

SIPRAS@11glab> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- -------
hi_shared_memory_address integer 0
memory_max_target big integer 608M
memory_target big integer 608M
shared_memory_address integer 0

You can also set maximum target too with the help of "MEMORY_MAX_TARGET" as was the case with "SGA_MAX_SIZE" . Some new (useful) views in 11g pertaining to automatic memory management :-

V$MEMORY_DYNAMIC_COMPONENTS -> find out how much has been allocated to each component along with minimum and maximum values it touched
V$MEMORY_TARGET_ADVICE -> will give you tuning advice for the MEMORY_TARGET

SIPRAS@11glab> SELECT component, current_size, min_size, max_size, last_oper_type FROM v$memory_dynamic_components;

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE LAST_OPER_TYP
------------------------------ ------------ ---------- ---------- -------------
shared pool 150994944 71303168 150994944 GROW
large pool 4194304 4194304 4194304 STATIC
java pool 12582912 4194304 12582912 GROW
streams pool 0 0 0 STATIC
SGA Target 381681664 381681664 381681664 STATIC
DEFAULT buffer cache 209715200 209715200 297795584 SHRINK
KEEP buffer cache 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 STATIC
Shared IO Pool 0 0 0 STATIC
PGA Target 16777216 16777216 16777216 STATIC
ASM Buffer Cache 0 0 0 STATIC

MEMORY_TARGET is a dynamic parameter whereas MEMORY_MAX_TARGET is not. If you set SGA_TARGET and PGA_AGGREGATE_TARGET with automatic memory management and they are less than MEMORY_TARGET then those values will act as the minimum values for SGA and PGA respectively. In case you set it to more than MEMORY_TARGET, then you will get the error "ORA-00844: Parameter not taking MEMORY_TARGET into account, see alert log for more information".

Reference :- Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-04