Question
Lloyds Banking Group PLC
GB
Last activity: 12 Feb 2020 9:05 EST
Pega0005 alert o OOTB tables
v7.3.1 / WAS 8.5.5 / Oracle 11g
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
v7.3.1 / WAS 8.5.5 / Oracle 11g
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(' Proprietary information hidden') 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(' Proprietary information hidden') 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
Lloyds Banking Group PLC
GB
Thank you for your reply.
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(' Proprietary information hidden')...."
Areteans
IN
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,
ROOT CAUSE 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.
RESOLUTION 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:
SQL> exec dbms_stats.set_table_prefs(ownname=>'<DATABASE_SCHEMA_NAME>',- tabname=>'<NAME_OF_THE_TABLE>',- pname=>'METHOD_OPT',- pvalue => 'FOR ALL COLUMNS SIZE 1' );
Refer below article
Also try to disable the Hints on the table if it’s not in use .
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,
ROOT CAUSE 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.
RESOLUTION 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:
SQL> exec dbms_stats.set_table_prefs(ownname=>'<DATABASE_SCHEMA_NAME>',- tabname=>'<NAME_OF_THE_TABLE>',- pname=>'METHOD_OPT',- pvalue => 'FOR ALL COLUMNS SIZE 1' );
Refer below article
Also try to disable the Hints on the table if it’s not in use .