Memory Management with 11G

Oracle Memory Managment now make easier with Oracle 11g.

In the previous release (Oracle10g) we need to set TWO parameter for automatic memory management.

1. SGA_TARGET = xxx G

2. PGA_AGGREGATE_TARGET = xxx G

then oracle will automatically allocated size for Buffer cache,Shared pool, large pool etc.

Now In Oracle 11g will reduce two parameter (SGA_TARGET, PGA_AGGREGATE_TARGET) and introduce one new parameter called "MEMORY_TARGET". Which use for SGA or PGA size configuration.

When we specify MEMORY_TARGET parameter for instance then we no need to specify SGA_TARGET or PGA_AGGREGATE_TARGET parameter for memory configuration. Oracle will automatically allocate memory for memory parameter.

How to enable automatic memory management?

As previous parameter SGA_MAX_SIZE in 11g also one new parameter MEMORY_MAX_TARGET which is maximum limit for instance memory for oracle database.

Current database is not configured for MEMORY_TARGET parameter

SQL> show parameter target
NAME TYPE VALUE

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

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 0

memory_target big integer 0

pga_aggregate_target big integer 25M

sga_target big integer 172M

1. First set MEMORY_MAX_TARGET parameter for maximum instance memory limit.

SQL> alter system set memory_max_target=200M scope=SPFILE;
System altered.

NOTE: memory_target=(sga_target+pga_aggregate_target)

2. Shutdown & startup database

SQL> shutdown immediate

SQL> startup

NOTE: ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account, see alert log for more information

SQL> --Above error occur when MEMORY_MAX_TARGET parameter value is lower then SGA_MAX_SIZE parameter value. so either remove SGA_MAX_SIZE parameter or Increase MEMORY_MAX_TARGET parameter value

SQL> create pfile='c:\temp\init.ora' from spfile;
File created.
SQL> --after editing recreate new spfile.SQL> create spfile from pfile='c:\temp\init.ora';
File created.

SQL> startup

3. SET MEMORY_TARGET parameter to NON-ZERO & set "0" to SGA or PGA_AGGREGATE_TARGET parameter.

SQL> alter system set memory_target=200m;
System altered.
SQL> alter system set sga_target=0;
System altered.
SQL> alter system set pga_aggregate_target=0;
System altered.
SQL> show parameter target
NAME TYPE VALUE

------------------------------------ ----------- -----------archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 200M

memory_target big integer 200M

pga_aggregate_target big integer 0

sga_target big integer 0

Monitoring and Tuning automatic memory management

New V$ view : V$MEMORY_DYNAMIC_COMPONENTS show the current size of SGA and PGA.

and New V$ view: v$MEMORY_TARGET_ADVICE provides advice for the memory_target init parameter

read more http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/memory003.htm#BGBJAHEJ