oracle 19c数据库W00n历程使用很多PGA内存资源的分析

今天，客户反馈测试环境的数据库PGA资源不敷，报错ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT；分析是多个W00n历程使用大量PGA-触发了BUG，对应解决办法就是打补丁。（民间办法就是KILL历程、重启数据库），如下为分析过程：
报错信息：

1. java.sql.SQLException: ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT at
2. oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at
3.   oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at
4.    oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at
5.     oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at
6.      oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at
7.       oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)   oracle@localhost:/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace$
8.        oerr ora 4036 04036, 00000, "PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT" //
9.        *Cause: Private memory across the instance exceeded the limit specified // in the PGA_AGGREGATE_LIMIT initialization parameter. The largest //
10.        sessions using Program Global Area (PGA) memory were interrupted //
11.        to get under the limit. // *Action: Increase the PGA_AGGREGATE_LIMIT initialization parameter or reduce
12.        // memory usage.     pga_aggregate_limit limit of aggregate PGA memory consumed by the instance  

复制代码

问题分析：
查看总的PGA分布：select sum(pga_alloc_mem)/(1024*1024) "Mbytes allocated", sum(pga_used_mem)/(1024*1024) "Mbytes used" from v$process;

查找占用内存多的历程：使用SQL；
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
SID, v$session.SERIAL#, v$process.SPID , ROUND(v$process.pga_used_mem/(1024*1024), 2) PGA_MB_USED,
v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr ORDER BY 4 DESC ;

数据库版本为默认的19.3，未安装RU补丁包。分析此历程，是触发了BUG：Bug 30098251 - WNNN PROCCESSES CREATE AN EXCESSIVE NUMBER OF OPEN CURSORS investigated the issue where Wnnn process has high number of open cursors and consuming more memory.
关于此BUG的形貌是：
Each Wnnn Background Process is consuming around 140MB of pga ( can be more as well) and holding hundreds of opened
cursors causing memory usage of instance to go high.
The sessions appear to remain open (session state ACTIVE), holding these cursors.
The Wnnn and SMCo processes appear to remain active for weeks at a time, as we can seen by logon time.
解决办法是：
1、民间办法：KILL历程、大概定期重启数据库。
2、官方办法：SOLUTION：
1. Apply Patch 30098251 available for your release and platform
OR
2. Upgrade to below releases where the fix for 30098251 is first included.
20.1.0
19.6.0.0.200114 (Jan 2020) Database Release Update (DB RU)
18.9.0.0.200114 (Jan 2020) Database Release Update (DB RU)
12.2.0.1.200114 (Jan 2020) Database Release Update (DB RU)
12.1.0.2.200114 (Jan 2020) Database Proactive Bundle Patch
12.2.0.1.200114 (Jan 2020) Bundle Patch for Windows Platforms
There is no workaround for this issue.

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。