acknowledge over PGA limit等待事件

刘泽宇

在数据库的awr报告中发现acknowledge over PGA limit事件等待时间较长
Top 10 Foreground Events by Total Wait Time


Event                                        Waits        Total Wait Time (sec)    Avg Wait        %              DB time        Wait Class
acknowledge over PGA limit                5,255                       1050.3            199.87ms                    90.2        Scheduler
cursor: pin S wait on X                6                                72.6                     12.10 s                            6.2        Concurrency
DB CPU                                                                        4.6                                                                 .4        

参考官方文档（ID:2138882.1）
CAUSE
The "acknowlege over PGA limit" is a new wait event that was introduced with PGA_AGGREGATE_LIMIT in 12.1,
and it will force a process that wants more PGA to wait a bit if the  instance is getting close to hitting the limit.
The hope is some other process will release memory and avoid the ORA-4036 error.
PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.
By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter.
It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.
Each of the following will independently increase the default PGA_AGGREGATE_LIMIT value:

(1) Increasing PGA_AGGREGATE_TARGET.
(2) Increasing PROCESSES parameter.
(3) Setting underscore parameter "_pga_limit_target_perc" to a value greater than the default value.

SOLUTION
1. Set PGA_AGGREGATE_LIMIT=0 (as SYS user) to revert to 11g behavior of PGA memory management using PGA_AGGREGATE_TARGET parameter and the wait event will be alleviated.

           ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID='*' SCOPE=BOTH;

OR

2. Set PGA_AGGREGATE_LIMIT to higher than the default value (as SYS user), 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  SID='*' SCOPE=BOTH;

One can override the default calculations to increase the value of PGA_AGGREGATE_LIMIT. For example:
(1) Increasing PGA_AGGREGATE_TARGET from 2G to 4G will give a default PGA_AGGREGATE_LIMIT of 8G.
(2) Increasing processes parameter to 3000 will give a default PGA_AGGREGATE_LIMIT of 9000M.
(3) Setting underscore parameter "_pga_limit_target_perc"=400 (default 200) will give a default limit of 8G.


Note: Also hidden parameter _pga_limit_check_wait_time can shorten these waits but could increase the odds of hitting the ORA-4036 error.