Question

6
Replies
135
Views
Solved

PEGA0005- marketing OOTB campaign query is running slow

Our application uses pega 7.3.1 version and PM 7.31. when we launch campaign, below OOTB query is running slow. Please find below PEGA0005 alert stack

questions :

1. At which part of campaign run this query will be triggered(eg.segment refresh/volume constraint)

2. Is it possible to force hint(oracle parallel/index hint) in this query ?

3. why this query is reading IH_FACT table twice?

2020-01-13 10:42:08,855 GMT*8*PEGA0005*2116*500*ddf-10.160.8.17*NA*NA*BGHF45LN9LCXI48WECR73HKI42WJCK7TB*none*V*null*584399fc5c36f588d41c500d77ab6805*N*0*BGHF45LN9LCXI48WECR73HKI42WJCK7TB*6906*DataFlow-Service-PickingupRun-PR-1046:333, Access group: [AoM:MarketAdmins]*STANDARD*com.pega.pegarules.data.internal.store.DataStorePreparedStatement*NA*NA*Get subject id function**NA*****NA*NA*NA*NA*NA*;initial Executable;0 additional frames in stack;*BatchSize=500;BatchTimeout=0;pyEventStrategyForceExpire=false;KeyValueStoreFactory=com.pega.dsm.dnode.impl.dataflow.state.memory.InMemoryKeyValueStoreFactory@504aaa39;PartitioningFeature=com.pega.dsm.dnode.impl.dataset.database.features.DatabasePartitioningFeature@1c5a3896;RunId=PR-1046;pyCacheSize=0;*Database operation took more than the threshold of 500 ms: 2,116 ms SQL: SELECT pxFactID AS "pxFactID", pxOutcomeTime AS "pxOutcomeTime", pySubjectID AS "pySubjectID", pxInteractionID AS "pxInteractionID", "FACTTABLE".pzActionID AS "pzActionID", "FACTTABLE".pzChannelID AS "pzChannelID", "FACTTABLE".pzOutcomeID AS "pzOutcomeID", "pyAssociationStrength" AS "pyAssociationStrength", "pyAssociatedID" AS "pyAssociatedID" FROM (SELECT "FACTTABLE".pxFactID, "FACTTABLE".pxOutcomeTime, "FACTTABLE".pySubjectID, "FACTTABLE".pxInteractionID, "FACTTABLE".pzActionID, "FACTTABLE".pzChannelID, "FACTTABLE".pzOutcomeID, null AS "pyAssociationStrength", null AS "pyAssociatedID" FROM PEGADATA.PR_DATA_IH_FACT "FACTTABLE" WHERE ("FACTTABLE".customerid's IN ('500 customerid's ) UNION ALL SELECT "FACTTABLE".pxFactID, "FACTTABLE".pxOutcomeTime, "FACTTABLE".pySubjectID, "FACTTABLE".pxInteractionID, "FACTTABLE".pzActionID, "FACTTABLE".pzChannelID, "FACTTABLE".pzOutcomeID, "ASSOCIATIONTABLE".pyAssociationStrength AS "pyAssociationStrength", "ASSOCIATIONTABLE".pySubjectID AS "pyAssociatedID" FROM PEGADATA.PR_DATA_IH_FACT "FACTTABLE" JOIN PEGADATA.PR_DATA_IH_ASSOCIATION "ASSOCIATIONTABLE" ON (("ASSOCIATIONTABLE".pyAssociatedID = "FACTTABLE".pySubjectID) ) WHERE ("ASSOCIATIONTABLE".pySubjectID IN ('customerid's)

Please suggest your valuable thoughts here.

***Edited by Moderator Marissa to update platform capability tags****