Catalog Bulk Load - Items Upload - Running Very Long Time - Poor Performance (Doc ID 2177509.1)

Last updated on APRIL 12, 2017

Applies to:

Oracle iProcurement - Version 12.1.3 to 12.2 [Release 12.1 to 12.2]
Information in this document applies to any platform.

Symptoms

Using Purchasing, Version 12.2.5

Actual:
The upload process current in 12.1.3 runs within a minute

In the upgraded 12.2.5 it is taking more that 3 hours to complete. This is unacceptable.


Expected:
Catalog bulk load should run as it did in 12.1.3, very quickly.

Error:
-------



Replication Steps:
The issue can be reproduced at will with the following steps:
1. Upload the catalog in 12.2.5.
2. Find it is taking a large amount of time.


Other:

Per the trace which was taken to investigate further:

SQL ID: 0xdx22vu9j1p3 Plan Hash: 1706093814

INSERT INTO PO_SESSION_GT ( KEY , INDEX_NUM1 , INDEX_NUM2 , CHAR5 ) SELECT
:B2 , POL.PO_LINE_ID , POL.PO_HEADER_ID , 'QUOTATION' FROM PO_LINES_ALL POL,
PO_HEADERS_ALL POH WHERE POL.PO_HEADER_ID = :B1 AND ( EXISTS ( SELECT
'last update date of po_headers or po_lines is greater than ip record' FROM
ICX_CAT_ITEMS_CTX_HDRS_TLP HDRS WHERE HDRS.PO_LINE_ID = POL.PO_LINE_ID AND
(POL.LAST_UPDATE_DATE > HDRS.LAST_UPDATE_DATE OR POH.LAST_UPDATE_DATE >
HDRS.LAST_UPDATE_DATE ) ) OR NOT EXISTS ( SELECT '1' FROM
ICX_CAT_ITEMS_CTX_HDRS_TLP HDRS WHERE HDRS.PO_LINE_ID = POL.PO_LINE_ID ) )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 12651.99 12779.72 2923 158864274 2856762776 218452000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 12651.99 12779.72 2923 158864274 2856762776 218452000

Changes

 

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