R12: AP: Performance issue with Invoice Purge (APXIIPRG) (Doc ID 1997557.1)

Last updated on FEBRUARY 01, 2023

Applies to:

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

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