189 8069 5689
本篇内容主要讲解“怎么解决ORACLE 12C :'acknowledge over PGA limit' Wait Event问题”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么解决ORACLE 12C :'acknowledge over PGA limit' Wait Event问题”吧!
成都创新互联主营许昌网站建设的网络公司,主营网站建设方案,成都App定制开发,许昌h5微信小程序定制开发搭建,许昌网站营销推广欢迎许昌等地区企业咨询
用户反映19c生产环境数据库慢，运行简单语句uat只需要2s,但是prod需要40s,对此对数据库进行检查
首先检查数据库的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 |
查询mos文档，发现此类问题是由于PGA大小达到了PGA_AGGREGATE_LIMIT的值，防止ORA-4036错误，后面进程分配pga时需要等待其他进程释放pga。
12c: 'acknowledge over PGA limit' Wait Event (Doc 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.
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;
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;
oracle建议设置PGA_AGGREGATE_LIMIT=0或者增大这个参数的值解决
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SCOPE=BOTH;
但是，这个方法只是暂时解决，究竟是什么导致的呢，需要继续检查pga使用情况
首先判断该应用pga设置是否合理，即连接数数量和pga大小，可以通过查询process数量和pga使用，如果
明显出现process较小而pga使用很大，则有可能是某些应用进程或者数据库进程异常导致，可以通过下面语句查询出是
哪个进程异常导致
1.列出占用pga最大的进程
select pid,spid,substr(username,1,20) "USER" ,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where pga_alloc_mem=(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');
2.查看数据库会话所有sid占用pga的的详细情况
set linesize 120
set pagesize 120
column spid heading 'OSpid' format a8
column pid heading 'Orapid' format 999999
column sid heading 'Sess id' format 99999
column serial# heading 'Serial#' format 999999
column status heading 'Status' format a8
column pga_alloc_mem heading 'PGA alloc' format 99,999,999,999
column pga_used_mem heading 'PGA used' format 99,999,999,999
column username heading 'oracleuser' format a12
column osuser heading 'OS user' format a12
column program heading 'Program' format a20
SELECT
p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
p.PGA_USED_MEM,
s.username,
s.osuser,
s.program
FROM
v$process p,
v$session s
WHERE s.paddr ( + ) = p.addr
and p.BACKGROUND is null /* Remove if need to monitor background processes */
Order by p.pga_alloc_mem desc;
3.检查pga随着时间的增长
select snap_id,round(value/1024/1024,0) from dba_hist_pgastat where name='total PGA allocated' order by snap_id
4.检查应用进程分配的pga,查询出大于50m的
select a.sid,a.status,a.sql_id,a.last_call_et,b.pga_alloc_mem/1024/1024,a.event,a.state from v$session a,v$process b where a.paddr=b.addr and a.status='INACTIVE' and b.pga_alloc_mem/1024/1024 > 50;
5.查看当前pga使用情况
select * from v$pgastat
通过1可以查询出是否有异常的数据库进程占用较大的pga,2可以查询出是否有异常会话占用较大pga,3可以查询出pga在最近一段时间的增长情况，可以判断出什么时候开始出现异常，4则是对应用进程使用Pga大于50m进行排查
如果是数据库进程异常，则需要检查该系统进程对应的trc文件进行分析。
如果是应用进程异常，则通过该sid和spid去分析,对不释放的进程做heapdump,看看内存中导致存储的是什么内容，然后和应用一起解决
到此，相信大家对“怎么解决ORACLE 12C :'acknowledge over PGA limit' Wait Event问题”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！