SGA_MAX_SIZE and Memory_Target Parameter in Oracle 10g & 11g

SGA_MAX_SIZE and Memory_Target Parameter in Oracle 10g & 11g

10g Feature :-

SGA_MAX_SIZE : Not Dynamic

SGA_TARGET     : Dynamic

SGA_MAX_SIZE & SGA_TARGET = Oracle introduces this feature in 10g with Automatic Shared Memory Management (ASMM) where oracle manages only sga and living PGA to parameter pga_aggregate_target.

We can say like this SGA_MAX_SIZE parameter value is the maximum size you can increase for your SGA.

Ex:- If SGA_MAX_SIZE=10gb and SGA_TARGET=6GB in future if you want to increase the size of SGA you can increase only up to 10gb.

> SGA_TARGET : When you set SGA_TARGET to any value the Automatic Shared Memory Management (ASMM) is enabled the main advantage of ASMM is no need to worry for a DBA to size the SGA components.

> But still PGA is not managed here in 10g.

Note:-

If you are using a server parameter file (SPFILE), the database remembers the sizes of the  automatically tuned SGA components across instance shutdowns.

As a result, the database instance does not need to learn the characteristics of the workload again each  time the instance is started.

The instance can begin with information from the previous instance and continue evaluating workload where it left off at the last shutdown.

SGA_MAX_SIZE

sga_max_size sets the maximum value for sga_target

If sga_max_size is less than the sum of (db_cache_size + log_buffer + shared_pool_size + large_pool_size ) at initialization time, then the value of sga_max_size is ignored.

SGA_TARGET

This parameter is new with Oracle 10g. It specifies the total amount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components - such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache - as required.

This new feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size need not be specified explicitly anymore.

sga_target cannot be higher than sga_max_size.

SGA_TARGET provides the following:

• Single parameter for total SGA size

• Automatically sizes SGA components

• Memory is transferred to where most needed

• Uses workload information

• Uses internal advisory predictions

• STATISTICS_LEVEL must be set to TYPICAL

By using one parameter we don't need to use all other SGA parameters like.

• DB_CACHE_SIZE (DEFAULT buffer pool)

• SHARED_POOL_SIZE (Shared Pool)

• LARGE_POOL_SIZE (Large Pool)

• JAVA_POOL_SIZE (Java Pool)

Enable SGA_TARGET

SQL> show parameter sga_target

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sga_target big integer 0

SQL> show parameter sga_max_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sga_max_size big integer 600M

As we can see out automatic SGA tuning is not enabled so we can enable it by setting the SGA_TARGET parameter value.

SQL> alter system set sga_target=500m;

System altered.

SQL> show parameter sga_target

NAME TYPE VALUE

------------------------------------ ----------- -------

sga_target big integer 500M

Resize SGA_TARGET

• SGA_TARGET is dynamic

• Can be increased till SGA_MAX_SIZE

• Can be reduced till some component reaches minimum size

• Change in value of SGA_TARGET affects only automatically sized components

Disable SGA_TARGET

We can Disable our automatic SGA tuning by setting the parameter SGA_TARGET to value digit 0.

SQL> alter system set sga_target=0;

System altered.

11g New Feature :-

MEMORY_TARGET      : Dynamic

MEMORY_MAX_TARGET  : Not Dynamic

> Prior to 11g, Oracle manages PGA separately by the pga_aggregate_target parameter.

> Oracle introduces AMM feature from 11g which will manage SGA as well as PGA.

> When we set MEMORY_TARGET, Oracle will dynamically assign memory to SGA & PGA           

Note :-  MEMORY_TARGET= SGA_TARGET + PGA_AGGREGATE_TARGET.

SGA and PGA can manage together rather than managing them separately.

If SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET is set to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non-zero value,

Oracle will manage both SGA components and PGA together within the limit specified.

If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non-zero value:

SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.

SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values; these values will be considered minimum values.(But sum of SGA_TARGET and PGA_AGGREGATE_TARGET should be less than or equal to MEMORY_TARGET).

SGA_TARGET is set to non-zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).

PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).