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
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.