My Oracle Support Banner

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


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_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
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.).


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.