标题: acknowledge over PGA limit等待事件 [打印本页] 作者: 刘泽宇 时间: 2021-8-1 16:01 标题: 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.