Interface Trip Stop Slow After Upgrade From 12.1.3 To 12.2.6

(Doc ID 2365996.1)

Last updated on FEBRUARY 26, 2018

Applies to:

Oracle Shipping Execution - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.6 version, Interface Trip Stop

ACTUAL BEHAVIOR
---------------
Interface Trip Stop is running slow after upgrade from 12.1.3 to 12.2.6.

Interface Trip stop program took almost 7 hours to complete shipment of 3000 orders.
Even 20 orders takes 35 minutes to complete

EXPECTED BEHAVIOR
-----------------------
Interface Trip Stop should complete in a timely manner

TKPROF shows the following poorly performing SQL:

UPDATE WF_ITEM_ACTIVITY_STATUSES SET ACTIVITY_STATUS = :B8 ,
ACTIVITY_RESULT_CODE = NVL(:B13 , ACTIVITY_RESULT_CODE), BEGIN_DATE =
NVL(:B11 , BEGIN_DATE), END_DATE = NVL(:B12 , END_DATE), DUE_DATE =
DECODE(:B11 , TO_DATE(NULL), DUE_DATE, :B10 ), OUTBOUND_QUEUE_ID = :B9 ,
EXECUTION_TIME = DECODE(:B8 , :B7 , :B4 , :B6 , NVL(EXECUTION_TIME, :B4 ),
:B5 , NVL(EXECUTION_TIME, :B4 ), EXECUTION_TIME)
WHERE
ITEM_TYPE = :B3 AND ITEM_KEY = :B2 AND PROCESS_ACTIVITY = :B1

call       count    cpu    elapsed     disk           query     current     rows
-------    ------ -------- ---------- ----------   ---------- ---------- -------
Parse         1       0.00      0.00         0               0               0          0
Execute  121    233.28  374.54     187   20422214         1969       121
Fetch         0        0.00       0.00       0                0              0           0
------- ------ -------- ---------- ---------- ---------- ---------- ------- -------
total       122    233.28 374.54       187  20422214         1969        121

Note: The query shows over 20 million records (20422214)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Using the OM Super User Responsibility, navigate to Shipping> Interfaces> Interface Trip Stop.
2. Using the delivery or trip stop id, run the process.
3. Process hangs.

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