Slow SQL on table PEGARULES.PR_SYS_APPCACHE_DEP
Hello,
Is the below query(long running query) which occurs more frequently for us is related to anything with the cache settings or some configurable variable.
Hello,
Is the below query(long running query) which occurs more frequently for us is related to anything with the cache settings or some configurable variable.
DELETE FROM pegarules.pr_sys_appcache_shortcut WHERE pzinskey IN (SELECT e.pzinskey FROM pegarules.pr_sys_appcache_dep d, pegarules.pr_sys_appcache_shortcut e, pegarules.pr_sys_app_hierarchy_flat h, pegarules.pr_sys_app_hierarchy_flat h2, pegarules.pr_sys_app_ruleset_index i, pegarules.pr_sys_app_ruleset_index i2, pegarules.pr_sys_class_ancestors c, pegarules.pr_sys_class_ancestors c2 WHERE d.pzrulename = ? AND d.pzruletype = ? AND d.pzcacheconfigid = ? AND d.pzexplicitexcludecombined < 'literal' AND d.pzimplementationkey = e.pzimplementationkey AND e.pzapphash = h2.pztopapphash AND h2.pzapphash = i2.pzapphash AND i2.pzrulesetname = ? AND (i2.pzrulesetversionmajor = -'literal' OR (i2.pzrulesetversionmajor = ? AND (i2.pzrulesetversionminor = -'literal' OR i2.pzrulesetversionminor > ? OR (i2.pzrulesetversionminor = ? AND (i2.pzrulesetversionpatch = -'literal' OR i2.pzrulesetversionpatch >= ?))))) AND h.pztopapphash = h2.pztopapphash AND h.pzapphash = i.pzapphash AND i.pzrulesetname = d.pzrulesetname AND c.pxleafclassname = e.pzprimarypageclassname AND c.pxucclassname = d.pzappliestoclassname AND c2.pxleafclassname = e.pzprimarypageclassname AND c2.pxclassname = ? AND (c.pxheight > c2.pxheight OR (c.pxheight = c2.pxheight AND ((d.pzrulesetname = ? AND (d.pzrulesetversionmajor = -'literal' OR (d.pzrulesetversionmajor = ? AND (d.pzrulesetversionminor = -'literal' OR (d.pzrulesetversionminor < ? OR (d.pzrulesetversionminor = ? AND d.pzrulesetversionpatch = -'literal' OR d.pzrulesetversionpatch <= ?))))) OR (d.pzrulesetname != ? AND (h.pzappheight > h2.pzappheight OR (h.pzappheight = h2.pzappheight AND i.pzrulesetindex > i2.pzrulesetindex))))))) UNION ALL SELECT e.pzinskey FROM pegarules.pr_sys_appcache_dep d, pegarules.pr_sys_appcache_shortcut e, pegarules.pr_sys_class_ancestors cDep WHERE d.pzrulename = ? AND d.pzruletype = ? AND d.pzcacheconfigid = ? AND d.pzexplicitexcludecombined >= 'literal' AND d.pzimplementationkey = e.pzimplementationkey AND cDep.pxleafclassname = ? AND cDep.pxclassname = d.pzassemblytimeclass UNION ALL SELECT e.pzinskey FROM pegarules.pr_sys_appcache_dep d, pegarules.pr_sys_appcache_shortcut e, pegarules.pr_sys_class_ancestors cMod, pegarules.pr_sys_class_ancestors cDep WHERE d.pzrulename = ? AND d.pzruletype = ? AND d.pzcacheconfigid = ? AND d.pzexplicitexcludecombined >= 'literal' AND d.pzimplementationkey = e.pzimplementationkey AND cMod.pxleafclassname = d.pzassemblytimeclass AND cMod.pxclassname = ? AND cDep.pxleafclassname = d.pzassemblytimeclass AND cDep.pxucclassname = d.pzappliesToClassName AND cMod.pxheight <= cDep.pxheight)
Regards,
Kondal
***Moderator Edit-Vidyaranjan: Updated Platform Capability***