Question

Timeouts occuring on DB for following queries

Hi,

We are getting a lot of time outs on the DB. DBA has identified the following queries that are being timed out by Pega:

Any ideas as to what action in the studio is causing this issue?

regards,

Elton

SELECT

        /*+leading(D R H A) */

        r.pxInsId AS "pzRuleName"

FROM    OWNER_ATR.pr4_rule_vw r             ,

        OWNER_ATR.pr_sys_appcache_dep d     ,

        OWNER_ATR.pr_sys_app_ruleset_index a,

        OWNER_ATR.pr_sys_app_hierarchy_flat h

WHERE   d.pzimplementationkey            = :1

        AND d.pzcacheconfigid            = :2

        AND d.pzexplicitexcludecombined IN (0, 2)

        AND d.pzappliestoclassname       = '+NA'

        AND d.pzdepruleinsid             = r.pxinsid

        AND d.pzruletype                 = r.pyclass

        AND r.pyruleset                  = a.pzrulesetname

        AND a.pzapphash                  = h.pzapphash

        AND h.pztopapphash               = :3

        AND

        (

                a.pzrulesetversionmajor = -1

                OR

                (

                        a.pzrulesetversionmajor = r.pzrulesetversionmajor

                        AND

                        (

                                a.pzrulesetversionminor    = -1

                                OR a.pzrulesetversionminor > r.pzrulesetversionminor

                                OR

                                (

                                        a.pzrulesetversionminor = r.pzrulesetversionminor

                                        AND

                                        (

                                                a.pzrulesetversionpatch    = -1

                                                OR a.pzrulesetversionpatch > r.pzrulesetversionpatch

                                                OR

                                                (

                                                        a.pzrulesetversionpatch = r.pzrulesetversionpatch

                                                )

                                        )

                                )

                        )

                )

        )

        AND r.pyclassname IS NULL

        AND h.pzappheight  < :4

        AND rownum         = 1

UNION ALL

SELECT

        /*+leading(D R C C2 H A) */

        r.pxInsId AS "pzRuleName"

FROM    OWNER_ATR.pr4_rule_vw r             ,

        OWNER_ATR.pr_sys_appcache_dep d     ,

        OWNER_ATR.pr_sys_class_ancestors c  ,

        OWNER_ATR.pr_sys_class_ancestors c2 ,

        OWNER_ATR.pr_sys_app_ruleset_index a,

        OWNER_ATR.pr_sys_app_hierarchy_flat h

WHERE   d.pzimplementationkey           = :5

        AND d.pzcacheconfigid           = :6

        AND d.pzexplicitexcludecombined = 0

        AND c.pxleafclassname           = :7

        AND d.pzappliestoclassname      = c.pxucclassname

        AND d.pzdepruleinsid            = r.pxinsid

        AND d.pzruletype                = r.pyclass

        AND r.pyruleset                 = a.pzrulesetname

        AND a.pzapphash                 = h.pzapphash

        AND h.pztopapphash              = :8

        AND

        (

                a.pzrulesetversionmajor = -1

                OR

                (

                        a.pzrulesetversionmajor = r.pzrulesetversionmajor

                        AND

                        (

                                a.pzrulesetversionminor    = -1

                                OR a.pzrulesetversionminor > r.pzrulesetversionminor

                                OR

                                (

                                        a.pzrulesetversionminor = r.pzrulesetversionminor

                                        AND

                                        (

                                                a.pzrulesetversionpatch    = -1

                                                OR a.pzrulesetversionpatch > r.pzrulesetversionpatch

                                                OR

                                                (

                                                        a.pzrulesetversionpatch = r.pzrulesetversionpatch

                                                )

                                        )

                                )

                        )

                )

        )

        AND c2.pxleafclassname = :9

        AND c2.pxclassname     = r.pyclassname

        AND

        (

                c.pxheight > c2.pxheight

                OR

                (

                        c.pxheight        = c2.pxheight

                        AND h.pzappheight < :10

                )

        )

        AND rownum = 1

UNION ALL

SELECT

        /*+leading(D R C C2 H A) */

        r.pxInsId AS "pzRuleName"

FROM    OWNER_ATR.pr4_rule_vw r             ,

        OWNER_ATR.pr_sys_appcache_dep d     ,

        OWNER_ATR.pr_sys_class_ancestors c  ,

        OWNER_ATR.pr_sys_class_ancestors c2 ,

        OWNER_ATR.pr_sys_app_ruleset_index a,

        OWNER_ATR.pr_sys_app_hierarchy_flat h

WHERE   d.pzimplementationkey           = :11

        AND d.pzcacheconfigid           = :12

        AND d.pzexplicitexcludecombined = 2

        AND c.pxleafclassname           = d.pzassemblytimeclass

        AND d.pzappliestoclassname      = c.pxucclassname

        AND d.pzdepruleinsid            = r.pxinsid

        AND d.pzruletype                = r.pyclass

        AND r.pyruleset                 = a.pzrulesetname

        AND a.pzapphash                 = h.pzapphash

        AND h.pztopapphash              = :13

        AND

        (

                a.pzrulesetversionmajor = -1

                OR

                (

                        a.pzrulesetversionmajor = r.pzrulesetversionmajor

                        AND

                        (

                                a.pzrulesetversionminor    = -1

                                OR a.pzrulesetversionminor > r.pzrulesetversionminor

                                OR

                                (

                                        a.pzrulesetversionminor = r.pzrulesetversionminor

                                        AND

                                        (

                                                a.pzrulesetversionpatch    = -1

                                                OR a.pzrulesetversionpatch > r.pzrulesetversionpatch

                                                OR

                                                (

                                                        a.pzrulesetversionpatch = r.pzrulesetversionpatch

                                                )

                                        )

                                )

                        )

                )

        )

        AND c2.pxleafclassname = d.pzassemblytimeclass

        AND c2.pxclassname     = r.pyclassname

        AND

        (

                c.pxheight > c2.pxheight

                OR

                (

                        c.pxheight        = c2.pxheight

                        AND h.pzappheight < :14

                )

        )

        AND rownum = 1

===========================================================================

WITH app_rulesets_pc0 AS

        (

                SELECT "PC0AR".pzRuleSetName          AS "pzRuleSetName"        ,

                        "PC0AR".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor",

                        "PC0AR".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor",

                        "PC0AR".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch"

                FROM

                        (

                                SELECT "PC0AR".pzRuleSetName         ,

                                        "PC0AR".pzRuleSetVersionMajor,

                                        "PC0AR".pzRuleSetVersionMinor,

                                        "PC0AR".pzRuleSetVersionPatch,

                                        ROW_NUMBER() OVER (PARTITION BY "PC0AR".pzRuleSetName ORDER BY "PC0AH".pzAppHeight) AS "pzRuleSetOrder"

                                FROM    OWNER_ATR.pr_sys_app_hierarchy_flat "PC0AH"

                                        INNER JOIN OWNER_ATR.pr_sys_app_ruleset_index "PC0AR"

                                        ON

                                                (

                                                        "PC0AH".pzAppHash = "PC0AR".pzAppHash

                                                )

                                WHERE "PC0AH".pzTopAppHash     = :1

                                        AND "PC0AH".pzAppName IN (:2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 )

                        )

                        "PC0AR"

                WHERE "PC0AR"."pzRuleSetOrder" = 1

                UNION ALL

                SELECT  CAST(:16 AS VARCHAR(128)) AS "pzRuleSetName"        ,

                        1                         AS "pzRulesetVersionMajor",

                        1                         AS "pzRulesetVersionMinor",

                        1                         AS "pzRuleSetVersionPatch"

                FROM    DUAL

        )

SELECT DISTINCT "ROC".PYCATEGORY AS "pyCategory"

FROM    OWNER_ATR.pr4_rule_vw "PC0"

        INNER JOIN app_rulesets_pc0 "PC0AR"

        ON

                (

                        (

                                (

                                        (

                                                "PC0".pzRuleSetVersionMajor   IS NULL

                                                OR "PC0".pzRuleSetVersionMajor = -1

                                        )

                                        AND

                                        (

                                                "PC0".pyRuleSet = "PC0AR"."pzRuleSetName"

                                        )

                                )

                                OR

                                (

                                        (

                                                "PC0".pzRuleSetVersionMajor     IS NOT NULL

                                                AND "PC0".pzRuleSetVersionMajor != -1

                                        )

                                        AND

                                        (

                                                "PC0".pyRuleSet = "PC0AR"."pzRuleSetName"

                                        )

                                        AND

                                        (

                                                "PC0".pzRuleSetVersionMajor = "PC0AR"."pzRuleSetVersionMajor"

                                        )

                                        AND

                                        (

                                                (

                                                        "PC0AR"."pzRuleSetVersionMinor"     != -1

                                                        AND "PC0AR"."pzRuleSetVersionPatch" != -1

                                                        AND

                                                        (

                                                                (

                                                                        "PC0".pzRuleSetVersionMinor < "PC0AR"."pzRuleSetVersionMinor"

                                                                )

                                                                OR

                                                                (

                                                                        "PC0".pzRuleSetVersionMinor      = "PC0AR"."pzRuleSetVersionMinor"

                                                                        AND "PC0".pzRuleSetVersionPatch <= "PC0AR"."pzRuleSetVersionPatch"

                                                                )

                                                        )

                                                )

                                                OR

                                                (

                                                        "PC0AR"."pzRuleSetVersionMinor"    != -1

                                                        AND "PC0AR"."pzRuleSetVersionPatch" = -1

                                                        AND "PC0".pzRuleSetVersionMinor    <= "PC0AR"."pzRuleSetVersionMinor"

                                                )

                                                OR

                                                (

                                                        "PC0AR"."pzRuleSetVersionMinor" = -1

                                                )

                                        )

                                )

                        )

                )

        INNER JOIN OWNER_ATR.pr4_base "ROC"

        ON

                (

                        (

                                "PC0".PYCLASS = "ROC".PYCLASSNAME

                        )

                        AND "PC0".PXOBJCLASS = :17

                        AND "ROC".PXOBJCLASS = :18

                )

WHERE

        (

                "PC0".PYCLASSNAME = :19

                AND "PC0".PYCLASSNAME NOT LIKE :20

                AND

                (

                        "ROC".PYCATEGORY     IS NOT NULL

                        AND "ROC".PYCATEGORY <> :21

                )

                AND "PC0".PXINSTANCELOCKEDKEY IS NULL

                AND

                (

                        "PC0".PYMETHODSTATUS    IS NULL

                        OR "PC0".PYMETHODSTATUS <> :22

                )

                AND

                (

                        "ROC".PYMETHODSTATUS IS NULL

                        OR

                        (

                                "PC0".PYRULESET NOT LIKE :23

                                AND "ROC".PYMETHODSTATUS = :24

                        )

                )

                AND

                (

                        "PC0".PYRULESET NOT LIKE :25

                        OR "PC0".PYMETHODSTATUS <> :26

                        OR "PC0".PYMETHODSTATUS IS NULL

                )

                AND "PC0".PYRULEAVAILABLE NOT IN (:27 , :28 )

        )

ORDER BY 1 ASC

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Comments

Keep up to date on this post and subscribe to comments

Pega
April 7, 2016 - 8:41am

Did the DBA update the statistics for the tables involved in these queries?

Pega
April 7, 2016 - 9:09am
Response to nistr

Hi Rajiv,

I'm not sure what you mean exactly? What kind of statistics are you referring to?

To my knowledge no changes were made to these tables ever since we migrated to Pega7.

Elton

Pega
April 7, 2016 - 9:38am

Pega
April 8, 2016 - 3:52am
Response to nistr

Hi Rajiv,

It seems our statistics are being automatically updated if the difference between the old statistics and new ones are more then 10%..

Any other ideas?

Elton

April 14, 2016 - 2:52pm
Response to nistr

IT makes sense that you would have more problems with your branch app because that is the app you are making the most changes to.  The issue is that the Histograms related to some of the operational tables cause problems because they adversely impact performance when the FUA is trying to get the latest rules to load the rule assembly cache.  With many people doing updates in that app and the app constantly recompiling, there is a ton of IO that is being slowed by an inefficient query plan caused by the Histograms.  On our last project, compile and rule access times progressively got slower and slower until we disabled the histograms.  Disabling the histograms improved performance by orders of magnitude.

Check out the following link for an explanation why:

Pega primary Key 32 char limitation and Oracle (11g) histograms

April 7, 2016 - 5:16pm

IF it is an Oracle database, I have also had trouble (in 6x) with tables with Histograms enabled for query optimization. The Histograms caused our queries to run very slowly over time until we had the DBAs turn them off. 

YOu could check with support to see if that is a possibility here. 

Pega
April 8, 2016 - 3:50am
Response to Chris_Boone

Hi Chris,

The histogram functionality indeed is enabled on basically all tables in the Rules schema. DBA however it not entirely convinced these should be turned off. I'll indeed check with support.

Thnx Elton

Pega
April 11, 2016 - 7:06am

Some more information

this seems to be related to branch applications only. Our main trunk applications do not seem to have this issue

Elton

April 11, 2016 - 11:21am
Response to JANSE

Hi Elton,

Have you tried increasing the timeout value for your JDBC connector? If you run the query listed above in SQL Developer you should get an idea of how long it takes to complete. Then you can adjust the timeout value accordingly. In tomcat for example, you can adjust the maxWait parameter to increase the timeout value.

August 9, 2016 - 7:49am

Assuming this is Oracle DB...

  1. Verify the length of the concerned class names and ensured they will not cause '32char cardinality problem'.
  2. Did we verify the row count in pr_sys_appcache_dep ? Is it too big ?
  3. Do we have any PEGA0005 alerts from rules_schema.pr_sys_appcache_* tables ?

Oracle's 32char Cardinality Problem

  • If a column is wider than 32 characters and its values are longer than 32 characters then only the first 32 characters in the column are considered for generating histograms. 
  • A histogram is a special type of column statistic that provides more detailed information about the data distribution in a column.
  • Histogram helps to determine cardinality of a query; especially for filtering / joining.
  • If the first 32 characters in all rows are 'similar / identical' then histograms may not be generated for the concerned column.
  • This will affect 'Cardinality' ( a function that returns the count of candiate rows ) resulting in wrong decisions made by query optimizer which in turn causes full table scans.

REFERENCE
https://docs.oracle.com/database/121/TGSQL
https://pdn.pega.com/support-articles/long-running-queries
https://pdn.pega.com/understanding-pega0005-alert-query-time-exceeds-limit