Should i enable automatic SGA tuning ?

Automatic SGA tuning let oracle decide when to move memory between db cache and other pools, but it's possible that sometimes when oracle tries to resize a pool and don't find enough free chunks in other pools the database appears to hang.

The following query help me to monitor the resize operations :

select component,oper_type,status,count(*) from (select

        component,

        oper_type,

        oper_mode,

        parameter,

        initial_size,

        target_size,

        final_size,

        status,

        to_char(start_time,'dd-mon hh24:mi:ss') start_time,

        to_char(end_time,'dd-mon hh24:mi:ss')   end_time

from

        v$sga_resize_ops) group by component,oper_type,status; 

This query help me to determine which component oracle could not shrink or grow.

If you get too many ORA-04031 errors with ASMM enabled, i recommend you to turn it off first by setting sga_target = 0.

You should set a lower limit for each pool, so that oracle will not try to shrink it below the limit.

Sources :

• https://jonathanlewis.wordpress.com/2006/12/04/resizing-the-sga/ 
• https://jonathanlewis.wordpress.com/2007/04/16/sga-resizing/ 
• http://www.oraclemagician.com/white_papers/SGA_resizing.pdf