Slow Performance of Mapping Rules - Merge Join Cartesian on Data Slice Query (Doc ID 1055478.1)

Last updated on MARCH 21, 2017

Applies to:

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

Symptoms

In Profitability Manager (PFT) 11i, Mapping Rule Rule Sets are running very slow.  There has been a significant decrease in performance.  After generating an AWR SQL report showing the execution plan or a SQL trace with Explain Plan, you can see the Data Slice SQL is performing a costly merge join cartesian on the dimension component tables.

Below is an example of the SQL statement generating the merge join cartesian:

select /*+ index_ffs(a,FEM_BALANCES_U2) parallel_index(a,FEM_BALANCES_U2,16) */ distinct a.NATURAL_ACCOUNT_ID
    from apps.FEM_BALANCES a
where (a.NATURAL_ACCOUNT_ID IN
       ( SELECT CHILD_ID
       FROM apps.FEM_NAT_ACCTS_HIER
       WHERE PARENT_ID IN (37094,37023,37070,37082,55764,37071)
       AND HIERARCHY_OBJ_DEF_ID = 120319 AND PARENT_ID <> CHILD_ID AND PARENT_VALUE_SET_ID = 2
       AND CHILD_VALUE_SET_ID = 2)
AND (a.FINANCIAL_ELEM_ID = 100)
AND a.LINE_ITEM_ID IN (66787,66807,66827)
AND (a.COMPANY_COST_CENTER_ORG_ID IN
      ( SELECT COMPANY_COST_CENTER_ORG_ID
      FROM apps.FEM_CCTR_ORGS_ATTR
      WHERE ATTRIBUTE_ID = 10293
      AND VERSION_ID = 13001
      AND DIM_ATTRIBUTE_VARCHAR_MEMBER = 'TEST' AND VALUE_SET_ID = 8))
AND (((a.DATASET_CODE =11001)
and (a.CAL_PERIOD_ID =24551970000000000000121100100140)))
AND (a.CREATED_BY_OBJECT_ID NOT IN(70896))
AND (a.LEDGER_ID = 2002))
and(a.CURRENCY_TYPE_CODE IN ('ENTERED','TOTAL'))
order by a.NATURAL_ACCOUNT_ID



Below is an example of an execution plan showing the cartesian join from an AWR SQL report:

| 11 |       MERGE JOIN CARTESIAN|                       |  1 | 43 | 10 (0)| 00:00:01 | | |
| 12 |        INDEX RANGE SCAN   | FEM_CCTR_ORGS_ATTR_PK |  1 | 22 |  3 (0)| 00:00:01 | | |
| 13 |        BUFFER SORT        |                       | 21 | 441 | 7 (0)| 00:00:01 | | |
| 14 |         INLIST ITERATOR   |                       |    | | | | | |
| 15 |          INDEX RANGE SCAN | FEM_NAT_ACCTS_HIER_PK | 21 | 441 | 7 (0)| 00:00:01 | | |


In the example above, the merge join cartesian occurs because the optimizer thinks there is only 1 row in FEM_CCTR_ORGS_ATTR.  In fact, this table contains thousands of rows and a merge join cartesian is far from optimal and is causing serious performance degradation.

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