• 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:

  • SGA_TARGET and SGA_MAX_SIZE

  • MEMORY_TARGET and MEMORY_MAX_TARGET


SGA_TARGET and SGA_MAX_SIZE


Important: 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.


Let us assume we have 10 GB physical RAM on a server. Which mean, at any given point we can max use 10 GB of RAM but not more than that. Also it does not mean that every-time we are using entire 10 GB of RAM. The same way SGA_TARGET defines the total size of SGA. SGA_MAX_SIZE define the total max value SGA_TARGET can take.


For example, server RAM is 10 GB, SGA_MAX_SIZE is 5 GB and SGA_TARGET is 3 GB. This means that I can max increase SGA_TARGET to 5 GB and not beyond that.


SGA_MAX_SIZE is not dynamic parameter but SGA_TARGET is.

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 our previous example, we have 10 GB RAM on 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.


Example, server RAM is 10 GB, MEMORY_MAX_TARGET is 7 GB and MEMORY_TARGET is 5 GB. This means I can max increase MEMORY_TARGET to 7 GB and not beyond that.


MEMORY_MAX_TARGET is not dynamic parameter but MEMORY_TARGET is

If MEMORY_TARGET is set to 5 GB, oracle will manage PGA + SGA withing 5 GB.


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


HOW TO INCREASE MEMORY_TARGET


SQL> show parameter target;
SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=3G SCOPE=SPFILE;
shut immediate;

Also make sure that /dev/shm is more than 3G otherwise you will get error at instance startup


df -h /dev/shm                  -> in my case, it was 2 GB. Increasing to 5 GB
mount -t tmpfs shmfs -o size=5G /dev/shm
df -h /dev/shm

Make /dev/shm value permanent in /etc/fstab file


vi /etc/fstab
change
tmpfs               /dev/shm                tmpfs   defaults        0 0
to
tmpfs               /dev/shm                tmpfs   size=5G        0 0

Start the database instance and increase MEMORY_TARGET


startup;
show parameter target;
ALTER SYSTEM SET MEMORY_TARGET=2560m;


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 in Oracle database


SELECT * FROM v$sgainfo;




3,321 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis