Our application uses Pega 7.3.1 and oracle DB which is hosted in AWS . RAM size of DB is 32 GB and PGA is 5 GB.
Problem statement : store procedure SPPR_SYS_RESERVEQUEUEITEM_B connection is closed in DB which is in creasing PGA memory. This increased PGA memory(almost touching 5GB) causing problem in regular system programs(data loading, campaign).
As a work around , we had to kill the db sessions forcefully to get some space .
Question here is that why store sproc is not closing connections. I could see there is an issue with same SP in 7.3.1 regarding cursor open. I think this is still an issue in 7.3.1. I may be wrong here.
Please suggest on mitigation of this issue and let me know any information/questions.
SR B38330 · ISSUE 297411
Added code to ensure cursor closes on null-pointer exception
RESOLVED IN PEGA VERSION 7.3
Open cursor issues were occurring with out-of-the-box queries.SPPR_SYS_RESERVEQUEUEITEM_B Stored Proc when an exception was raised. Code has been added to ensure the cursor is closed in this situation.
After migration from 7.1.8 to 7.3.1 we also faced issue with this procedure.
I don't remember exactly how we stabilized all this, but I remember that:
- we had to clearly increase max cursor at session level from 200 to 1000
- I disabled AES agent PUSHDBIndexes (which was creating cursor issue)
- we worked on pr_sys_queue_sla which was having huge number of entries and SLA agent is calling/using above procedure.
Posted: 2 years ago
Posted: 3 Feb 2020 10:13 EST
Abhinav Gupta (Abhinav7)
Manager, Technical Support - Platform Service Engineering