ASSM in Oracle 11gR2

DB setting before changing the memory in the Database

Below Configuration is Automatic Shared Memory Management – For the SGA

If you want to exercise more direct control over the size of the SGA, you can disable automatic memory management and enable automatic shared memory management. This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of current workload without requiring any additional configuration.

Automatic Memory Management – For Both the SGA and Instance PGA

Beginning with Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

To achieve this, two new parameters have been introduced named MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

Add the values of PGA_AGGREGATE_TARGET and SGA_TARGET from the first section . In our case it would sum to 2746MB ( 2000+746).

ALTER SYSTEM SET memory_max_target=2746M SCOPE=SPFILE;

ALTER SYSTEM SET memory_target=2746M SCOPE=SPFILE;

ALTER SYSTEM SET sga_target=0 SCOPE=SPFILE;

ALTER SYSTEM SET pga_aggregate_target=0 SCOPE=SPFILE;

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2104479744 bytes

Fixed Size                         2229984 bytes

Variable Size                 1006635296 bytes

Database Buffers          1090519040 bytes

Redo Buffers                    5095424 bytes

Database mounted.

Database opened.

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   2752M

memory_target                               big integer   2752M

parallel_servers_target                    integer         32

pga_aggregate_target                    big integer   0

sga_target                                     big integer   0

Note: In case you do not specify any value for MEMORY_MAX_TARGET and only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET

Refer the Oracle Meta link for More details  443746.1