- 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
Table of Contents
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