Question
Vodafone
IN
Last activity: 27 Jan 2020 12:14 EST
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?
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- Proprietary information hidden*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****
Accepted Solution
Updated: 4 Feb 2020 6:56 EST
Pegasystems Inc.
IN
IH query is has a UNION join to help in identity matching the customer interactions, the second query tries to find the associated customer identity in the association table and sends all the matching interactions, if you do not have any associations the second query should be very fast.
Pegasystems Inc.
FR
Hello,
Maybe you should purge the transaction data in the database. Please have a look at the below Support Article that describe the process:
https://community.pega.com/sites/default/files/help_v72/dsm/concepts/ih-scripts-con.htm
Vodafone
IN
Hi Marc,
we are maintaining pruge process for ih_fact and other tables. our question is as mentioned below to check fine tuning scope
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?
Can you please clarify here?
Pegasystems Inc.
NL
This query is triggered the by the usage of the Interaction History shape in the Strategy.
I can't see from the body of the alert that the query is executing twice. Can you please explain why do you think it is executed twice ?
Accepted Solution
Updated: 4 Feb 2020 6:56 EST
Pegasystems Inc.
IN
IH query is has a UNION join to help in identity matching the customer interactions, the second query tries to find the associated customer identity in the association table and sends all the matching interactions, if you do not have any associations the second query should be very fast.
Vodafone
IN
This seems OOTB query when interactionhistory shape in strategy is executed. This is taking 26 sec in peak time.
is there any scope for fine tuning this query?
Vodafone
IN
Could you please suggest how i do acheive this? when we add interaction history shape in strategy , this query trigger based on batch size set in marketing application settings.
Is there any way that we can restrict joining condition? as we do not have any records in association table in prod