Question
Vodafone
IN
Last activity: 18 Aug 2019 14:32 EDT
PushLogusagedata causing performance issue
We have observed pushlogusagedata causing lot of pega0005 alerts in alert log during campaign and inbound requests span which is causing performance issue in DB.
Our application is using pega version 7.3.1 and AES 7.30.
Pushlogusagedata is running every 600sec .
1.what would happen if run this agent once per day during non-business office hour?
2.Do i have to customize the PusgLogUsagetoAES in this case?
3. will it improve the query performance if i create one more index on column 'pxRequestorType'?
Query : SELECT TO_CHAR(TRUNC("PC0"."PXSNAPSHOTTIME",'DD'),'YYYYMMDD') AS "pyDateValue(1)" , "PC0"."PXREQUESTORTYPE" AS "pxRequestorType" , COUNT(DISTINCT "PC0"."PXREQUESTORID") AS "pySummaryCount(1)" , COUNT(DISTINCT "PC0"."PYUSERIDENTIFIER") AS "pySummaryCount(2)" FROM PEGADATA.pr_perf_stats "PC0" WHERE ( "PC0"."PXSYSTEMNODEID" = ? AND "PC0"."PXSNAPSHOTTIME" >= ? AND "PC0"."PXSNAPSHOTTIME" < ? ) AND "PC0"."PXOBJCLASS" = ? GROUP BY TO_CHAR(TRUNC("PC0"."PXSNAPSHOTTIME",'DD'),'YYYYMMDD') , "PC0"."PXREQUESTORTYPE" ORDER BY 1 DESC, 2 ASC
Please suggest
***Moderator Edit-Vidyaranjan: Updated Platform Capability***
Cognizant Technology Limited
IN
Hi,
It's a good idea, You can try creating an index for the fields used in the where clause. It requires server restart.
If still you are facing the same issue you can think about disabling Push notification service.
Ernst & Young LLP
MT
Is it possible for you to complete the query with the parameter values that you would find in the same Alert entry, and generate the explain plan of this query from database (seek help from your DBA if you don't know how to generate it)?
I have a quick look at the DB index of this table and found it already got a composite index on PXSNAPSHOTTIME and PXSYSTEMNODEID, so no reason to believe this query is not optimized.
My best guess is the longer response time is mainly caused by two factor - 1) use of function on PXSNAPSHOTTIME (and its subsequent use in GROUP BY clause), 2) Your application is inserting good amount of records on this table (expected for high volume system).
In that case a function based index would help, but first let us have a look the current explain plan.