Slow Performance on Mapping Rule - High Disk Reads Using Large FEM_BALANCES_P Index (Doc ID 760300.1)

Last updated on AUGUST 20, 2015

Applies to:

Oracle Enterprise Performance Foundation - Version 11.5.10 to 11.5.10.2 [Release 11.5]
Oracle Profitability Manager - Version 11.5.10 to 11.5.10.2 [Release 11.5]
Information in this document applies to any platform.
Mapping Rules
FEMCCE

Symptoms

In Profitability Manager 11i, a Mapping Rule that processes against the FEM_BALANCES table is running very slowly.  The initial data source of the rule includes a hierarchy dimension component, an attribute dimension component or both.

After performing a SQL trace, you can see a SQL statement similar to the following is slow:

select distinct a.CREATED_BY_REQUEST_ID,a.CREATED_BY_OBJECT_ID 
from
 FEM_BALANCES a where ((((a.FINANCIAL_ELEM_ID > 280248 OR a.FINANCIAL_ELEM_ID 
  < 280248) AND (a.FINANCIAL_ELEM_ID > 100 OR a.FINANCIAL_ELEM_ID < 100) AND 
  ((a.FINANCIAL_ELEM_ID > 280249 OR a.FINANCIAL_ELEM_ID < 280249))) AND 
  (a.LINE_ITEM_ID IN ( SELECT CHILD_ID FROM FEM_LN_ITEMS_HIER WHERE CHILD_ID 
  IN (281508) AND HIERARCHY_OBJ_DEF_ID = 34492 AND PARENT_DEPTH_NUM = 1 AND 
  PARENT_VALUE_SET_ID = 10005 AND CHILD_VALUE_SET_ID = 10005 UNION ALL  
  SELECT CHILD_ID FROM FEM_LN_ITEMS_HIER WHERE PARENT_ID IN (281508) AND 
  HIERARCHY_OBJ_DEF_ID = 34492 AND PARENT_ID <> CHILD_ID AND 
  PARENT_VALUE_SET_ID = 10005 AND CHILD_VALUE_SET_ID = 10005) AND 
  (a.USER_DIM6_ID IN ( SELECT USER_DIM6_ID FROM FEM_USER_DIM6_ATTR WHERE 
  ATTRIBUTE_ID = 10237 AND VERSION_ID = 10009 AND 
  DIM_ATTRIBUTE_VARCHAR_MEMBER = 'USD' AND VALUE_SET_ID = 24))) AND 
  (((a.DATASET_CODE =11003)and(a.CAL_PERIOD_ID =
  24544970000000000000011100200140)) OR((a.DATASET_CODE =28008)
  and(a.CAL_PERIOD_ID =24544970000000000000011100200140)) ) AND 
  (a.CREATED_BY_OBJECT_ID NOT IN(17190)) AND (a.LEDGER_ID = 21003))
  and(a.CURRENCY_TYPE_CODE IN ('ENTERED','TOTAL')) order by 
  a.CREATED_BY_REQUEST_ID, a.CREATED_BY_OBJECT_ID


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    195.95     199.91    1237750    1404768          0         163
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    195.95     199.91    1237750    1404768          0         163

In addition to the large number of disk reads above, the Wait Event section shows a large number of times waited:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
  db file sequential read                   1237750        0.12         32.20
  SQL*Net more data to client                     1        0.00          0.00


Looking at the Explain Plan, the optimizer is using the FEM_BALANCES_P index to query the FEM_BALANCES table for values.  The FEM_BALANCES_P index is very large and contains almost all dimension columns on the FEM_BALANCES table.  Several dimension columns used in the "where" clause of the SQL are located in the middle or end of the index.

Additionally:

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