13 ASMM( Automatic Shared Memory Management)

New features in 10g to help manage the SGA. When SGA_TARGET large then 0, it means the ASMM is enable. Disable the AMM by set MEMORY_TARGET to 0. after the setting, you can see the SGA_TARGET change from 0 to 608MB.

Enable ASMM

set the memory_target=0 is to disable the AMM. This will change the AMM to ASMM.

show parameter target

alter system set MEMORY_TARGET=0;

show parameter target

If you don’t have the AMM enable, then just set the SGA_TARGET to enable the ASMM. SGA_TARGET is the dynamic value as long as you set it small than SGA_MAX_SIZE.

alter system set sga_target=500M scope=both;

• SGA_TARGET must small than SGA_MAX_SIZE.
• SGA_MAX_SIZE is static value. To change the SGA_MAX_SIZE require bounce the oracle instance.

V$SGA_DYNAMIC_FREE_MEMORY

Show the free memory that we can add to the SGA. The difference between SGA_MAX_SIZE and SGA_TARGET. ( SGA_MAX_SIZE- SGA_TARGET)

show parameter sga

select * from V$SGA_DYNAMIC_FREE_MEMORY;

V$SGA_DYNAMIC_COMPONET

Show the size of the SGA dynamic components.

COLUMN component FORMAT A24

SELECT  component, current_size, min_size, max_size,user_specified_size FROM  v$sga_dynamic_components;

V$SGA_TARGET_ADVICE

We can use it to determine whether the SGA_TARGET is bigger enough or we can reduce the memory.

select * from V$SGA_TARGET_ADVICE;

IceRocket Tags: oracle,ASMM