Support Article
Cursor leak: Maximum number of open cursors exceeded
SA-22528
Summary
In Production, the PegaRULES logs are presenting a number of open cursors exceeded.
This issue is mainly reproducable in Production due to the volumes.
It is obviously a cursor leak and some Autonomic Event Services (AES) reports are confirming the leak.
Error Messages
ORA-01000: Maximum number of open cursors exceeded.
Steps to Reproduce
Not applicable.
Root Cause
A cursor leak has multiple root causes. The first one is the setting of "open_cursor", the original value was "300" which is a bit low for a PRPC application so it has been changed to "500" but no significant reduction of cursor leak.
The second one could be an incompatible jar file.
The following article has been used "https://pdn.pega.com/support-articles/max-open-cursors-exceeded-error-after-upgrade" but still not working. The third one is an issue with a store procedure. The following article has been used "https://pdn.pega.com/support-articles/spprsysreservequeueitemb-stored-proc-leave-cursors-open", the behaviour was slightly better after this change but still not fully working as expected.
Run the following steps in order to identify which SQL queries were holding the cursor:
- Get the list of SQL with high open cursors with this SQL (“SELECT sid, sql_text, hash_value, user_name, COUNT (*) AS "OPEN CURSORS" FROM v$open_cursor WHERE user_name = 'PRPC718' GROUP BY sql_text, sid, user_name ORDER BY 4 DESC”)
- Then to get a fuller view of the SQL text, use a query like (“select vst.hash_value, vst.piece, vst.address, voc.user_name as user_name, vst.sql_text as sqltext from v$open_cursor voc inner join v$sqltext vst on voc.address=vst.address and voc.hash_value=vst.hash_value where voc.user_name='PRPC718' order by vst.hash_value,vst.address,vst.piece;”)
- Find the full SQL for the large number of open cursors using the hash_value to match from the first and second queries.
Resolution
Apply HFix-25792.
Published April 27, 2016 - 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.