ITPub博客

首页 > 数据库 > Oracle > ORACLE 12C :'acknowledge over PGA limit' Wait Event 解决思路

ORACLE 12C :'acknowledge over PGA limit' Wait Event 解决思路

原创 Oracle 作者:shadow_young 时间:2020-07-06 10:17:20 0 删除 编辑

用户反映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,看看内存中导致存储的是什么内容,然后和应用一起解决



    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69978503/viewspace-2702623/,如需转载,请注明出处,否则将追究法律责任。

    上一篇: 没有了~
    请登录后发表评论 登录
    全部评论
    oracle热爱者

    注册时间:2020-07-06

    • 博文量
      9
    • 访问量
      13725