The last few days we have been seeing slowness when accessing rules in our dev instance and our alert logs indicate the below query executed frequently -
I couldn't find any reference to this query on pdn and would like to understand when PEGA would execute this query as I have never come across this one
SELECT /ASTERISK+ leading(D R H A) optimizer_features_enable('18.104.22.168') ASTERISK/ r.pxInsId AS "pzRuleName" FROM PEGA_RULES_731.pr_sys_appcache_dep d, PEGA_RULES_731.pr4_rule_vw r, PEGA_RULES_731.pr_sys_app_hierarchy_flat h, PEGA_RULES_731.pr_sys_app_ruleset_index a WHERE d.pzimplementationkey = ? AND d.pzcacheconfigid = ? 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 = ? 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 < ? AND rownum = 1 UNION ALL SELECT /ASTERISK+ leading(D R) optimizer_features_enable('22.214.171.124') ASTERISK/ r.pxInsId AS "pzRuleName" FROM PEGA_RULES_731.pr_sys_appcache_dep d, PEGA_RULES_731.pr4_rule_vw r WHERE d.pzimplementationkey = ? AND d.pzcacheconfigid = ? A
Sometimes it takes maybe 4 - 5 seconds but mostly it is not overly above the threshold. I was just curious on when this query runs (or is there a special condition on when this runs?) as I mentioned earlier, I have never come across this query in our alert logs before and certainly not in the frequency I am seeing now.
I could maybe run a DBTrace in a working environment to find out if this query runs always but thought would post the question in parallel if anyone has already come across this one?
Also, before going to the DBA, wanted certain things clarified from the product end like for e.g. why would the product code explicitly set out an optimizer condition for the DB?
"SELECT /ASTERISK+ leading(D R H A) optimizer_features_enable('126.96.36.199')...."
I found an internal article which talks about a similar issue. I just pasting root cause and resolution for your reference. Hope this answers your query,
the issue may be related to a character-length limitation in the Oracle database: On Oracle 11g, queries which use a key where the first 32 characters are identical will not use a database index defined on that key.
When determining the query plan for a SQL statement that uses this column in a WHERE clause, the database optimizer will choose not to select a perfectly valid index due to the histogram reporting it as non-selective.
The solution for this issue on Oracle 11g is to set the statistics gathering process to not gather histogram statistics on tables that show this behaviour. You can disable histogram stats on a table by table with the following command: