Dimension Member Loader Process Runs Very Slow on the FEM_xxxxxx_HIER Table
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.).
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