How to Increase MEMORY_TARGET

Before you can increase memory in oracle database, you must first understand four oracle memory parameters which govern the instance.

If you set MEMORY_TARGET, then oracle will perform AMM (automatic memory management) and both SGA + PGA are managed within the allocated memory. No need to touch SGA_TARGET / SGA_MAX_SIZE parameters.

SGA_TARGET and SGA_MAX_SIZE

The SGA_TARGET defines the total size of SGA and SGA_MAX_SIZE define the total max RAM SGA_TARGET can take. Example, if server RAM is 10 GB, SGA_TARGET is 3 GB and SGA_MAX_SIZE is 5 GB. This means that during heavy workloads, Oracle can max assign 5 GB RAM to SGA.

SGA_TARGET parameter is a dynamic parameter

show parameter sga_target;

alter system set sga_target = 10G;

SGA_MAX_SIZE is a static parameter and cannot be changed immediately

show parameter sga_max_size;

alter system set sga_max_size = 12G scope=spfile;

MEMORY_TARGET and MEMORY_MAX_TARGET

From 11g onward, you do not need to manage SGA and PGA separately. You can allocate MEMORY_TARGET parameter and oracle will handle both SGA + PGA. You do not even need to set SGA_TARGET or SGA_MAX_SIZE.

Let us assume we have 16 GB RAM on a server and we want to allocate 5 GB to Oracle. This can be simple done by setting MEMORY_TARGET to 5 GB. MEMORY_MAX_TARGET defines the maximum value MEMORY_TARGET can go.

MEMORY_TARGET is a dynamic parameter

show parameter memory_target;

alter system set memory_target = 5G;

MEMORY_MAX_TARGET is a static parameter

show parameter memory_max_target;

alter system set memory_max_target = 7G scope=spfile;

If you set MEMORY_TARGET

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

Query to find memory used by oracle

select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb
from
(
select 'sga' nm, sum(value) val
from v$sga
union all
select 'pga', sum(a.value)
from v$sesstat a, v$statname b
where b.name = 'session pga memory'
and a.statistic# = b.statistic#
)
group by rollup(nm);

Query to find SGA components size

A very simple query to display SGA components size in Oracle database

SELECT * FROM v$sgainfo;

credit: https://www.support.dbagenesis.com/post/increase-memory-target-in-oracle

SGA_TARGET and SGA_MAX_SIZE

MEMORY_TARGET and MEMORY_MAX_TARGET

If you set MEMORY_TARGET

Query to find memory used by oracle

Query to find SGA components size

Related posts: