Question
Virtusa Corporation
US
Last activity: 24 May 2022 10:17 EDT
Noticing too many PEGA0005 alerts on pr_page_store table 8.4.2
Hi - I'm noticing too many PEGA0005's on pr_page_store table.
Query 1: SQL: select pzPVStream , pxCommitDateTime from XXXXXX.pr_page_store where pzInsKey = ?*
Query 2: SQL: delete from XXXXXXXX.pr_page_store where pyRequestorID = ?*
Not sure whether these are invoked from with job scheduler [ClusterAndDBCleaner OR NodeCleaner]. Kindly someone share your thoughts to address them.
Thanks in advance!
Accepted Solution
Updated: 24 May 2022 10:17 EDT
Pegasystems Inc.
GB
This query gets executed during requestor passivation and triggerred by cleaner agent.
the PR_PAGE_STORE table is used to store individual clipboard pages used by a requestor.
As you can surmise, the table has very high DBMS INSERT/UPDATE/DELETE and the INDEXes will become fragmented very quickly. Therefore, DBMS maintenance (ie Oracle ESTIMATE STATISTICS) has to be performed regularly on the tables to keep them performant. If not, then access times will begin to creep up.
NOTE that these DELETE operations will increase as the number of users on the system increases. Therefore, observing higher counts of these DELETE operations is expected behavior.
From PegaALERTS, you can check the DELETE query is fired by Job Scheduler as a part of clean up OR activation of requestor data back into memory will be done if passivated users come back into session. This also triggers the DELETE query to remove the data for the particular user.
pr_sys_context has all the passivated requestors. System is getting passivated requestors using select pxrequestorid from pegadata.pr_sys_context where pxCreateDateTime < Value of DSS ( here it is 24hurs)
This query gets executed during requestor passivation and triggerred by cleaner agent.
the PR_PAGE_STORE table is used to store individual clipboard pages used by a requestor.
As you can surmise, the table has very high DBMS INSERT/UPDATE/DELETE and the INDEXes will become fragmented very quickly. Therefore, DBMS maintenance (ie Oracle ESTIMATE STATISTICS) has to be performed regularly on the tables to keep them performant. If not, then access times will begin to creep up.
NOTE that these DELETE operations will increase as the number of users on the system increases. Therefore, observing higher counts of these DELETE operations is expected behavior.
From PegaALERTS, you can check the DELETE query is fired by Job Scheduler as a part of clean up OR activation of requestor data back into memory will be done if passivated users come back into session. This also triggers the DELETE query to remove the data for the particular user.
pr_sys_context has all the passivated requestors. System is getting passivated requestors using select pxrequestorid from pegadata.pr_sys_context where pxCreateDateTime < Value of DSS ( here it is 24hurs)
The DSS takes effect here, once this query returns all the passivated requestor Id's, this is used to further delete from pr_page_store. Something like delete from pegadata.pr_page_store where pzinskey in ( 'SYSTEM-SAVEDPAGES HIBYPZYOS1HNCFPLHS5F5OF5GOWCMIQBZA!PS2__TABTHREAD4!PGCLASSTEMP'
So, it possible to see 7 days old data if the corresponding requestor id exists in pr_sys_context table
If there is any active requestors exist during the job run, those requestors data will not be deleted from pr_page_store, once they get passivated then the data will be deleted.
To stop passivation of Batch / Service Requestors please refer :
https://docs-previous.pega.com/system-administration/84/setting-passivation-and-requestor-time-outs https://collaborate.pega.com/question/when-are-passivated-requestors-restored
Also check the number of records present and the date range from when the records are present in pr_page_store table.