Support Article
ORA-01013 error and Application Explorer is slow
SA-24513
Summary
User has performed upgrade of platform from Pega 7.1.9 to Pega 7.2, the server is very slow, especially when using the Application Explorer.
The query used to get the application details is taking more than 30 seconds, so it is getting canceled.
Error Messages
DatabaseException caused by prior exception: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
| SQL Code: 1013 | SQL State: 72000
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 1013 SQLState: 72000 Message: ORA-01013: user requested cancel of current operation
DatabaseException caused by prior exception: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
| SQL Code: 1013 | SQL State: 72000
2016-05-25 12:01:11,876 [fault (self-tuning)'] [ STANDARD] [ ] [ SGFSSoprano:01.01] (l.access.ConnectionManagerImpl) ERROR xxxx - Not returning connection 5 for database "pegarules" to the pool as it previously encountered the following error
User ID: xxxxx
Last SQL: 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 PEG_RULES_OWNER.pr_sys_app_hierarchy_flat "PC0AH" INNER JOIN PEG_RULES_OWNER.pr_sys_app_ruleset_index "PC0AR" ON ("PC0AH".pzAppHash = "PC0AR".pzAppHash) WHERE "PC0AH".pzTopAppHash = ? AND "PC0AH".pzAppName IN (? , ? , ? , ? , ? , ? , ? , ? , ? ) ) "PC0AR" WHERE "PC0AR"."pzRuleSetOrder" = 1 UNION ALL SELECT CAST(? AS VARCHAR(128)) AS "pzRuleSetName", 1 AS "pzRulesetVersionMajor", 1 AS "pzRulesetVersionMinor", 1 AS "pzRuleSetVersionPatch" FROM DUAL ) SELECT DISTINCT "ROC".PYCATEGORY AS "pyCategory" FROM PEG_RULES_OWNER.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 PEG_RULES_OWNER.pr4_base "ROC" ON ( ( "PC0".PYCLASS = "ROC".PYCLASSNAME ) AND "PC0".PXOBJCLASS = ? AND "ROC".PXOBJCLASS = ? ) WHERE ( "PC0".PYCLASSNAME = ? AND "PC0".PYCLASSNAME NOT LIKE ? AND ( "ROC".PYCATEGORY IS NOT NULL AND "ROC".PYCATEGORY <> ? ) AND "PC0".PXINSTANCELOCKEDKEY IS NULL AND ( "PC0".PYMETHODSTATUS IS NULL OR "PC0".PYMETHODSTATUS <> ? ) AND ( "ROC".PYMETHODSTATUS IS NULL OR ( "PC0".PYRULESET NOT LIKE ? AND "ROC".PYMETHODSTATUS = ? ) ) AND ( "PC0".PYRULESET NOT LIKE ? OR "PC0".PYMETHODSTATUS <> ? OR "PC0".PYMETHODSTATUS IS NULL ) AND "PC0".PYRULEAVAILABLE NOT IN (? , ? ) ) ORDER BY 1 ASC
java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
Steps to Reproduce
Open a class in Application Explorer.
Root Cause
It is found that the Oracle database was not using the indexes as expected, explain plan, and statistics were old.
Resolution
Update statistics using one of the following command below where "PRPC72RULES" is the name of the Oracle schema:
exec dbms_stats.gather_schema_stats(ownname=> 'PRPC72RULES', estimate_percent=>20);
OR
exec DBMS_STATS.gather_schema_stats('PRPC72RULES', estimate_percent => 20);
After this, performance issue gets resolved.
Published January 13, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.