Interface Trip Stop Process Running Long For Large Deliveries (Doc ID 1988388.1)

Last updated on NOVEMBER 10, 2016

Applies to:

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

Symptoms

On : 12.1.3 version, Performance Issues

ACTUAL BEHAVIOR
---------------
Interface trip stop takes 5 hours when running for a large amount of records such as 53,000. You call the Ship Confirm API with Defer Interface as No, so the API triggers the Interface Trip Stop process for each delivery/trip.

The following is identified as the most expensive sql:

BEGIN WSH_SHIP_CONFIRM_ACTIONS.interface_ALL_wrp(:errbuf,:rc,:A0,:A1,:A2,:A3)
 ; END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1285.12 2141.52 983 39271 230743 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1285.12 2141.53 983 39271 230743 1

Customer does not want to defer the ITS process and run it individually using ITS - SRS because they would cause dead locks as detailed in note below. They have scenarios where the same order could get shipped in multiple deliveries.
Deadlocks occur when Multiple ITS processes are running (Doc ID 822636.1)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Ship Confirm API


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