Important Initilize Parameter for Oracle 11G: SGA_TARGET/SGA_MAX_TARGET/MEMORY_TARGET/MEMORY_MAX_TARGET

SGA_TARGET

Property	Description
Parameter type	Big integer
Syntax	SGA_TARGET = integer [K | M | G]
Default value	0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests)
Modifiable	ALTER SYSTEM
Range of values	64 MB to operating system-dependent
Basic	Yes

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)

• Shared pool (SHARED_POOL_SIZE)

• Large pool (LARGE_POOL_SIZE)

• Java pool (JAVA_POOL_SIZE)

• Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to nonzero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

• Log buffer

• Other buffer caches, such as KEEP, RECYCLE, and other block sizes

• Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

In the Default value field, IMMEDIATE mode autotuning requests are necessary to avoid ORA-04031 errors. The DEFERRED and IMMEDIATE modes are reflected in the OPER_MODE column of the V$MEMORY_RESIZE_OPS view.

If Automatic Memory Management is enabled (MEMORY_TARGET is set to a positive value) and SGA_TARGET is also set to a positive value, the SGA_TARGET 

value acts as the minimum value for the size of the SGA.

 

SGA_MAX_SIZE

Property	Description
Parameter type	Big integer
Syntax	SGA_MAX_SIZE = integer [K | M | G]
Default value	Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
Modifiable	No
Range of values	0 to operating system-dependent

SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.

On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters. This causes more address space to be reserved for expansion of the SGA.

On Windows 32-bit platforms, the default value of SGA_MAX_SIZE is the largest of the following values:

• 60% of MEMORY_TARGET, if specified

• 60% of MEMORY_MAX_TARGET, if specified

• 25% of the total available virtual address space

 

MEMORY_TARGET

Property	Description
Parameter type	Big integer
Syntax	MEMORY_TARGET = integer [K | M | G]
Default value	0 (SGA autotuning is disabled for DEFERRED mode autotuning requests, but allowed for IMMEDIATE mode autotuning requests)
Modifiable	ALTER SYSTEM
Range of values	152 MB to MEMORY_MAX_TARGET
Basic	No

MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.

MEMORY_TARGET should be set higher than or equal to the sum of the current sizes of the SGA and PGA.

In a text-based initialization parameter file, if you omit MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, then the database automatically setsMEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a nonzero value, provided that it does not exceed the value ofMEMORY_MAX_TARGET.

Total memory usage can grow beyond the value of MEMORY_TARGET. For example, memory is allocated to PL/SQL tables and varrays regardless of the value ofMEMORY_TARGET as long as memory is available at the operating system level.

In the Default value field, IMMEDIATE mode autotuning requests are necessary to avoid ORA-04031 errors. The DEFERRED and IMMEDIATE modes are reflected in the OPER_MODE column of the V$MEMORY_RESIZE_OPS view.

Note:

The default value of SGA_MAX_SIZE depends on the values of MEMORY_TARGET and MEMORY_MAX_TARGET.

See Also:

Oracle Database Administrator's Guide for more information about managing memory

 

 

MEMORY_MAX_TARGET

Property	Description
Parameter type	Big integer
Syntax	MEMORY_MAX_TARGET = integer [K | M | G]
Default value	0
Modifiable	No
Range of values	0 to the physical memory size available to the Oracle Database
Basic	No

MEMORY_MAX_TARGET specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter. See the description of MEMORY_TARGET for more information about how the settings of MEMORY_MAX_TARGET and MEMORY_TARGET affect each other.