Dimension Member Loader Process Runs Very Slow on the FEM_xxxxxx_HIER Table

(Doc ID 560170.1)

Last updated on JULY 28, 2017

Applies to:

Oracle Enterprise Performance Foundation - Version: 11.5.10 to 12.1.2 - Release: 11.5 to
Information in this document applies to any platform.
Dimension Member Loader

Symptoms

In Enterprise Performance Foundation Administrator 11i or R12, the Dimension Member Loader program is running for a very long time when loading approximately 4,000 members to a dimension. A "Sub-Request" of the loader is taking most of the time when executing SQL like the following:

SELECT count(*) FROM
FEM_USER_DIM1_HIER H,
fem_object_definition_b D,fem_hierarchies O WHERE
H.hierarchy_obj_def_id = D.object_definition_id
AND D.object_id = O.hierarchy_obj_id
AND O.group_sequence_enforced_code IN
('SEQUENCE_ENFORCED','SEQUENCE_ENFORCED_SKIP_LEVEL')
AND to_char(child_id) = to_char(:b_member_id) AND
H.child_value_set_id = :b_value_set_id;


In the example above, the FEM_USER_DIM1_HIER table contains a large quantity of records (ex. 1,000,000+).  Full table scans are performed on FEM_HIERARCHIES, FEM_OBJECT_DEFINITION_B, and FEM_USER_DIM1_HIER.

Note: The SQL Trace functions in Oracle Applications (Program trace, Initialization trace, etc.) did not pick up the Sub-Request SQL and the DBA had to identify the slow SQL using a DBA tool (ex. Toad, Enterprise Manager, etc.).

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