Create Accounting Has Performance Issue After Applied the Patch20441168 (Doc ID 2061515.1)

Last updated on JANUARY 13, 2017

Applies to:

Oracle Receivables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

The create accounting program has bottleneck as following SQL after applied the patch20441168.

SQL ID: aat0kxnbway61
Plan Hash: 2507679971
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 , L11.TRX_TYPE_NAME SOURCE_3 , L11.TRX_NUMBER SOURCE_4 ,
  L11.TRX_DOC_SEQUENCE_VALUE SOURCE_6 , L4.DIST_CODE_COMBINATION_ID SOURCE_29
  , L4.DIST_SOURCE_TYPE SOURCE_30 , L4.DIST_GAIN_CCID SOURCE_31 ,
  L4.DIST_MFAR_ADDITIONAL_ENTRY SOURCE_32 , L4.DIST_LOSS_CCID SOURCE_34 ,
  L4.REVERSED_SOURCE_ID SOURCE_44 , L2.TRX_DISTRIBUTION_TYPE SOURCE_51 ,
  L11.TRX_ENTITY_CODE SOURCE_52 , L2.TRX_LINE_DIST_ID SOURCE_53 ,
  L11.TRX_CUSTOMER_TRX_ID SOURCE_54 , L4.DIST_LINE_ID SOURCE_82 ,
  L4.DISTRIBUTION_TYPE SOURCE_83 , L4.DIST_ENT_AMT_FROM SOURCE_84 ,
  L4.DIST_CURRENCY_CODE_FROM SOURCE_85 , L3.DIST_CUR_CONVERSION_DATE
  SOURCE_86 , L3.DIST_CUR_CONVERSION_RATE SOURCE_87 ,
  L3.DIST_CUR_CONVERSION_TYPE SOURCE_88 , L4.RCT_GAIN_LOSS_REF SOURCE_89 ,
  L3.DIST_ACCTD_AMT SOURCE_90 , L4.DIST_PARTY_ID SOURCE_91 ,
  L4.DIST_PARTY_SITE_ID SOURCE_92 , L4.DIST_PARTY_TYPE SOURCE_93 ,
  L8.APP_APPLICATION_ID SOURCE_95 , L7.REC_ACT_TYPE SOURCE_96 ,
  L4.DIST_ENT_AMT SOURCE_98 , L4.DIST_CURRENCY_CODE SOURCE_99 ,
  L3.DIST_TO_ACCTD_AMT SOURCE_100 , L3.RECP_OVERRIDE_ACCTD_AMT SOURCE_101 ,
  L3.DIST_TO_CUR_CONVERSION_DATE SOURCE_125 , L3.DIST_TO_CUR_CONVERSION_TYPE
  SOURCE_126 , L3.DIST_TO_CUR_CONVERSION_RATE SOURCE_127 ,
  L4.DIST_SOURCE_TABLE SOURCE_128
FROM
 XLA_EVENTS_GT XET , AR_CUST_TRX_LINES_L_V L2 , AR_DISTRIBUTIONS_BASE_V L3 ,
  AR_DISTRIBUTIONS_L_V L4 , AR_RECEIVABLES_TRX_ACT_S_V L7 ,
  AR_RECEIVABLE_APPS_L_V L8 , AR_TRANSACTIONS_S_V L11 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 AND L2.EVENT_ID (+) = L3.EVENT_ID AND L2.LINE_NUMBER (+) =
  L3.LINE_NUMBER AND L4.EVENT_ID = L3.EVENT_ID AND L4.LINE_NUMBER =
  L3.LINE_NUMBER AND L7.EVENT_ID (+) = L3.EVENT_ID AND L7.LINE_NUMBER (+) =
  L3.LINE_NUMBER AND L8.EVENT_ID (+) = L3.EVENT_ID AND L8.LINE_NUMBER (+) =
  L3.LINE_NUMBER AND L11.EVENT_ID (+) = L3.EVENT_ID AND L11.LINE_NUMBER (+) =
  L3.LINE_NUMBER


call     count       cpu    elapsed       disk      query    current      rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.01       0.00          0          0          0         0
Execute      1      1.58       1.59          0         92          0         0
Fetch      128   7512.64    7541.50       4156   97222319          0      63730
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total      130   7514.23    7543.10       4156   97222411          0      63730



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