Oracle 11g Memory Management Methods

Instance

SGA

PGA

Description

Initialization Parameters

Auto

n/a

n/a

The database tunes the size of the instance based on a single instance target size.

You set:

• Total memory target size for the database instance (MEMORY_TARGET)

• Optional maximum memory size for the database instance (MEMORY_MAX_TARGET)

n/a

Auto

Auto

The database automatically tunes the SGA based on an SGA target.

The database automatically tunes the PGA based on a PGA target.

You set:

• SGA target size (SGA_TARGET)

• Optional SGA maximum size (SGA_MAX_SIZE)

• Instance PGA target size (PGA_AGGREGATE_TARGET)

n/a

Auto

Manual

The database automatically tunes the SGA based on an SGA target.

You control the PGA manually, setting the maximum work area size for each type of SQL operator.

You set:

• SGA target size (SGA_TARGET)

• Optional SGA maximum size (SGA_MAX_SIZE)

• PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE

n/a

Manual

Auto

You control the SGA manually by setting individual component sizes.

The database automatically tunes the PGA based on a PGA target.

You set:

• Shared pool size (SHARED_POOL_SIZE)

• Buffer cache size (DB_CACHE_SIZE)

• Large pool size (LARGE_POOL_SIZE)

• Java pool size (JAVA_POOL_SIZE)

• Streams pool size (STREAMS_POOL_SIZE)

• Instance PGA target size (PGA_AGGREGATE_TARGET)

n/a

Manual

Manual

You must manually configure SGA component sizes.

You control the PGA manually, setting the maximum work area size for each type of SQL operator.

You must manually configure SGA component sizes. You set:

• Shared pool size (SHARED_POOL_SIZE)

• Buffer cache size (DB_CACHE_SIZE)

• Large pool size (LARGE_POOL_SIZE)

• Java pool size (JAVA_POOL_SIZE)

• Streams pool size (STREAMS_POOL_SIZE)

• PGA work area parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE