Slow Performance of Mapping Rules - Merge Join Cartesian on Data Slice Query
(Doc ID 1055478.1)
Last updated on JULY 18, 2018
Applies to:Oracle Profitability Manager - Version 11.5.10 to 220.127.116.11 [Release 11.5]
Oracle Enterprise Performance Foundation - Version 11.5.10 to 18.104.22.168 [Release 11.5]
Information in this document applies to any platform.
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:
from apps.FEM_BALANCES a
where (a.NATURAL_ACCOUNT_ID IN
( SELECT CHILD_ID
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
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:
| 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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!