怎么解决ORACLE 12C :'acknowledge over PGA limit' Wait Event问题

本篇内容主要讲解“怎么解决ORACLE 12C :’acknowledge over PGA limit’ Wait Event问题”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么解决ORACLE 12C :’acknowledge over PGA limit’ Wait Event问题”吧!用户反映19c生产环境数据库慢，运行简单语句uat只需要2s,但是prod需要40s,对此对数据库进行检查首先检查数据库的awr报告，发现异常等待acknowledge over PGA limit占用较大Top 10 Foreground Events by Total Wait Time查询mos文档，发现此类问题是由于PGA大小达到了PGA_AGGREGATE_LIMIT的值，防止ORA-4036错误，后面进程分配pga时需要等待其他进程释放pga。12c: ‘acknowledge over PGA limit’ Wait Event (Doc ID 2138882.1)CAUSEThe “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.SOLUTION1. 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_AGGREGA免费主机域名TE_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_MEMfrom 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_id4.检查应用进程分配的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问题”有了更深的了解，不妨来实际操作一番吧！这里是云技术网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: RMAN中的备份保留策略有哪些

这篇文章主要介绍了RMAN中的备份保留策略有哪些，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。RMAN中提供了两种备份保留策略：基于时间和基于冗余数量的备份保留策略1. 基于时间的备份保留策略这…