• Arun Kumar

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, they 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;


Further Read:

13,942 views

Recent Posts

See All

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather sta

Imagine an application user gave accidental salary hike of 50% instead of 5% to all employees! To initiate a database point-in-time recovery, we must know the exact time the query was executed against