Karen Ning, March 02, 2018 - 11:40 am UTC
We didn’t manually change it to a lower value. Instead I increased it to be 4G. From the v$pgastat, I saw max allocation pga is 1.2g. Not sure how it hit this limit.
We cannot set the limit to be 0 as this environment is shared by a few databases.
I wondered if there is anything change in 12.2 comparing to 12.1.
March 02, 2018 - 11:59 am UTC
I'm not aware of any.
But there is a MOS note (2337875.1) where customers say their application ran slower upgrading from 12.1 -> 12.2 due to "Acknowledge over PGA limit". But this looks app specific to me.
If you're having problems with this, contact support.
Target value is set to 0
jayendra, November 26, 2018 - 11:30 am UTC
We encountered the same issue after upgrade to the 12.2 version, the wait event "acknowledge over PGA limit" is encountered on most of our databases after upgrade. The "pga_aggregate_limit" was set to 10 g & "pga_aggregate_target" to 0. After setting the parameter pga_aggregate_target to 5 g the performance improved over time. Still we are trying to find the optimum value, could you suggest on this.
November 26, 2018 - 4:51 pm UTC
You can query v$pgastat to get memory usage details.