My Oracle Support Banner

Interface Trip Stop Slow After Upgrade From 12.1.3 To 12.2.6 (Doc ID 2365996.1)

Last updated on FEBRUARY 21, 2019

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
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

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
Cause
Solution
References


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