Question
LLoyds Banking Group
GB
Last activity: 21 Aug 2018 2:20 EDT
Expensive Query in Pega 7.3.1
Hi All-
We are running a Pega Upgrade project in our enterprise and as part of it, performance and Capacity team reported that the below query has been triggered continuously.
INTO PEGDEVR0.pr_sys_appcache_dep(pzInsKey, pxCommitDateTime,
"PXCREATEDATETIME", "PXCREATEOPERATOR", "PXCREATEOPNAME",
"PXCREATESYSTEMID", "PXINSNAME", "PXOBJCLASS",
"PXSAVEDATETIME", "PZAPPLIESTOCLASSNAME",
"PZASSEMBLYTIMECLASS", "PZCACHECONFIGID", "PZCLASS",
"PZCLASSHASH", "PZDEPENDENCYKEY", "PZDEPRULEINSID",
"PZDEPRULEINSKEY", "PZEXCLUDEFROMAA",
"PZEXPLICITEXCLUDECOMBINED", "PZEXPLICITINLINECLASS",
"PZIMPLEMENTATIONKEY", "PZMODELINDEX", "PZPACKAGE",
"PZRULENAME", "PZRULESETNAME", "PZRULESETVERSIONMAJOR",
"PZRULESETVERSIONMINOR", "PZRULESETVERSIONPATCH",
"PZRULETYPE")
VALUES (?, CURRENT_TIMESTAMP, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Do we know as part of which Pega activity , this query is getting triggered and can we stop this ?
PEG
GB
I don't think this is avoidable during the upgrade.
It will purge those entries at the start of the process, but the engine will rebuild those as the upgrade runs.
PEG
GB
I'm not sure.
Is this a DB2 instance by any chance? If so, when you run the generateddl --action upgrade to generate the upgraded schema I believe this will include an instruction to truncate pr_sys_appcache_dep (amongst others).
So if DB2 then I'm wondering whether a REORG after the truncation may help.