Minimize Memory Footprint of running RAC instances to reduce OS Paging/Swapping

  • Tested version : RAC 12.1.0.2 / RAC 12.2.0.1
  • In test environments you very often my have memory resource problems and you need to reduce paging 
  • Paging/Swapping is the worse case scenario – so we need to reduce PGA/SGA size first

Using AMM

Reduce memory parameters on system 1
SQL> ALTER SYSTEM SET SGA_TARGET = 600m scope=spfile;
SQL> ALTER SYSTEM SET  pga_aggregate_target=350m scope=spfile;
SQL> startup force

After db reboot verify memory consumption  with top  
[root@ractw21 ~]# top
top - 12:06:43 up  1:50,  5 users,  load average: 2.19, 4.18, 3.50
Tasks: 471 total,   2 running, 469 sleeping,   0 stopped,   0 zombie
%Cpu(s):  3.5 us,  1.5 sy,  0.0 ni, 93.8 id,  1.0 wa,  0.1 hi,  0.3 si,  0.0 st
KiB Mem :  3781616 total,    82784 free,  2190716 used,  1508116 buff/cache
KiB Swap:  8257532 total,  6943376 free,  1314156 used.   677496 avail Mem
--> After reboot the system1 uses about 2.2 GByte memory
    paging/swappinig is low :  1.0 wa           

Now change these parameters on the complete cluster
SQL> ALTER SYSTEM SET SGA_TARGET = 600m scope=spfile sid='*';
SQL> ALTER SYSTEM SET  pga_aggregate_target=350m scope=spfile  sid='*';

Restart database
[oracle@ractw21 ~]$ svrctl stop database -db ractw
[oracle@ractw21 ~]$ srvctl stop database -db ractw

[root@ractw22 ~]# top
top - 12:17:17 up  2:01,  2 users,  load average: 17.49, 14.26, 9.98
Tasks: 499 total,   1 running, 497 sleeping,   0 stopped,   1 zombie
%Cpu(s):  2.8 us,  2.1 sy,  0.0 ni, 94.5 id,  0.3 wa,  0.0 hi,  0.3 si,  0.0 st
KiB Mem :  3781616 total,    83492 free,  1877612 used,  1820512 buff/cache
KiB Swap:  8257532 total,  5851312 free,  2406220 used.   902384 avail Mem
--> After reboot the system2 uses about 1.87 GByte memory
    paging/swappinig is low :  0.3 wa

Using memory_target

  •  Note:  avoid using memory_target  on prodcution systems
Monitor OS resources :
top - 10:06:57 up  1:54, 10 users,  load average: 2.89, 5.43, 5.05
Tasks: 490 total,   3 running, 487 sleeping,   0 stopped,   0 zombie
Cpu(s): 10.5%us,  7.9%sy,  0.0%ni, 17.7%id,  63.1%wa,  0.0%hi,  0.9%si,  0.0%st
Mem:   3785860k total,  2866768k used,   919092k free,    30704k buffers
Swap:  4063228k total,   965472k used,  3097756k free,  1315228k cached

--> if wa values are very high for many top samples you may reduce your SGA/PGA footprint 
    to reduce OS paging/swapping.  

Verify that at least 2 instances are up and running 
Note changing values on a single instance first will allow us to easily change back these values in 
case the instance doesn't startup after reboot !

[oracle@hract21 ~]$ srvctl  status database -d bankA
Instance bankA_1 is running on node hract21
Instance bankA_3 is running on node hract22

Test your new memory settings on your local instance  
SQL> ALTER SYSTEM SET MEMORY_TARGET = 400M  scope=spfile ;
SQL> ALTER SYSTEM SET SGA_TARGET = 0  scope=spfile;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0  scope=spfile;
SQL> startup force
ORACLE instance started.
Total System Global Area  419430400 bytes
Fixed Size            2925120 bytes
Variable Size          335547840 bytes
Database Buffers       75497472 bytes

After single instance reboot works change parameter globally and restart database 
SQL> ALTER SYSTEM SET MEMORY_TARGET = 400M  scope=spfile sid='*' ;
SQL> ALTER SYSTEM SET SGA_TARGET = 0  scope=spfile sid='*';
SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0  scope=spfile sid='*';

Restart database :
$ srvctl stop database -d bankA
$ srvctl start database -d bankA
$ srvctl  status database -d bankA
Instance bankA_1 is running on node hract22
Instance bankA_3 is running on node hract21

Reference

 

Leave a Reply

Your email address will not be published. Required fields are marked *