Question

10
Replies
2383
Views
X
JANSE Member since 2011 5 posts
PEGA
Posted: 4 years ago
Last activity: 3 years 10 months ago
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

                                (

                                        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

System Administration Data Integration
Moderation Team has archived post
Share this page LinkedIn