My Oracle Support Banner

Initialize Credit Summaries Table Program Is Taking Long In R12 (Doc ID 2436212.1)

Last updated on AUGUST 19, 2018

Applies to:

Oracle Order Management - Version 12.2.5 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.5 version, Transaction Entry

ACTUAL BEHAVIOR
----------------------
The program 'Initialize Credit Summaries Table' is taking around 60+ mins in R12 as compared to 10 mins in 11i. Since this is an important program for business, user requirement is that it should complete quicker as it was in Betsy.
The main reason for delay is sql_id '5ygpx7h4ad6ha' is having 369Million buffer_gets per execution and execution time of around 30 mins.

SQL ID: 5ygpx7h4ad6ha
-----------------------------
INSERT INTO OE_INIT_CREDIT_SUMM_GTT (LINE_ORDERED_QUANTITY ,
 LINE_UNIT_SELLING_PRICE, ORDER_INVOICE_TO_ORG_ID, RL_AMOUNT,
 RL_QUANTITY_ORDERED, ORDER_ORG_ID , ORDER_TRANSACTIONAL_CURR_CODE,
 LINE_SCHEDULE_SHIP_DATE , LINE_REQUEST_DATE , ORDER_REQUEST_DATE,
 ORDER_CREATION_DATE, LINE_TAX_VALUE , LINE_INVOICE_TO_ORG_ID,
 LINE_LINE_CATEGORY_CODE , LINE_INVOICED_QUANTITY , LINE_PAYMENT_TYPE_CODE ,
 ORDER_PAYMENT_TYPE_CODE, ORDER_ORDER_NUMBER , LINE_LINE_ID ,
 ORDER_HEADER_ID, LINE_HEADER_ID , ORDER_CUST_ACCOUNT_ID, ORDER_PARTY_ID,
 LINE_CUST_ACCOUNT_ID, LINE_PARTY_ID, INTERFACE_LINE_ATTRIBUTE6) SELECT /*+
 leading(L) use_nl(H SU S CA CA_L SU_L S_L) */ L.ORDERED_QUANTITY
 LINE_ORDERED_QUANTITY, L.UNIT_SELLING_PRICE LINE_UNIT_SELLING_PRICE,
 H.INVOICE_TO_ORG_ID ORDER_INVOICE_TO_ORG_ID, 0 RL_AMOUNT, 0
 RL_QUANTITY_ORDERED, H.ORG_ID ORDER_ORG_ID, H.TRANSACTIONAL_CURR_CODE
 ORDER_TRANSACTIONAL_CURR_CODE, L.SCHEDULE_SHIP_DATE LINE_SCHEDULE_SHIP_DATE,
  L.REQUEST_DATE LINE_REQUEST_DATE, H.REQUEST_DATE ORDER_REQUEST_DATE,
 H.CREATION_DATE ORDER_CREATION_DATE, NVL(L.TAX_LINE_VALUE,L.TAX_VALUE)
 LINE_TAX_VALUE, L.INVOICE_TO_ORG_ID LINE_INVOICE_TO_ORG_ID,
 L.LINE_CATEGORY_CODE LINE_LINE_CATEGORY_CODE, L.INVOICED_QUANTITY
 LINE_INVOICED_QUANTITY, L.PAYMENT_TYPE_CODE LINE_PAYMENT_TYPE_CODE,
 H.PAYMENT_TYPE_CODE ORDER_PAYMENT_TYPE_CODE, H.ORDER_NUMBER
 ORDER_ORDER_NUMBER, L.LINE_ID LINE_LINE_ID, H.HEADER_ID ORDER_HEADER_ID,
 L.HEADER_ID LINE_HEADER_ID, S.CUST_ACCOUNT_ID ORDER_CUST_ACCOUNT_ID,
 CA.PARTY_ID ORDER_PARTY_ID, S_L.CUST_ACCOUNT_ID LINE_CUST_ACCOUNT_ID,
 CA_L.PARTY_ID LINE_PARTY_ID, -999 INTERFACE_LINE_ATTRIBUTE6 FROM
 OE_ORDER_LINES_ALL L , OE_ORDER_HEADERS_ALL H , HZ_CUST_SITE_USES_ALL SU ,
 HZ_CUST_ACCT_SITES_ALL S , HZ_CUST_ACCOUNTS CA , HZ_CUST_SITE_USES_ALL SU_L
 , HZ_CUST_ACCT_SITES_ALL S_L , HZ_CUST_ACCOUNTS CA_L WHERE H.HEADER_ID =
 L.HEADER_ID AND H.BOOKED_FLAG = 'Y' AND H.OPEN_FLAG = 'Y' AND L.OPEN_FLAG =
 'Y' AND NVL( L.INVOICED_QUANTITY, 0 ) = 0 AND SU.SITE_USE_ID =
 H.INVOICE_TO_ORG_ID AND SU.CUST_ACCT_SITE_ID = S.CUST_ACCT_SITE_ID AND
 CA.CUST_ACCOUNT_ID = S.CUST_ACCOUNT_ID AND SU_L.SITE_USE_ID =
 L.INVOICE_TO_ORG_ID AND SU_L.CUST_ACCT_SITE_ID = S_L.CUST_ACCT_SITE_ID AND
 CA_L.CUST_ACCOUNT_ID = S_L.CUST_ACCOUNT_ID AND EXISTS ( SELECT /*+
 no_unnest */ NULL FROM OE_PAYMENT_TYPES_ALL T WHERE T.CREDIT_CHECK_FLAG =
 'Y' AND NVL(T.ORG_ID,-99) = NVL(H.ORG_ID, -99) AND L.PAYMENT_TYPE_CODE IS
 NOT NULL AND T.PAYMENT_TYPE_CODE = L.PAYMENT_TYPE_CODE UNION ALL SELECT /*+
 no_unnest */ NULL FROM OE_PAYMENT_TYPES_ALL T WHERE T.CREDIT_CHECK_FLAG =
 'Y' AND NVL(T.ORG_ID,-99) = NVL(H.ORG_ID, -99) AND L.PAYMENT_TYPE_CODE IS
 NULL AND H.PAYMENT_TYPE_CODE IS NOT NULL AND T.PAYMENT_TYPE_CODE =
 H.PAYMENT_TYPE_CODE UNION ALL SELECT /*+ no_unnest */ NULL FROM
 OE_PAYMENT_TYPES_ALL T WHERE T.CREDIT_CHECK_FLAG = 'Y' AND NVL(T.ORG_ID,-99)
  = NVL(H.ORG_ID, -99) AND L.PAYMENT_TYPE_CODE IS NULL AND
 H.PAYMENT_TYPE_CODE IS NULL ) UNION SELECT /*+ cardinality ( rl 10 )
 leading(rl h l)  */ 0 LINE_ORDERED_QUANTITY, 0 LINE_UNIT_SELLING_PRICE,
 H.INVOICE_TO_ORG_ID ORDER_INVOICE_TO_ORG_ID, RL.AMOUNT RL_AMOUNT,
 RL.QUANTITY_ORDERED RL_QUANTITY_ORDERED, H.ORG_ID ORDER_ORG_ID,
 H.TRANSACTIONAL_CURR_CODE ORDER_TRANSACTIONAL_CURR_CODE,
 L.SCHEDULE_SHIP_DATE LINE_SCHEDULE_SHIP_DATE, L.REQUEST_DATE
 LINE_REQUEST_DATE, H.REQUEST_DATE ORDER_REQUEST_DATE, H.CREATION_DATE
 ORDER_CREATION_DATE, DECODE(NVL(RL.INTERFACE_LINE_ATTRIBUTE11,0),0,
 NVL(L.TAX_LINE_VALUE,L.TAX_VALUE),0) LINE_TAX_VALUE, L.INVOICE_TO_ORG_ID
 LINE_INVOICE_TO_ORG_ID, L.LINE_CATEGORY_CODE LINE_LINE_CATEGORY_CODE,
 L.INVOICED_QUANTITY LINE_INVOICED_QUANTITY, L.PAYMENT_TYPE_CODE
 LINE_PAYMENT_TYPE_CODE, H.PAYMENT_TYPE_CODE ORDER_PAYMENT_TYPE_CODE,
 H.ORDER_NUMBER ORDER_ORDER_NUMBER, L.LINE_ID LINE_LINE_ID, H.HEADER_ID
 ORDER_HEADER_ID, L.HEADER_ID LINE_HEADER_ID, S.CUST_ACCOUNT_ID
 ORDER_CUST_ACCOUNT_ID, CA.PARTY_ID ORDER_PARTY_ID, S_L.CUST_ACCOUNT_ID
 LINE_CUST_ACCOUNT_ID, CA_L.PARTY_ID LINE_PARTY_ID,
 DECODE(OE_CREDIT_EXPOSURE_PVT.IS_EQUAL(RL.INTERFACE_LINE_ATTRIBUTE6,
 L.LINE_ID),'Y',-999,TO_NUMBER(RL.INTERFACE_LINE_ATTRIBUTE6))
 INTERFACE_LINE_ATTRIBUTE6 FROM OE_ORDER_LINES_ALL L , OE_ORDER_HEADERS_ALL
 H , HZ_CUST_SITE_USES_ALL SU , HZ_CUST_ACCT_SITES_ALL S , HZ_CUST_ACCOUNTS
 CA , HZ_CUST_SITE_USES_ALL SU_L , HZ_CUST_ACCT_SITES_ALL S_L ,
 HZ_CUST_ACCOUNTS CA_L , RA_INTERFACE_LINES_ALL RL WHERE H.HEADER_ID =
 L.HEADER_ID AND H.BOOKED_FLAG = 'Y' AND RL.ORIG_SYSTEM_BILL_CUSTOMER_ID =
 CA.CUST_ACCOUNT_ID AND NVL(RL.INTERFACE_STATUS, '~') <> 'P' AND
 RL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' AND RL.INTERFACE_LINE_ATTRIBUTE1 =
  H.ORDER_NUMBER AND NVL( L.INVOICED_QUANTITY, 0 ) <> 0 AND SU.SITE_USE_ID =
 H.INVOICE_TO_ORG_ID AND SU.CUST_ACCT_SITE_ID = S.CUST_ACCT_SITE_ID AND
 CA.CUST_ACCOUNT_ID = S.CUST_ACCOUNT_ID AND SU_L.SITE_USE_ID =
 L.INVOICE_TO_ORG_ID AND SU_L.CUST_ACCT_SITE_ID = S_L.CUST_ACCT_SITE_ID AND
 CA_L.CUST_ACCOUNT_ID = S_L.CUST_ACCOUNT_ID AND EXISTS ( SELECT /*+
 no_unnest */ NULL FROM OE_PAYMENT_TYPES_ALL T WHERE T.CREDIT_CHECK_FLAG =
 'Y' AND NVL(T.ORG_ID,-99) = NVL(H.ORG_ID, -99) AND L.PAYMENT_TYPE_CODE IS
 NOT NULL AND T.PAYMENT_TYPE_CODE = L.PAYMENT_TYPE_CODE UNION ALL SELECT /*+
 no_unnest */ NULL FROM OE_PAYMENT_TYPES_ALL T WHERE T.CREDIT_CHECK_FLAG =
 'Y' AND NVL(T.ORG_ID,-99) = NVL(H.ORG_ID, -99) AND L.PAYMENT_TYPE_CODE IS
 NULL AND H.PAYMENT_TYPE_CODE IS NOT NULL AND T.PAYMENT_TYPE_CODE =
 H.PAYMENT_TYPE_CODE UNION ALL SELECT /*+ no_unnest */ NULL FROM
 OE_PAYMENT_TYPES_ALL T WHERE T.CREDIT_CHECK_FLAG = 'Y' AND NVL(T.ORG_ID,-99)
  = NVL(H.ORG_ID, -99) AND L.PAYMENT_TYPE_CODE IS NULL AND
 H.PAYMENT_TYPE_CODE IS NULL )
;

EXPECTED BEHAVIOR
-----------------------
Expect better performance.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
The issue can be reproduced at will with the following steps:
1. Order Management Super User responsibility
2. Run Initialize Credit Summaries Table program.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot have the correct exposure on time.

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!


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