My Oracle Support Banner

Performance issue with Invoice Validation (Doc ID 1928835.1)

Last updated on FEBRUARY 03, 2019

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

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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.