Recently we had a long running SQL and we decided to implement Parallel sql to speed up the SQL run time. Oracle 11Gr2 on Solaris 10 on ASM storgae
SGA=35GB
PGA=5GB
16 Core CPU Oracle SPARC T4-2
My PGA usage hardly goes beyond 3 GB at any given point in time. After enabling the Parallelism the CPU usage went high which was expected from usual 10% to 25 %. The Memory also went up but the usage was so high my OS memory RSS went up by almost 15GB incuding the disk swap and the server begin to slowdown. The PGA went up beyond 7GB which shocked me but i got it from v$process and hence it was more of Process memory from the OS level. Since i allocated only 5 GB it started to swap the memory into the disks which caused the system to slow down for a while..
We had to increase the PGA to stop swapping into the disks.. Something interesting i learned
SGA=35GB
PGA=5GB
16 Core CPU Oracle SPARC T4-2
My PGA usage hardly goes beyond 3 GB at any given point in time. After enabling the Parallelism the CPU usage went high which was expected from usual 10% to 25 %. The Memory also went up but the usage was so high my OS memory RSS went up by almost 15GB incuding the disk swap and the server begin to slowdown. The PGA went up beyond 7GB which shocked me but i got it from v$process and hence it was more of Process memory from the OS level. Since i allocated only 5 GB it started to swap the memory into the disks which caused the system to slow down for a while..
We had to increase the PGA to stop swapping into the disks.. Something interesting i learned
