My Oracle Support Banner

Performance Issue for the Accounting Program - Problem with Global Temporary Tables (Doc ID 1508846.1)

Last updated on DECEMBER 14, 2017

Applies to:

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

Symptoms

On : R12 version, Subledger Accounting Uptake for Receivables

User runs Create Accounting (XLAACCPB), and this one calls Accounting Program (XLAACCUP). Accounting Program performance is very poor

 

Steps to Reproduce:

Responsibility: Receivables Manager
Navigation
: Control > Requests > Run

  1. Run Create Accounting

 

A trace/tkprof of the process shows the following query as the top one:

 

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 , L10.TRX_NUMBER SOURCE_3 , L4.DIST_CODE_COMBINATION_ID
 SOURCE_27 , L4.DIST_SOURCE_TYPE SOURCE_28 , L4.DIST_GAIN_CCID SOURCE_29 ,
 L4.DIST_MFAR_ADDITIONAL_ENTRY SOURCE_30 , L4.DIST_LOSS_CCID SOURCE_31 ,
 L7.REC_ACT_TYPE SOURCE_45 , L2.TRX_DISTRIBUTION_TYPE SOURCE_47 ,
 L10.TRX_ENTITY_CODE SOURCE_48 , L2.TRX_LINE_DIST_ID SOURCE_49 ,
 L10.TRX_CUSTOMER_TRX_ID SOURCE_50 , L4.DIST_LINE_ID SOURCE_51 ,
 L4.DISTRIBUTION_TYPE SOURCE_52 , L4.DIST_ENT_AMT SOURCE_53 ,
 L4.DIST_CURRENCY_CODE SOURCE_54 , L3.DIST_CUR_CONVERSION_DATE SOURCE_55 ,
 L3.DIST_CUR_CONVERSION_RATE SOURCE_56 , L3.DIST_CUR_CONVERSION_TYPE
 SOURCE_57 , L3.DIST_TO_ACCTD_AMT SOURCE_58 , L3.RECP_OVERRIDE_ACCTD_AMT
 SOURCE_59 , L3.DIST_TO_CUR_CONVERSION_DATE SOURCE_88 ,
 L3.DIST_TO_CUR_CONVERSION_RATE SOURCE_89 , L3.DIST_TO_CUR_CONVERSION_TYPE
 SOURCE_90 , L4.RCT_GAIN_LOSS_REF SOURCE_91 , L4.DIST_PARTY_ID SOURCE_92 ,
 L4.DIST_PARTY_SITE_ID SOURCE_93 , L4.DIST_PARTY_TYPE SOURCE_94 ,
 L4.DIST_SOURCE_TABLE SOURCE_95 , L4.DIST_ENT_AMT_FROM SOURCE_99 ,
 L4.DIST_CURRENCY_CODE_FROM SOURCE_100 , L3.DIST_ACCTD_AMT SOURCE_101
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_TRANSACTIONS_S_V L10 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 L10.EVENT_ID (+) =
 L3.EVENT_ID AND L10.LINE_NUMBER (+) = L3.LINE_NUMBER

 

The performance of this SQL statement, per the tkprof, seems to be related to the following operations:

 

   393805     393805     393805                TABLE ACCESS BY INDEX ROWID AR_XLA_LINES_EXTRACT (cr=2183133402 pr=9 pw=0 time=294081619 us cost=0 size=167 card=1)
2245595688 2245595688 2245595688                 INDEX FULL SCAN AR_XLA_LINES_EXTRACT_N2 (cr=6817797 pr=0 pw=0 time=658211113 us cost=0 size=0 card=1)(object id 2721025)

 

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.