My Oracle Support Banner

Performance issue with Invoice Purge (APXIIPRG) (Doc ID 1997557.1)

Last updated on JANUARY 16, 2018

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.
Performance issue with Invoice Purge (APXIIPRG)

Symptoms

The following SQL statements are performing poor:

 

SQL ID: d1y1p54dhqq46 Plan Hash: 653406116

SELECT INVOICE_ID
FROM
AP_INVOICES_INTERFACE   WHERE UPPER(SOURCE )  =  UPPER(:b1 )   AND ( ( :b2
 IS NULL  )  OR ( GROUP_ID =  :b2 ) )  AND ( ( NVL(:b3 , 'N' )  =  'Y' )  OR
 ( STATUS =  'PROCESSED' ) )  AND ( ( :b4 IS NULL  )  OR ( ROWNUM  <=  :b4 )
 )  AND ( ( ORG_ID IS NOT NULL   AND :b5 IS NOT NULL   AND ORG_ID =  :b5 )  
 OR ( :b5 IS NULL   AND ORG_ID IS NOT NULL   AND
 MO_GLOBAL.CHECK_ACCESS(ORG_ID )  =  'Y' )  OR ( :b5 IS NOT NULL   AND
 ORG_ID IS NULL  )  OR ( :b5 IS NULL   AND ORG_ID IS NULL  ) )   ORDER BY
 VENDOR_ID , VENDOR_NUM , VENDOR_NAME , VENDOR_SITE_ID , VENDOR_SITE_CODE ,
 INVOICE_NUM FOR UPDATE OF INVOICE_ID NOWAIT


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5    934.41     987.66    1377168    1383851       4498           0
Fetch     4421      0.12       0.12          0          0          0        4416
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4427    934.53     987.78    1377168    1383851       4498        4416


SQL ID: 6cay1673z2knh Plan Hash: 1888348847

UPDATE AP_INVOICES_INTERFACE I SET ORG_ID = (SELECT HR.ORGANIZATION_ID FROM
 HR_OPERATING_UNITS HR  
WHERE
HR.NAME =  I.OPERATING_UNIT   )  WHERE I.ORG_ID IS NULL   AND
 I.OPERATING_UNIT IS NOT NULL  


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    201.61     264.75     275482     276773          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    201.62     264.76     275482     276773          3           1


SQL ID: gugfpw1yq9pn6 Plan Hash: 3913544918

SELECT COUNT (*)
FROM
AP_INVOICES_INTERFACE   WHERE UPPER(SOURCE )  =  UPPER(:b1 )   AND ( ( :b2
 IS NULL  )  OR ( GROUP_ID =  :b2 ) )  AND ( ( NVL(:b3 , 'N' )  =  'Y' )  OR
 ( STATUS =  'PROCESSED' ) )  AND ( ( ORG_ID IS NOT NULL   AND :b4 IS NOT
 NULL   AND ORG_ID =  :b4 )  OR ( :b4 IS NULL   AND ORG_ID IS NOT NULL   AND
 MO_GLOBAL.CHECK_ACCESS(ORG_ID )  =  'Y' )  OR ( :b4 IS NOT NULL   AND
 ORG_ID IS NULL  )  OR ( :b4 IS NULL   AND ORG_ID IS NULL  ) )  


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    143.50     144.22     276502     276592          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    143.50     144.22     276502     276592          0           1


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.