Slow Percent Distribution Mapping Rule Does Full Scan on FEM_BALANCES Partition (Doc ID 848262.1)

Last updated on JULY 20, 2009

Applies to:

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

Symptoms

In Oracle Profitability Manager (PFT) 11i, a new Percent Distribution Mapping Rule that only uses FEM_BALANCES is running very slow.  This rule has been slow since you created it.

Looking at an Explain Plan, the slow SQL is the SQL used to perform the percent distribution calculation.

Below is an example of the slow SQL for a rule that uses Channel, Product and User Dimension02 to perform the Pct Distribution:

SQL 1:

select SUM(a.XTD_BALANCE_F)AmntPct, a.CHANNEL_ID, a.PRODUCT_ID, a.USER_DIM2_ID from FEM_BALANCES a 
where a.CHANNEL_ID=:CHANNEL_ID 
and a.PRODUCT_ID=:PRODUCT_ID
and (a.USER_DIM2_ID IN ( SELECT CHILD_ID FROM FEM_USER_DIM2_HIER WHERE PARENT_ID IN
(36689,36687,52755) AND HIERARCHY_OBJ_DEF_ID = 105975 AND PARENT_ID <> CHILD_ID AND PARENT_VALUE_SET_ID = 20 AND CHILD_VALUE_SET_ID = 20)
AND (a.FINANCIAL_ELEM_ID = 37102) 
AND (a.NATURAL_ACCOUNT_ID = 37114)
AND (((a.DATASET_CODE =11001)and(a.CAL_PERIOD_ID =24549220000000000000031100100140)) ) AND
(a.CREATED_BY_OBJECT_ID NOT IN(62581))
AND (a.LEDGER_ID = 2002))and(a.CURRENCY_TYPE_CODE IN ('ENTERED','TOTAL')) group by a.CHANNEL_ID,
a.PRODUCT_ID, a.USER_DIM2_ID


SQL 2:

select SUM(a.XTD_BALANCE_F)AmntPct from FEM_BALANCES a 
where a.CHANNEL_ID=:CHANNEL_ID
and a.PRODUCT_ID=:PRODUCT_ID
and (a.USER_DIM2_ID IN ( SELECT CHILD_ID FROM FEM_USER_DIM2_HIER WHERE PARENT_ID IN (36689,36687,52755) AND HIERARCHY_OBJ_DEF_ID = 105975 AND PARENT_ID <> CHILD_ID AND PARENT_VALUE_SET_ID = 20 AND CHILD_VALUE_SET_ID = 20)
AND (a.FINANCIAL_ELEM_ID = 37102) 
AND (a.NATURAL_ACCOUNT_ID = 37114)
AND (((a.DATASET_CODE =11001)and(a.CAL_PERIOD_ID =24549220000000000000031100100140)) ) AND (a.CREATED_BY_OBJECT_ID NOT IN(62581))
AND (a.LEDGER_ID = 2002))and(a.CURRENCY_TYPE_CODE IN ('ENTERED','TOTAL'))


Steps To Reproduce:

  1. Go to the Profitability Manager responsibility
  2. Go to Business Rule > Mapping
  3. Run the Percent Distribution Mapping Rule

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