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:/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;
