Memory Check

98gb is the physical ram


set linesize 200
col VALUE for a50
set pagesize 100
col Parameter for a50
col "Session Value" for a50
col "Instance Value" for a50
column component format a25
column Initial format 99,999,999,999
column Final format 99,999,999,999
column Started format A25

select name,value from v$system_parameter where name in ( 'memory_max_target', 'memory_target', 'sga_max_size', 'sga_target', 'shared_pool_size', 'db_cache_size', 'large_pool_size', 'java_pool_size', 'pga_aggregate_target', 'workarea_size_policy', 'streams_pool_size' ) ;

select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and a.ksppinm in
('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');


select * from v$sgastat where pool like '%shared%' order by bytes;

select NAMESPACE,GETHITRATIO,PINHITRATIO,RELOADS,INVALIDATIONS from v$librarycache;

SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$SGA_RESIZE_OPS;

SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$MEMORY_RESIZE_OPS;









col username for a20
col program for a40
select s.username, s.program, count(*) total_sessions, sum(PGA_ALLOC_MEM)/1024/1024 total_mem_mb
from v$session s, v$process p
where s.paddr=p.addr
--and type<>'BACKGROUND'
group by s.username, s.program
order by 4 desc
/
+++++++++++++++++++++++++++++++++++++++++++++++++++++

ORA-00845: MEMORY_TARGET not supported on this system


SQL> ALTER SYSTEM SET SGA_TARGET=8G scope=spfile;

System altered.

SQL> alter system set SGA_MAX_SIZE=8G scope=spfile;

System altered.

SQL> startup nomount
ORA-01078: failure in processing system parameters
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 7184M
SQL> exit

SQL> alter system set memory_target=8208M scope=spfile;

System altered.

SQL> alter system set memory_max_target=8208M scope=spfile;

System altered.

SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported on this system
SQL> exit
Disconnected


$ df -h |grep /dev/shm
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 7.4G  176M  7.2G   3% /dev/shm

FIX:-

$ mount -o remount,size=9G /dev/shm


$ df -h |grep /dev/shm
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 9.0G  8.2G  851M  91% /dev/shm


SQL> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 8G
sga_target                           big integer 8

• sga_target: (pre 11g):  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.
  • sga_max_size:  The 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.
• memory_target (starting in 11g):  If memory_target  is set, then AMM is enabled:  If memory_target is set to non zero value and :
  • sga_target, sga_max_size and pga_aggregate_target are set to 0, then 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, then 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 auto-tuned 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 auto-tuned. The sga_target will be initialized to a value of (memory_target-pga_aggregate_target).

memory_target	sga_target	sga_max_size	pga_aggregate_target	Behavior
non-zero	0	0	0	60% of memory_target to SGA, 40% to PGA
non-zero	non-zero		non-zero	Minimum values
non-zero	non-zero		un-set	pga_aggregate_target = memory_target - sga_target
non-zero	un-set		un-set	sga_target is set to memory_target - pga_aggregate_target

monitoring automatic shared memroy


select 
   component, 
   oper_type, 
   oper_mode, 
   initial_size/1024/1024 "Initial", 
   TARGET_SIZE/1024/1024  "Target", 
   FINAL_SIZE/1024/1024   "Final", 
   status 
from 
   v$sga_resize_ops;

• select sum(value) from v$sga;
• select sum(bytes) from v$sgastat;
• select sum(current_size) from v$sga_dynamic_components;
• select * from v$sga_dynamic_free_memory;

-----------------------------------------------------------------------------------

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT


Some time we face this error when we run batch jobs with very high degree of parallelism.  Oracle is  unable to use more PGA and it kills the session and the batch job fails. This can happen even though you assign very high  PGA_AGGREGATE_LIMIT.

Batch job sessions failed with below errors.

ORA-00028: your session has been killed
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB

To Avoid this error: Select correct degree of parallelism

Selecting degree of parallelism is very much important, number of parallel sessions depends on server CPU core.

Degree of parallelism can be calculated with below equation.

For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT.
For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

Running more that 16 parallel session in 8 core CPU server is not a good idea.

You may have a look at this oracle document for more details.

https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

Check the degree of parallelism is in recommended value. If yes check the  parameter PGA_AGGREGATE_LIMIT is set for small value. You may increase it according to degree of parallelism.

Still facing the issue and if your database is 12c, you may revert this parameter to pre12c by setting pga_aggregate_limit to 0.

alter system set pga_aggregate_limit=0 scope=both;

Setting the higher value for pga_aggregate_limit

alter system set pga_aggregate_limit=<Value> scope=both;

This parameter change not required instance restart