Performance issue with Invoice Validation

(Doc ID 1928835.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.
Invoice Validation taking over 30 hours.

Symptoms

Invoice Validation taking over 30 hours.

SQL ID: 21ayq4sk91dma Plan Hash: 776756430

UPDATE AP_INVOICE_LINES_ALL AIL SET ( AIL.TOTAL_REC_TAX_AMOUNT,
 AIL.TOTAL_NREC_TAX_AMOUNT, AIL.TOTAL_REC_TAX_AMT_FUNCL_CURR,
 AIL.TOTAL_NREC_TAX_AMT_FUNCL_CURR ) = (SELECT
 SUM(DECODE(NVL(ZD.RECOVERABLE_FLAG, 'N'), 'Y', NVL(ZD.REC_NREC_TAX_AMT, 0),
 0)), SUM(DECODE(NVL(ZD.RECOVERABLE_FLAG, 'N'), 'N', NVL(ZD.REC_NREC_TAX_AMT,
  0), 0)), SUM(DECODE(NVL(ZD.RECOVERABLE_FLAG, 'N'), 'Y',
 NVL(ZD.REC_NREC_TAX_AMT_FUNCL_CURR, 0), 0)),
 SUM(DECODE(NVL(ZD.RECOVERABLE_FLAG, 'N'), 'N',
 NVL(ZD.REC_NREC_TAX_AMT_FUNCL_CURR, 0), 0)) FROM ZX_REC_NREC_DIST ZD
WHERE
APPLICATION_ID = :B6 AND ENTITY_CODE = :B5 AND EVENT_CLASS_CODE IN (:B4 ,
 :B3 , :B2 ) AND ZD.TRX_ID = AIL.INVOICE_ID AND AIL.SUMMARY_TAX_LINE_ID =
 ZD.SUMMARY_TAX_LINE_ID AND ZD.INCLUSIVE_FLAG = 'N' AND
 NVL(ZD.SELF_ASSESSED_FLAG, 'N') = 'N' ) WHERE AIL.INVOICE_ID = :B1 AND
 AIL.SUMMARY_TAX_LINE_ID IS NOT NULL AND AIL.LINE_TYPE_LOOKUP_CODE = 'TAX'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    841    395.75     399.99         48  157089945      64572       31915
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      842    395.75     399.99         48  157089945      64572       31915


SQL ID: 5m98u10taz9ks Plan Hash: 2289217495

DELETE FROM ZX_LINES ZL
WHERE
ZL.APPLICATION_ID = :B3 AND ZL.ENTITY_CODE = :B2 AND ZL.EVENT_CLASS_CODE =
 :B1 AND (ZL.TRX_ID) IN (SELECT /*+ use_hash(ZX_LINES_DET_FACTORS) */ TRX_ID
 FROM ZX_LINES_DET_FACTORS WHERE APPLICATION_ID = :B3 AND ENTITY_CODE = :B2
 AND EVENT_CLASS_CODE = :B1 AND EVENT_ID = :B4 ) AND (ZL.DELETE_FLAG = 'Y'
 OR (ZL.TRX_LINE_ID,ZL.TRX_LEVEL_TYPE) IN (SELECT ZLDF.TRX_LINE_ID,
 ZLDF.TRX_LEVEL_TYPE FROM ZX_LINES_DET_FACTORS ZLDF WHERE
 ZLDF.APPLICATION_ID = ZL.APPLICATION_ID AND ZLDF.ENTITY_CODE =
 ZL.ENTITY_CODE AND ZLDF.EVENT_CLASS_CODE = ZL.EVENT_CLASS_CODE AND
 ZLDF.TRX_ID = ZL.TRX_ID AND ZLDF.EVENT_ID = :B4 AND ZLDF.LINE_LEVEL_ACTION
 NOT IN ('SYNCHRONIZE', 'NO_CHANGE') ) )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     31.97     284.49      15820     292337    5538125      220554
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     31.97     284.49      15820     292337    5538125      220554

SQL ID: 05ywgcja87h9m Plan Hash: 4274924946

UPDATE AP_INVOICE_DISTRIBUTIONS_ALL D SET MATCH_STATUS_FLAG = 'S'
WHERE
NVL(MATCH_STATUS_FLAG, '!') <> 'A' AND NVL(D.POSTED_FLAG, 'N' ) = 'N' AND
 D.INVOICE_ID = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      848      0.01       0.01          0          0          0           0
Execute   1682    197.70     261.89       5844      32220    1273221      313978
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2530    197.72     261.90       5844      32220    1273221      313978



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