SGA pool sizing

As an alternative to manually sizing the main SGA pools in an Oracle database, IBM recommends that you use Automatic Memory Management (AMM).

Automatic Memory Management is the simplest way to manage instance memory, and allows the Oracle database instance to automatically manage and tune it for you. The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. The maximum memory size serves as an upper limit so that you cannot accidentally set the target memory size too high, and so that enough memory is set aside for the Oracle database instance in case you do want to increase total instance memory in the future. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting the target memory size too low.

In the process of enabling Oracle AMM, the following settings should be used:

• Set MEMORY_TARGET and MEMORY_MAX_TARGET to a nonzero value
• Set the SGA_TARGET and the PGA_AGGREGATE_TARGET to zero