Performance issue with the Payables Period Close (Doc ID 1408983.1)

Last updated on NOVEMBER 12, 2015

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.
Performance issue with the Payables Period Close


Symptoms

Payables period close issue. Running extremely slow anywhere between 30mins-2.5hr and multiple out of sync accounting dates issue preventing close.  When you chose open to close, it takes approximately 30-40mins for it to come back and is still in open status

INSERT INTO AP_PERIOD_CLOSE_EXCPS_GT ( INVOICE_ID ,ACCOUNTING_EVENT_ID ,
ACCOUNTING_DATE ,ORG_ID ,INVOICE_NUM ,INVOICE_CURRENCY_CODE ,PARTY_ID ,
VENDOR_ID ,DOC_SEQUENCE_VALUE ,VOUCHER_NUM ,INVOICE_DATE ,INVOICE_AMOUNT ,
CANCELLED_DATE ,LEGAL_ENTITY_ID ,SOURCE_TYPE ,SOURCE_TABLE_NAME ) SELECT
AI.INVOICE_ID ,APPH.ACCOUNTING_EVENT_ID ,APPH.ACCOUNTING_DATE ,AI.ORG_ID ,
AI.INVOICE_NUM ,AI.INVOICE_CURRENCY_CODE ,AI.PARTY_ID ,AI.VENDOR_ID ,
AI.DOC_SEQUENCE_VALUE ,AI.VOUCHER_NUM ,AI.INVOICE_DATE ,AI.INVOICE_AMOUNT ,
AI.CANCELLED_DATE ,AI.LEGAL_ENTITY_ID ,:B7 ,:B6 FROM AP_INVOICES_ALL AI ,
AP_PREPAY_HISTORY_ALL APPH ,AP_ORG_ATTRIBUTES_GT ORG_GTT WHERE
AI.INVOICE_ID = APPH.INVOICE_ID AND ( APPH.ACCOUNTING_DATE BETWEEN :B5 AND
:B4 ) AND APPH.POSTED_FLAG IN ('N' , 'S', 'P') AND APPH.ACCOUNTING_EVENT_ID
IS NOT NULL AND AI.SET_OF_BOOKS_ID = :B3 AND AI.ORG_ID = ORG_GTT.ORG_ID AND
( :B2 <> :B1 OR (:B2 = :B1 AND ROWNUM = 1 ) )



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 2 972.96 11388.28 3246028 15845308 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 972.97 11388.28 3246028 15845308 0 0

INSERT INTO AP_PERIOD_CLOSE_EXCPS_GT ( INVOICE_ID ,INVOICE_LINE_NUMBER ,
ACCOUNTING_DATE ,ORG_ID ,INVOICE_NUM ,INVOICE_CURRENCY_CODE ,PARTY_ID ,
VENDOR_ID ,DOC_SEQUENCE_VALUE ,VOUCHER_NUM ,INVOICE_DATE ,INVOICE_AMOUNT ,
CANCELLED_DATE ,SOURCE_TYPE ,SOURCE_TABLE_NAME ) SELECT /*+ leading(org_gtt,
ail,aid,ai) */ AI.INVOICE_ID ,AIL.LINE_NUMBER ,AIL.ACCOUNTING_DATE ,
AIL.ORG_ID ,AI.INVOICE_NUM ,AI.INVOICE_CURRENCY_CODE ,AI.PARTY_ID ,
AI.VENDOR_ID ,AI.DOC_SEQUENCE_VALUE ,AI.VOUCHER_NUM ,AI.INVOICE_DATE ,
AI.INVOICE_AMOUNT ,AI.CANCELLED_DATE ,:B7 ,:B6 FROM AP_INVOICES_ALL AI ,
AP_INVOICE_LINES_ALL AIL ,AP_ORG_ATTRIBUTES_GT ORG_GTT WHERE AI.INVOICE_ID =
AIL.INVOICE_ID AND (AIL.ACCOUNTING_DATE BETWEEN :B5 AND :B4 ) AND NOT
EXISTS (SELECT /*+ nl_aj */ 1 FROM AP_INVOICE_DISTRIBUTIONS_ALL AID WHERE
AID.INVOICE_ID = AIL.INVOICE_ID AND AID.INVOICE_LINE_NUMBER =
AIL.LINE_NUMBER AND AID.ORG_ID = ORG_GTT.ORG_ID ) AND AIL.DISCARDED_FLAG <>
'Y' AND AIL.AMOUNT <> 0 AND AI.CANCELLED_DATE IS NULL AND
AIL.SET_OF_BOOKS_ID = :B3 AND AIL.ORG_ID = ORG_GTT.ORG_ID AND ( :B2 <> :B1
OR (:B2 = :B1 AND ROWNUM = 1 ) ) AND AI.APPROVAL_READY_FLAG <> 'S'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 38.71 286.52 37481 3436786 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 38.71 286.52 37481 3436786 0 0

Changes

None

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