The Import Price Catalogs Is Taking Very Long Time To Process The Records (Doc ID 1611838.1)

Last updated on DECEMBER 01, 2016

Applies to:

Oracle Purchasing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Using Purchasing - the Import Price Catalog Program is taking a very long time.


A trace passed through the tkprof utility shows the expensive SQL statements, for example :


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 33 11.14 11.66 129 2157 505 2
Fetch 101 0.00 0.04 4 233 0 70
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 137 11.14 11.70 133 2390 505 72

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 87989 2.89 3.12 0 1 0 0
Execute 246370 2775.17 2973.07 50095 237117200 2850048 642184
Fetch 192593 9.15 29.16 11989 1038275 10 368172
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 526952 2787.21 3005.36 62084 238155476 2850058 1010356


INSERT INTO PO_SESSION_GT(KEY, NUM1) SELECT :B8 , :B1 FROM PO_LINES_INTERFACE
  INTF_LINES, PO_HEADERS_INTERFACE INTF_HEADERS WHERE
  INTF_LINES.INTERFACE_HEADER_ID = INTF_HEADERS.INTERFACE_HEADER_ID AND
  INTF_LINES.PROCESSING_ID = :B7 AND INTF_HEADERS.PROCESSING_ROUND_NUM = :B6
  AND INTF_HEADERS.PO_HEADER_ID = :B5 AND INTF_LINES.INTERFACE_LINE_ID < :B4
  AND INTF_LINES.INTERFACE_LINE_ID >= :B3 AND INTF_LINES.LINE_NUM = :B2



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 3 479.29 479.31 0 102447712 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 479.30 479.31 0 102447712 0 0


UPDATE PO_LINES_INTERFACE SET PROCESS_CODE = :B3 , PROCESSING_ID = -:B2
WHERE
 INTERFACE_LINE_ID = :B1 AND PROCESSING_ID = :B2 RETURNING INTERFACE_LINE_ID,
  INTERFACE_HEADER_ID INTO :O0 ,:O1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5 267.38 267.38 0 106535263 127 11
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 267.38 267.38 0 106535263 127 11


INSERT INTO PO_SESSION_GT(KEY, NUM1, NUM2) SELECT :B6 , :B1 ,
  PRICE_UPDATE_TOLERANCE FROM PO_ASL_ATTRIBUTES WHERE (ITEM_ID = :B4 OR
  CATEGORY_ID = :B3 OR CATEGORY_ID IN (SELECT MIC.CATEGORY_ID FROM
  MTL_ITEM_CATEGORIES MIC WHERE MIC.INVENTORY_ITEM_ID = :B7 AND
  MIC.ORGANIZATION_ID = :B5 ) ) AND VENDOR_ID = :B2 AND USING_ORGANIZATION_ID
  IN (-1, :B5 )



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 1514.18 1515.19 2267 25240769 68233 22037
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1514.18 1515.19 2267 25240769 68233 22037

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