Support Article
Open cursors leak in Pega 7.2
SA-31879
Summary
After 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 )
or
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 Messages
Not Applicable
Steps to Reproduce
Not Applicable
Root Cause
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
Resolution
Perform 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;"
Example:
<Resource name="jdbc/PegaRULES"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@<Host>:<portnumber>:<DBNAME>"
username="XXX"
password="XXX"
maxActive="100"
maxIdle="30"
maxWait="10000"
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 January 24, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.