Slow Performance Running the Dimension Member Loader for the Customer Dimension

(Doc ID 429937.1)

Last updated on OCTOBER 04, 2016

Applies to:

Oracle Enterprise Performance Foundation - Version: 11.5.10
This problem can occur on any platform.

Symptoms

Running the customer dimension loading from a source set of data over 8.2 million customer records. The processing started today and has processed only 170 thousand records in about 3 hours.

In analysis the following SQL is the main issue:

SELECT b.rowid, t.rowid, b.customer_display_code, null, null, null, null,
b.value_set_display_code, v1.value_set_id,
b.dimension_group_display_code, d.dimension_group_id, 'LOAD',
t.customer_name, t.description, t.language, 'LOAD', to_date('12/31/2499',
'MM/DD/YYYY'), null, null
FROM fem_customers_b_t b, fem_customers_tl_t t, fem_dimension_grps_b d, fem_value_sets_b v1
WHERE b.status LIKE '%'
AND b.customer_display_code BETWEEN '110010224209' AND '110010249220'
AND t.status LIKE '%'
AND b.customer_display_code = t.customer_display_code
AND b.value_set_display_code = t.value_set_display_code
AND NOT EXISTS (SELECT 0
    FROM fem_customers_b g, fem_value_sets_b v2
    WHERE to_char(g.customer_display_code) = b.customer_display_code
    AND g.value_set_id = v2.value_set_id
    AND v2.value_set_display_code = b.value_set_display_code)
    AND b.dimension_group_display_code = d.dimension_group_display_code (+)
    AND d.dimension_id (+) = 16
    AND b.value_set_display_code = v1.value_set_display_code (+)
    AND v1.dimension_id (+) = 16
    AND t.language = userenv('LANG');

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:
i. Enterprise Performance Foundation Administrator -> Process Management -> Submit Requests
ii. Choose 'Dimension Member Loader' and 'Customer' dimension as run parameter

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