Oracle PGA & Swaping on host

10g:

*If see swaping  on oracle db host. Check how much PGA is being utilized.

How:
Select sum(PGA_ALLOC_MEM) from V$process;

Above shows current allocated PGA.

*SGA size
>Show SGA



If total of PGA+SGA is greater than memory, you have nailed the problem 'DB process is the issue'.


Recently we saw swapping happening on one of our DB host. Suspecting PGA is the culprit we found PGA utilization was actually about the same as defined by 'pga_aggregate_target'. Issue discovered was huge pages was defined much higher than required by SGA, causing PGA to swap. Fix was simple, reconfigure huge pages.

Commands used :
cat /proc/meminfo

Modify:
cat sysctl.conf parameter hugepages
eg:
vm.nr_hugepages=20

Either reboot or use /sbin/sysctl -p to reload and kick off the hugepages configuration.