Open cursors leak in Pega 7.2
SummaryAfter applying the Hfix-29955 for the "too many open cursors", the SQL generating the cursors leak has disappeared, the Hotfix helped with this.
However, now user is facing a different SQL getting an increasing number of open connections:
Bellow are the SQL queries causing the open_cursors leak:
Insert into pega71_rules.pr_assembledclasses (pzjar, pzpackage, pzclass, pzlastmodified, pzsha1, pzbinarycontent, pzCacheConfigId, pzGenerationDate) values (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 )
Update pega71_rules.pr_assembledclasses set pzlastmodified = :1 , pzsha1 = :2 , pzbinarycontent = :3 where pzjar = :4 and pzpackage = :5 and pzclass = :6 and pzCacheConfigId = :7 and pzGenerationDate = :8
Within a day it left almost 250 open cursors for the first and 120 for the second.
Error MessagesNot Applicable
Steps to ReproduceNot Applicable
The root cause of this issue is open_cursor value was set to some less value.
The root causes for this type of issues are clearly explained in bellow PDN article:https://pdn.pega.com/support-articles/cursor-leak-maximum-number-open-cursors-exceeded
ResolutionPerform the following local-change steps:
1) Set the the open_cursor value to some higher value.
2) If using Tomcat server :Do the following changes.Add below parameter to data source tag(In context.xml).jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"
/>org.apache.tomcat.jdbc.pool.interceptor.ConnectionState - keeps track of auto commit, read only, catalog and transaction isolation level.org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer - keeps track of opened statements, and closes them when the connection is returned to the pool.
Published December 26, 2016 - Updated January 23, 2017