As part of Oracle tuning exercise, we have increased sga_target and pg_aggregate_target to a higher value. This is a 32-bit Windows 2008 Enterprise server.
Users complaint application returned this error after changing init.ora parameters. After checking and some troubleshooting, we found that this is due to 32-bit restriction of 2gb virtual memory for each process.
With sga_target = 1.3g, pga_aggregate_target=0.5g, we had used 1.8g out of 2gb. Oracle ddl and process overhead took up 150mb, all this totals up to 1950gb. This leaves little for the user connections. With each connection takes 2.14mb, this only allows about 20 user connections (50/2.14) before it hit the 2gb limit.
We reduced the sga and pga and things back to normal, but the performance didn't get improved. This is some solutions for this large sga requirements. We will explore further.
This error 12518 can be caused by many things like hitting process or session limits, and this case it was not. We need to increase user memory space and this is some solutions on 32-bit systems. We will explore later.
No comments:
Post a Comment