Oracle Database Memory Structure - AMM Vs ASMM - Memory Tuning

Automatic Memory Management - AMM Vs ASMM - Memory Tuning

Oracle Database 10g Automated SGA Memory Tuning [ID 257643.1]
Automatic Memory Management (AMM) on 11g [ID 443746.1]

PGA_AGGREGATE_TARGET --- PGA_AGGREGATE_LIMIT

>>> If you set this it will help in automating PGA

SGA_TARGET (10g) --- SGA_MAX_SIZE

>>> SGA memory pools are automatically sized

MEMORY_TARGET (11g) --- MEMORY_MAX_TARGET

>>> SGA and PGA both are automated

MEMORY_TARGET=0

>>> SGA is automatically tuned based on SGA_TARGET setting 

>>> PGA is automatically tuned based on PGA_AGGREGATE_TARGET setting 

MEMORY_TARGET=<VALUE>

>>> SGA is automatically tuned

>>> PGA is automatically tuned

SQL> select * from v$sgainfo;

NAME                                  BYTES RES     CON_ID

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

Fixed SGA Size                      8627248 No           0

Redo Buffers                        8146944 No           0

Buffer Cache Size                2969567232 Yes          0

In-Memory Area Size                       0 No           0

Shared Pool Size                  671088640 Yes          0

Large Pool Size                    33554432 Yes          0

Java Pool Size                     16777216 Yes          0

Streams Pool Size                         0 Yes          0

Shared IO Pool Size               184549376 Yes          0

Data Transfer Cache Size                  0 Yes          0

Granule Size                       16777216 No           0

Maximum SGA Size                 3707764736 No           0

Startup overhead in Shared Pool   230199392 No           0

Free SGA Memory Available                 0              0

14 rows selected.

SQL>

SQL> show parameter sga

NAME                                 TYPE        VALUE

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

allow_group_access_to_sga            boolean     FALSE

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     TRUE

sga_max_size                         big integer 3536M

sga_min_size                         big integer 0

sga_target                           big integer 3536M

unified_audit_sga_queue_size         integer     1048576

SQL>

SQL> show parameter pga

NAME                                 TYPE        VALUE

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

pga_aggregate_limit                  big integer 2348M

pga_aggregate_target                 big integer 1174M

SQL>

MEMORY_MAX_TARGET is not dynamic parameter 

MEMORY_TARGET is dynamic parameter

===========================================

SQL> show parameter target;

SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=3G SCOPE=SPFILE;

SQL> shut immediate;

SQL> show parameter target;

SQL> ALTER SYSTEM SET MEMORY_TARGET=2560m;

SGA_MAX_SIZE is not dynamic parameter 

SGA_TARGET is dynamic parameter 

===========================================

SQL> show parameter SGA_MAX_SIZE

SQL> ALTER SYSTEM SET SGA_MAX_SIZE=2G SCOPE=SPFILE;

SQL> shut immediate;

SQL>alter system set sga_target=160m;

SQL>show parameter sga_target;

Regards,

Mallik