SGA_MAX_SIZE & SGA_TARGET / MEMORY_TARGET & MEMORY_MAX_TARGET

In this article i will discuss the Difference between these parameter and how to use them , MEMORY_TARGET & MEMORY_MAX_TARGET parameters appear in 11g .

I will depend on oracle documentation to describe these parameter :

SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.

The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA
Memory area parameters. If the SGA_TARGET is set to some value then the
Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET
value can be adjusted up to the SGA_MAX_SIZE parameter, not more than
SGA_MAX_SIZE parameter value.

MEMORY_TARGET & MEMORY_MAX_TARGET

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

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET 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 by you.

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.

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).

MEMORY_TARGET controls the total amount of memory to Oracle both for PGA and SGA You don't need to set SGA_TARGET if you already have MEMORY_TARGET set. Read Oracle documentation https://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm

Post a Comment

Popular posts from this blog

Caused by: java.net.ConnectException: t3://exalytic.domain:8001: Destination unreachable; nested exception is:
The Above Error Appear when i was trying to access BPM, the main Cause for this error there's no configuration protocol on SOA Server Which mean No Listening Host.

To Avoid and Solve this error Follow the below Steps :

Access to weblogic Console using Administrator Password.Click on Servers -> Protocols -> Channels like the below screen Now you have to create new Protocol Click on "Configure a new Network Channel,"with the below confguration
Name: LoopbackProtocol: t3Listen Address: localhostListen Port: 8001 (same as Server Host/port)Restart The Server and test the connection Again
Thank you
Osama Mustafa

This Article for newbe Oracle DBA Since If you trying to change sga on rac with wrong way , spfile maybe be corrupted .

Node One : ORCL1
Node Two : ORCL2

Note : You Need To Check Memory Parameter On Database , if its Have Available Size . Connect To Node One :alter system set sga_max_size=16g scope=spfile sid = 'ORCL1';
alter system set sga_target=12g scope=spfile sid = 'ORCL1';
alter system set sga_max_size=16g scope=spfile sid = 'ORCL2';
alter system set sga_target=12g scope=spfile sid = 'ORCL2';
Or In Another Way :
sql>alter system set sga_target=12G scope=spfile sid='*';
sql>alter system set sga_max_size=16G scope=spfile sid='*';
sql>alter system set sga_max_size=16G scope=spfile ;
sql>alter system set sga_target=12G scope=spfile;

PGA :

sql>alter system set pga_aggregate_target=4G scope =spfile sid='*';
sql>alter system set pga_aggregate_target=4G scope=both;