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
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
Pegasystems Inc.
NL
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
Pegasystems
IN
Please refer to this - https://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#g49431
Pegasystems Inc.
NL
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
Pegasystems Inc.
US
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
Pegasystems Inc.
US
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.
Pegasystems Inc.
NL
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
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.
Cognizant
IN
Assuming this is Oracle DB...
- Verify the length of the concerned class names and ensured they will not cause '32char cardinality problem'.
- Did we verify the row count in pr_sys_appcache_dep ? Is it too big ?
- Do we have any PEGA0005 alerts from rules_schema.pr_sys_appcache_* tables ?
Oracle's 32char Cardinality Problem
Assuming this is Oracle DB...
- Verify the length of the concerned class names and ensured they will not cause '32char cardinality problem'.
- Did we verify the row count in pr_sys_appcache_dep ? Is it too big ?
- 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://pdn.pega.com/understanding-pega0005-alert-query-time-exceeds-limit