Home > oracle_11g_new_features > oracle 11g new feature—Automatic Memory Management(AMM)

oracle 11g new feature—Automatic Memory Management(AMM)

This article  explains Oracle Database 11g New Feature(AMM).

Memory Parameters:

oracle 11g introduced new feature called automatic memory management(AMM).We can expand or shrink both sga and pga based on the instance demands.

To enable this feature we need to set the following parameters.

1)memory_max_target
2)memory_target

SQL> show parameter memory

NAME                                 TYPE        VALUE
———————————— ———– ——————————
memory_max_target                    big integer 0
memory_target                        big integer

MEMORY_TARGET :this parameter sets the system-wide usable memory that will be used by the instance for SGA and PGA.
MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

In oracle 10g we need to specify sga_target,pga_aggregate_target parameters (Oracle Automatic Shared Memory Management (ASMM)) then oracle will automatically allocated size for Buffer cache,Shared pool, large pool etc.

Oracle 11g introduced new parameter called memory_target parameter which is used for sga and pga configuration.

when we specify memory_target parameter then we don’t need to specify sga_target,pga_aggregate_target parameters for memory configuration.

Enabling automatic memory management(AMM):

on linux system make sure you have configure /dev/shm before enabling automatic memory management.

/dev/shm is nothing but implementation of traditional shared memory concept. It is an efficient means of passing data between programs.

The shared memory file system should be big enough to accommodate the MEMORY_TARGET and MEMORY_MAX_TARGET values, or Oracle will throw the following error.

ORA-00845: MEMORY_TARGET not supported on this system

We can not use hugepage settings on linux system if we want to use automatic memory management feature in oracle 11g.

dbsl9002<oracle:oradv11>:/home/oracle=>df -h | grep tmpfs
tmpfs                1011M  192M  820M  19% /dev/shm

SQL> alter system set memory_target=512M scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2228200 bytes
Variable Size             377487384 bytes
Database Buffers          150994944 bytes
Redo Buffers                3751936 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME                                 TYPE        VALUE
———————————— ———– ——————————
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 512M
sga_target                           big integer 384M
SQL> show parameter memory

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hi_shared_memory_address             integer     0
memory_max_target                    big integer 512M
memory_target                        big integer 512M
shared_memory_address                integer     0
SQL> show parameter pga

NAME                                 TYPE        VALUE
———————————— ———– ——————————
pga_aggregate_target                 big integer 128M

In the above scenario the lower bounds of  PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to
128 MB and 384 MB, respectively.

Monitoring and Tuning automatic memory management:

Oracle 11g includes four new V$ views to support automatic memory management:

V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE

To display current status of the memory components, use the following query:

COLUMN component FORMAT A30

SQL> SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS;

COMPONENT                      CURRENT_SIZE        MIN        MAX
—————————— ———— ———- ———-
shared pool                             224        224        224
large pool                                4          4          4
java pool                                 4          4          4
streams pool                              0          0          0
SGA Target                              384        384        384
DEFAULT buffer cache                    144        144        144
KEEP buffer cache                         0          0          0
RECYCLE buffer cache                      0          0          0
DEFAULT 2K buffer cache                   0          0          0
DEFAULT 4K buffer cache                   0          0          0
DEFAULT 8K buffer cache                   0          0          0

COMPONENT                      CURRENT_SIZE        MIN        MAX
—————————— ———— ———- ———-
DEFAULT 16K buffer cache                  0          0          0
DEFAULT 32K buffer cache                  0          0          0
Shared IO Pool                            0          0          0
PGA Target                              128        128        128
ASM Buffer Cache                          0          0          0

16 rows selected.

SQL> SELECT * FROM v$memory_target_advice ORDER BY memory_size;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
———– —————— ———— ——————- ———-
256                 .5           42              1.0244          0
384                .75           41                   1          0
512                  1           41                   1          0
640               1.25           41                   1          0
768                1.5           41                   1          0
896               1.75           41                   1          0
1024                  2           41                   1          0

7 rows selected.

To know how Oracle has modified the memory area sizes by time, issue the following query:
select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME;

 

 

 

 

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: