- 10 Sep, 2023
- 0 Comments
- 1 Min Read
How to Increase MEMORY_TARGET
How to Increase MEMORY_TARGET
To increase memory in oracle database, we must first understand four oracle memory parameters which govern the instance.
They are as Follows :
SGA_TARGET.
SGA_MAX_SIZE.
- MEMORY_TARGET.
MEMORY_MAX_TARGET
- SGA_TARGET : The SGA_TARGET defines the total size of SGA.SGA_TARGET parameter is a dynamic parameter. Syntax to check SGA_Target & to reset its vaule is given below :
1 2 3 | show parameter sga_target;alter system set sga_target = 10G; |
2. SGA_MAX_SIZE :
SGA_MAX_SIZE define the total max RAM SGA_TARGET can take. SGA_MAX_SIZE is a static parameter and cannot be changed immediately 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.1 2 3 | show parameter sga_max_size;alter system set sga_max_size = 12G scope=spfile; |
3. MEMORY_TARGET : If You allocate MEMORY_TARGET parameter and oracle will handle both SGA + PGA.
MEMORY_TARGET is a dynamic parameter. 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA
1 2 | show parameter memory_target; alter system set memory_target = 5G; |
4. MEMORY_MAX_TARGET :
MEMORY_MAX_TARGET defines the maximum value MEMORY_TARGET can assign.
MEMORY_MAX_TARGET is a static parameter.
1 2 3 | show parameter memory_max_target;alter system set memory_max_target = 7G scope=spfile; |
How to find memory used by oracle :
1 2 | select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mbfrom(select 'sga' nm, sum(value) valfrom v$sgaunion allselect 'pga', sum(a.value)from v$sesstat a, v$statname bwhere b.name = 'session pga memory'and a.statistic# = b.statistic#)group by rollup(nm); |
Query to check SGA components size :
1 | SELECT * FROM v$sgainfo; |