To Enable Automatic Shared Memory Management (ASMM)
- You must use an spfile for the init.ora values
- sga_target parameter is set to a non-zero value
- statistics_level parameter set to to TYPICAL (the default) or ALL
- shared_pool_size must be set to a non-zero value
show parameter sga_max_size
show parameter statistics_level
show parameter spfile
To check current SGA
SELECT ((SELECT SUM(value)/1024/1024 FROM V$SGA) - (SELECT CURRENT_SIZE/1024/1024 FROM V$SGA_DYNAMIC_FREE_MEMORY)) "SGA_TARGET" FROM DUAL;
select sum(value) from v$sga;
select sum(bytes) from v$sgastat;
select sum(current_size) from v$sga_dynamic_components;
select * from v$sga_dynamic_free_memory;
To enable ASMM
ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
ALTER SYSTEM SET SGA_TARGET = 900M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
SGA_MAX_SIZE > SGA_TARGET enable you to increase memory
ALTER SYSTEM SET SGA_MAX_SIZE =1000M;
MEMORY_TARGET must be zero
ALTER SYSTEM SET MEMORY_TARGET = 0;
v$sga_dynamic_components
select
component,
current_size/1024/1024 "CURRENT_SIZE",
min_size/1024/1024 "MIN_SIZE",
user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",
last_oper_type "TYPE"
from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE TYPE
------------------------------ ------------ ---------- ------------------- -------------
shared pool 144 64 0 GROW
large pool 8 8 0 SHRINK
java pool 4 4 0 STATIC
streams pool 0 0 0 STATIC
DEFAULT buffer cache 40 40 0 SHRINK
KEEP buffer cache 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 STATIC
Shared IO Pool 4 0 0 GROW
Data Transfer Cache 0 0 0 STATIC
In-Memory Area 0 0 0 STATIC
ASM Buffer Cache 0 0 0 STATIC
16 rows selected.
v$sga_resize_ops
select
component,
oper_type,
oper_mode,
initial_size/1024/1024 "Initial",
TARGET_SIZE/1024/1024 "Target",
FINAL_SIZE/1024/1024 "Final",
status
from v$sga_resize_ops;
v$sga_target_advice
select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
290 .5 448176 1.6578 1636103
435 .75 339336 1.2552 1636103
580 1 270344 1 1201780
725 1.25 239038 .8842 907584
870 1.5 211517 .7824 513881
1015 1.75 201866 .7467 513881
1160 2 200703 .7424 513881
