AMM RAC database and switch RAC database to ASMM

Calculate the current memory usage for our AMM RAC database and switch RAC database to ASMM

SQL> select sum(bytes/1024/1024) Current_SGA_SIZE_in_MB from v$sgastat;
CURRENT_SGA_SIZE_IN_MB
----------------------
        904.844437
SQL> select sum(bytes/1024/1024) MAX_SGA_SIZE_in_MB from  v$sgainfo    where name = 'Maximum SGA Size';
MAX_SGA_SIZE_IN_MB
------------------
    1592.84766
SQL> show parameter memory_max_target;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 1600M
SQL> select (value/1024/1024) Current_PGA_IN_USE_in_MB from v$pgastat where name = 'total PGA inuse';
CURRENT_PGA_IN_USE_IN_MB
------------------------
          122.085938
SQL> select (value/1024/1024) MAX_PGA_ALLOCATED_in_MB from v$pgastat where name = 'maximum PGA allocated';
MAX_PGA_ALLOCATED_IN_MB
-----------------------
         167.658203
SQL> select (value/1024/1024) PGA_TARGET_in_MB    from v$pgastat where name = 'aggregate PGA target parameter';
PGA_TARGET_IN_MB
----------------
         480
Our current AMM uses the following memory
 - memory reserved  for PGA/SGA: 1600 MByte 
 - current PGA size 120 MB
 - current SGA size 904 MB
 - free memory for future PGA/SGA usage: ~ 600 MByte

For switching ASMM this can be translated into 
  SGA_MAX_SIZE             : 1400 MByte
  SGA_TARGET               : 1000 MByte
  PGA_AGGREGATE_TARGET     :  480 Mbyte

For further tuning check : V$PGA_TARGET_ADVICE

Execute the  following commands.
Disable AMM
  SQL> alter system reset memory_max_target scope=spfile  sid='*';
  SQL> alter system reset memory_target  scope=spfile  sid='*';

Enable ASMM
  SQL> alter system set SGA_MAX_SIZE=1400m scope=spfile  sid='*';
  SQL> alter system set SGA_TARGET=1000m scope=spfile  sid='*'; 
  SQL> alter system set PGA_AGGREGATE_TARGET=480m scope=spfile  sid='*';  

Reboot database and verify that we have switched from AMM to ASMM
SQL> show parameter memory
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 0
memory_target                 big integer 0
--> AMM disabled 

SQL> show parameter sga
NAME                     TYPE          VALUE
------------------------ ----------- ------------------------------
sga_max_size             big integer 1408M
sga_target               big integer 912M

SQL> show parameter pga
NAME                     TYPE     VALUE
------------------------ ----------- ------------------------------
pga_aggregate_target     big integer 480M

--> ASMM enabled !

 Review impact on OS resources after switchging for AMM to ASMM

The switch from AMM to ASMM frees space in /dev/shm but allocates shared memomry for the SGA
$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status                             
0x00000000 3145746    oracle     640        16777216   41                      
0x00000000 3178515    oracle     640        1459617792 41                      
0xe1106fe8 3211284    oracle     640        2097152    41                      

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 2.0G  200M  1.9G  10% /dev/shm

Memory check

Huge pages on SRI()
========================================================

300 GB lock ( lock 300 gb memory for oracle out of 376 GB allocated on server )

--------/etc/security/limits.conf
* soft memlock 314572800
* hard memlock 314572800

huge pages and other shared memory settings

vm.nr_hugepages =45098
shmmax=322122547200
shmall=78643200


disable transparent huge pages
it is enabled as of now and needs to be disabled

SGA size for databases

total sga
mgmtdb= 1024 mb
ASM   = 1088 mb
PDWDB= 79872 mb (78 GB)
PRPDB= 8192 mb (8 GB)
total sga=90176 mb (88.0625 GB)

pga size for databases

pga_aggregate_target
PRPDB=8 GB
PDWDB=148 GB
total pga=156 GB

below parameter of both the databases PDWDB and PXPDB

large_pool_size=500M
use_large_pages=only 

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