The V$SGA_TARGET_ADVICE view provides information that helps us in deciding optimal value for  SGA_TARGET. MMON background process gather statistics about sga_target usage and update the V$SGA_TARGET_ADVICE view.

1
2
3
4
5
6
7
8

a. DB_CACHE_ADVICE should be ON

SQL> show parameter db_cache_advice
STATISTICS_LEVEL should be TYPICAL/ALL.

SQL> show parameter statistics_level

SQL> select * from v$sga_target_advice order by sga_size;