Acknowledge Over PGA limit is a new wait event introduced with PGA_AGGREGATE_LIMIT in 12.1 version of Oracle Database.
If process need more PGA then process has to wait if instance is using PGA near to its limit defined in PGA_AGGREGATE_LIMIT parameter.
Note: This wait event can also be encountered when PGA allocation is less than PGA_AGGREGATE_LIMIT.
Error:
It will also produced error in alert log if the PGA limit exceeds. When a session is killed messages client also received error message:ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Performance issue
After upgrade from 11g to 12c, we are hitting the performance issue on our database on check alert log we are getting this in TOP EVENTS
PGA_AGGREGATE_LIMIT is introduced in 12c version, On fetching AWR report,
we are getting “ACKNOWLEGE OVER PGA LIMIT” in top event of AWR report in one of the customer for update query.
Default value for parameter PGA_AGGREGATE_LIMIT as
Note: default value should be greater than 2GB.
(1) Increasing PGA_AGGREGATE_TARGET.(200% of PGA_AGGREGATE_TARGET)
(2) Increasing PROCESSES parameter.(3 MB times the PROCESSES parameter)
(3) Setting underscore parameter “_pga_limit_target_perc” to a value greater than the default value.
Explain the default value behaviour for PGA_AGGREGATE_LIMIT Parameter
1. If we increase the value of PGA_AGGREGATE_TARGET then PGA_AGGREGATE_LIMIT is double or 200%.
Example: Increasing PGA_AGGREGATE_TARGET from 2G to 4G will give a default PGA_AGGREGATE_LIMIT of 8G.
2. If we increase processes then PGA_AGGREGATE_LIMIT is 3 times.
Example Increase PROCESSES parameter to 1000 will give a default PGA_AGGREGATE_LIMIT of 3000M.
3. Setting underscore parameter “_pga_limit_target_perc” to a value greater than the default value(200).
Example: Change parameter “_pga_limit_target_perc” to 400 (default 200) will give a default limit of 8G.
Performance issue Solution:
1. You can disable the 12c feature of PGA_AGGREGATE_LIMIT by setting this parameter to zero value.
Retrieve back to 11g behaviour by setting this to zero value.
Note: It is dynamic parameter no need to reboot the database.
-- For Stand alone
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SCOPE=BOTH;
— For RAC
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID=’*’ SCOPE=BOTH;
2. Alternate solution, Set PGA_AGGREGATE_LIMIT to higher than the default value, which was calculated based on the PGA_AGGREGATE_TARGET, PROCESSES or “_pga_limit_target_perc” setting and that will also help to reduce this wait event.
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT= new_value SCOPE=BOTH;
ORA-04036 Error Solution
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
--Check PGA parameter value
Show parameter pga
-- Increase the value
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT= new_value SCOPE=BOTH;
For more reference you can go through the Oracle Documents id:
Doc ID 2437003.1
Doc ID 2138882.1