pzWarningsIntroduced and similar reports
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.
Is there any solution to this problem?
Thanks
John
Message was edited by: Lochan to add Category
Pega
US
Hi Rajiv,
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.
Thanks
John
Pegasystems
IN
Thank you John Bushey for your feedback. We will definitely consider this.
Pega
US
Hi Rajiv, I have a few more questions.
1. What's your plan to address this? Do you think that you will be able to provide a hfix for 7.1.9 or will we need to upgrade? What's your ETA?
2. How can we track your work? Can you provide a Story-, Goal-, or Epic- ID that I can use when I ping support?
3. Are you aware of any work-arounds that may provide some relief while we wait for changes? Do you have suggestions for tuning the DB or PRPC cache?
Thanks
John
Pegasystems
IN
Hi John,
>> 1. What's your plan to address this? Do you think that you will be able to provide a hfix for 7.1.9 or will we need to upgrade? What's your ETA?
Currently, there is no defined ETA. Product management is getting all the requirements and trying to address all use cases.
>> 2. How can we track your work? Can you provide a Story-, Goal-, or Epic- ID that I can use when I ping support?
As of now, there is no specific epic id or story to track. We did have one but it is currently undergoing change. I will forward your post to product management so that they are aware.
>> 3. Are you aware of any work-arounds that may provide some relief while we wait for changes? Do you have suggestions for tuning the DB or PRPC cache?
Updating the statistics regularly, for the tables involved, has helped in most cases.
-Rajiv
Pegasystems Inc.
US
John,
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.
Pega
US
Hi Buelent,
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.
Thanks
John
Pega
US
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.
pzSpecsForWorkType
pzFlowsForWorkClass
pzFlowsNotInWorkClass
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.
pzSpecsForWorkType
pzFlowsForWorkClass
pzFlowsNotInWorkClass
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.