PFT Rule Sets Very Slow When Opening, Updating or Creating (Doc ID 603335.1)

Last updated on MAY 07, 2015

Applies to:

Oracle Profitability Manager - Version 11.5.10 to 12.1.2
Oracle Enterprise Performance Foundation - Version 11.5.10.2 to 12.1.2
Information in this document applies to any platform.
Rule Set
FEM

Checked for relevance on 27-Sept-2013.

Symptoms

On 11.5.10.2 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.

EXPECTED BEHAVIOR
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.

Cause

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