Reports like pzWarningsIntroduced trigger lots of Pega0005 alerts because the query logic is so complicated. The complexity is caused by using the rule resolution and/or application filter check boxes in the report definition rules. Pega ships many reports like this that trigger alerts and at times prevents functionality from working. A common work around is to use a private checkout on PRPC reports and disable the filters. I've also seen several PDN articles telling users to update the Guardrails section used by each rule to stop using the pzWarningsIntroduced -thus masking warnings.
My team has not been able to tune the rules tables to improve the performance of these queries. At times we have to resort to unnatural things to work around these problems and unblock development.
Thanks for the update. I'd like to point out one thing that may help. If you look closely at the generated SQL you will notice that the query is pulling in non-rule resolved instances into the temp result set used by the Union logic. Each time I've tried to tune this sort of query I find that it pulls in 10,000+ non-rules resolved instances that can never be a possible answer to the query. (We are using the rules resolution filter checkbox thus non-RR instances need to be excluded. ) This is not a silver bullet but when I exclude the non-rr instances from the query the plan is better. Please include this in your analysis.
I am trying to determine the scope for the targeted EPIC that will handle a single rule resolution. Can you please send me the query that you referring to.
With the new epic, I would like to have a targeted and incremental improvements rather than a big bang approach and handle all use cases. We are in touch with the Pega Express folks and will keep you in the loop.
I can send you a couple queries but the problem is wide spread. Most if not all queries (Reports Defs) that use the rules resolution and/or application filtering options trigger Pega0005 performance alerts. Some are worse than others. I'll get the alerts from some recent Alert logs and send them to you. In the meantime just look at the pzWarningsIntroduced report. It is used by the guardrails warning gadget that you see when you log into the development portal of any PRPC system. Also this is a well known issues that my former team (when I worked for Pega) found starting in 7.1.9 while beta testing the release with PMF. Unfortunately, root cause was never found and the release shipped. I'm 99% sure that the PMF production system deployed in Cambridge still triggers alerts for these types of reports. You may find it easier to work directly with that team to gain access to a real world production environment where the issue exists.
Hi Buelent, Rajiv, How are you? Have you made any progress toward fixing the RR filtered and App Filtered reports?
As I mentioned yesterday via email the problem is impacting DCO doc generation. We've started to use DCO more (and like it) so this is now a blocker issue.
For DCO doc generation the following queries are timing out and also cause exceptions stating that the DB connection is not being returned to the pool. In my experience too many of these exceptions will eventually caues the system to fail/hang.
To work around the problem I've had to disable/uncheck “Filter by rule resolution” and “Filter by Application context” . Although this worked as a temporary solution this will not work once I start creating more versions of my rules. At the moment I've just started development in 01-01-01, but once I create new versions this work around will lead to duplicate information in the doc because filtering is disabled. Support Request SR-A90432 has been created to get help from support too.
Attached is a trimmed down version of the Pega Alert logs that highlight the performance issue.