Question

10
Replies
2569
Views
Elton Janssen (JANSE)
PEGA
Consulting Manager
Pegasystems
NL
JANSE Member since 2011 5 posts
PEGA
Posted: April 7, 2016
Last activity: August 9, 2016
Posted: 7 Apr 2016 7:44 EDT
Last activity: 9 Aug 2016 7:49 EDT
Closed

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

Show More

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

Show Less
System Administration Data Integration
Moderation Team has archived post, This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.