Question
Vodafone
IN
Last activity: 11 Jun 2020 5:58 EDT
SPPR_SYS_RESERVEQUEUEITEM_B in 7.3.1 is not closing DB connection
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.
Proximus
BE
Hello,
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.
Regards
Anthony
Pegasystems Inc.
IN
Hi,
How did you find out that this stored procedure did not close the connections. Is there any log message.
Thanks,
Abhinav
Vodafone
IN
Hi Abhinav,
Please find below screenshot which depicts store proc session is becoming inactive. This is holding PGA memory and not releasing memory for other processes.