2009/07/21

Memory Tuning Tips

Oracle 10gR2

You can check the current size of components in sga using v$ views like:
V$SGA
V$SGASTAT
V$SGA_DYNAMIC_COMPONENTS

If you enabled ASMM, memory statistics will be calculated and indication of memory tuning can be found in the following v$ views:
V$SGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAM_POOL_ADVICE

Another interactive way to tune memory is through 'Memory Advisor' interface which can be found in 'Advisor Central' from Grid Control.

SGA_TARGET vs. SGA_MAX_SIZE
SGA_MAX_SIZE is static parameter, while SGA_TARGET is dynamic. The size of SGA_MAX_SIZE is allocated from system memory after system start. On some platforms, the difference (SGA_MAX_SIZE-SGA_TARGET) is in virtual memory, which onlys taks space from disk. While on other platforms (Windows and Linux), it takes memory from system, which means much more SGA_MAX_SIZE than SGA_TARGET can be resouce waste on production system.