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 December 26, 2016 - Updated January 23, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.