PFT Rule Sets Very Slow When Opening, Updating or Creating
Last updated on MAY 07, 2015
Applies to:Oracle Profitability Manager - Version 11.5.10 to 12.1.2
Oracle Enterprise Performance Foundation - Version 18.104.22.168 to 12.1.2
Information in this document applies to any platform.
Checked for relevance on 27-Sept-2013.
On 22.214.171.124 in FEM.D.1 Rollup #4, when opening, updating and saving large rule sets is taking a very long time (more than a few minutes) and will sometimes cause an application time out. Per analysis of trace file, there may also be single long running SQL which is executing a full table scan on FEM_OBJECTS_CATALOG_B. The query gets re-executed for each member of this rule set exacerbating the issue for very large Rule Sets.
Long running SQL:
SELECT * FROM (SELECT RuleSetMemberEO.RULE_SET_OBJ_DEF_ID, RuleSetMemberEO.CHILD_OBJ_ID, RuleSetMemberEO.CHILD_EXECUTION_SEQUENCE, RuleSetMemberEO.EXECUTE_CHILD_FLAG, CatalogOfObjectEO.OBJECT_ID, CatalogOfObjectEO.OBJECT_NAME As OBJECT_DISPLAY_NAME, CatalogOfObjectEO.DESCRIPTION AS OBJECT_DESCRIPTION, decode(CatalogOfObjectEO.OBJECT_TYPE_CODE,'RULE_SET',fem_utils.getRuleSetObjec tDefID(CatalogOfObjectEO.OBJECT_ID),null) as CHILD_RS_OBJECT_DEFINITION_ID, RuleSetMemberEO.RULE_SET_OBJ_DEF_ID as OBJECT_DEFINITION_ID, 'Y' as DISABLED_FLAG, CatalogOfObjectEO.OBJECT_TYPE_CODE as OBJECT_TYPE_CD, FND_LOOKUPS.MEANING as FOLDER_PRIVILEGE FROM FEM.FEM_RULE_SET_MEMBERS RuleSetMemberEO, FEM_OBJECT_CATALOG_VL CatalogOfObjectEO, FND_LOOKUPS WHERE RuleSetMemberEO.child_obj_id = CatalogOfObjectEO.Object_ID and FND_LOOKUPS.LOOKUP_TYPE = 'YES_NO' and FND_LOOKUPS.LOOKUP_CODE = nvl(fem_utils.getFolderPrivilege(CatalogOfObjectEO.OBJECT_ID),'N')) QRSLT WHERE (:1 = RULE_SET_OBJ_DEF_ID) ORDER BY CHILD_EXECUTION_SEQUENCE
Some Rule Sets take so long to update that the application times out during update.
Expect rule sets to open and update within a reasonable time frame.
Steps To Reproduce
The issue can be reproduced at will with the following steps:
1. Attempt to open, update and save a large Rule Set ID which launches slow SQL.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms