Create Accounting - Receiving (CSTCRACCRCV) is Having a Poor Performance (Doc ID 1367269.1)

Last updated on JANUARY 13, 2017

Applies to:

Oracle Cost Management - Version 12.1.3 and later
Oracle Purchasing - Version 12.1.3 and later
Information in this document applies to any platform.
***Checked for relevance 21-March-2015***

Symptoms

On :  12.1.3 version, Subledger Accounting

ACTUAL BEHAVIOR  
-------------------------
Create Accounting - Receiving (CSTCRACCRCV) exhibits a poor performance (aprox 6 hours)

Patch.10244514:R12.BOM.C was applied and Gather Schema Statistics is run on a weekly basis.

SELECT /*+ leading(xet) cardinality(xet,1) */ XET.ENTITY_ID ,
XET.LEGAL_ENTITY_ID ,XET.ENTITY_CODE ,XET.TRANSACTION_NUMBER ,XET.EVENT_ID ,
XET.EVENT_CLASS_CODE ,XET.EVENT_TYPE_CODE ,XET.EVENT_NUMBER ,XET.EVENT_DATE
,XET.TRANSACTION_DATE ,XET.REFERENCE_NUM_1 ,XET.REFERENCE_NUM_2 ,
XET.REFERENCE_NUM_3 ,XET.REFERENCE_NUM_4 ,XET.REFERENCE_CHAR_1 ,
XET.REFERENCE_CHAR_2 ,XET.REFERENCE_CHAR_3 ,XET.REFERENCE_CHAR_4 ,
XET.REFERENCE_DATE_1 ,XET.REFERENCE_DATE_2 ,XET.REFERENCE_DATE_3 ,
XET.REFERENCE_DATE_4 ,XET.EVENT_CREATED_BY ,XET.BUDGETARY_CONTROL_FLAG ,
H1.XL_DESCRIPTION SOURCE_1 , H1.XL_CCID SOURCE_6 , H2.DISTRIBUTION_TYPE
SOURCE_9 , FVL9.MEANING SOURCE_9_MEANING , H2.CURRENCY_CODE SOURCE_11 ,
H2.CURRENCY_CONVERSION_DATE SOURCE_12 , H2.CURRENCY_CONVERSION_RATE
SOURCE_13 , H2.CURRENCY_CONVERSION_TYPE SOURCE_14 , H4.APPLIED_TO_APPL_ID
SOURCE_17 , H4.APPLIED_TO_DIST_LINK_TYPE SOURCE_18 ,
H4.APPLIED_TO_ENTITY_CODE SOURCE_19 , H4.PO_DISTRIBUTION_ID SOURCE_20 ,
H4.APPLIED_TO_PO_DOC_ID SOURCE_21 , H5.PO_BUDGET_ACCOUNT SOURCE_22 ,
H4.ENCUM_REVERSAL_AMOUNT_ENTERED SOURCE_23 , H4.ENCUMBRANCE_REVERSAL_AMOUNT
SOURCE_24 , H7.CST_PEA_ENC_UPG_OPTION SOURCE_25 ,
H6.PURCH_ENCUMBRANCE_TYPE_ID SOURCE_26 , H2.TRANSFER_TO_GL_INDICATOR
SOURCE_44 , FVL44.MEANING SOURCE_44_MEANING
FROM
XLA_EVENTS_GT XET , CST_XLA_PO_DIST_REF_V H1 , CST_XLA_RCV_HEADERS_V H2 ,
CST_XLA_RCV_REF_V H4 , PO_DISTS_REF_V H5 , PO_HEADERS_REF_V H6 ,
PSA_CST_XLA_PEA_UPG_V H7 , FND_LOOKUP_VALUES FVL9 , FND_LOOKUP_VALUES FVL44
WHERE XET.EVENT_DATE BETWEEN :B3 AND :B2 AND XET.EVENT_CLASS_CODE = :B1 AND
XET.EVENT_STATUS_CODE <> 'N' AND H2.EVENT_ID = XET.EVENT_ID AND
H1.XL_DISTRIBUTION_ID (+) = H4.PO_DISTRIBUTION_ID AND
H4.REF_RCV_ACCOUNTING_EVENT_ID = H2.RCV_ACCOUNTING_EVENT_ID AND
H4.PO_HEADER_ID = H5.PO_HEADER_ID (+) AND H4.PO_DISTRIBUTION_ID =
H5.PO_DISTRIBUTION_ID (+) AND H4.PO_HEADER_ID = H6.PO_HEADER_ID (+) AND
H4.REF_RCV_ACCOUNTING_EVENT_ID = H7.ACCOUNTING_EVENT_ID (+) AND
FVL9.LOOKUP_TYPE(+) = 'CST_DISTRIBUTION_TYPE' AND FVL9.LOOKUP_CODE(+) =
H2.DISTRIBUTION_TYPE AND FVL9.VIEW_APPLICATION_ID(+) = 700 AND
FVL9.LANGUAGE(+) = USERENV('LANG') AND FVL44.LOOKUP_TYPE(+) = 'YES_NO' AND
FVL44.LOOKUP_CODE(+) = H2.TRANSFER_TO_GL_INDICATOR AND
FVL44.VIEW_APPLICATION_ID(+) = 0 AND FVL44.LANGUAGE(+) = USERENV('LANG')
ORDER BY EVENT_ID

takes 15856.33 seconds


and

SELECT /*+ leading(xet) cardinality(xet,1) */ XET.ENTITY_ID ,
XET.LEGAL_ENTITY_ID ,XET.ENTITY_CODE ,XET.TRANSACTION_NUMBER ,XET.EVENT_ID ,
XET.EVENT_CLASS_CODE ,XET.EVENT_TYPE_CODE ,XET.EVENT_NUMBER ,XET.EVENT_DATE
,XET.TRANSACTION_DATE ,XET.REFERENCE_NUM_1 ,XET.REFERENCE_NUM_2 ,
XET.REFERENCE_NUM_3 ,XET.REFERENCE_NUM_4 ,XET.REFERENCE_CHAR_1 ,
XET.REFERENCE_CHAR_2 ,XET.REFERENCE_CHAR_3 ,XET.REFERENCE_CHAR_4 ,
XET.REFERENCE_DATE_1 ,XET.REFERENCE_DATE_2 ,XET.REFERENCE_DATE_3 ,
XET.REFERENCE_DATE_4 ,XET.EVENT_CREATED_BY ,XET.BUDGETARY_CONTROL_FLAG ,
L3.LINE_NUMBER , L3.CODE_COMBINATION_ID SOURCE_5 ,
L3.DISTRIBUTION_IDENTIFIER SOURCE_8 , L3.ENTERED_AMOUNT SOURCE_10 ,
L3.ACCOUNTED_AMOUNT SOURCE_15 , L3.RCV_ACCOUNTING_LINE_TYPE SOURCE_16
FROM
XLA_EVENTS_GT XET , CST_XLA_RCV_LINES_V L3 WHERE XET.EVENT_ID BETWEEN :B5
AND :B4 AND XET.EVENT_DATE BETWEEN :B3 AND :B2 AND XET.EVENT_CLASS_CODE =
:B1 AND XET.EVENT_STATUS_CODE <> 'N' AND L3.EVENT_ID = XET.EVENT_ID

takesn 9166.44 seconds


EXPECTED BEHAVIOR
-----------------------
expect a better performance


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. run Create Accounting - Receiving (CSTCRACCRCV)

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