My Oracle Support Banner

API Wsh_deliveries_pub.delivery_action Used In Custom Program Has Slow Performance (Doc ID 2281093.1)

Last updated on MARCH 19, 2019

Applies to:

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

Symptoms

On : 12.2.4 version,

Observing slow performance with Ship Confirm.

- Have created a custom program to ship confirm the deliveries.
- This program has slow performance.
- Observed that wsh_deliveries_pub.delivery_action API which is being used in the custom program is taking a long time.

TKPROF shows the following:

SELECT 1
FROM
 WSH_DELIVERY_LEGS WDL, WSH_TRIP_STOPS WTS WHERE WDL.DELIVERY_ID = :B1 AND (
  WDL.PICK_UP_STOP_ID = WTS.STOP_ID OR WDL.DROP_OFF_STOP_ID = WTS.STOP_ID )
  AND WTS.STATUS_CODE <> 'CL' AND EXISTS ( SELECT 1 FROM WSH_DELIVERY_LEGS
  WDL1, WSH_NEW_DELIVERIES WND WHERE WDL1.DELIVERY_ID <> :B1 AND
  WDL1.DELIVERY_ID = WND.DELIVERY_ID AND WND.DELIVERY_TYPE = 'STANDARD' AND (
  WDL1.PICK_UP_STOP_ID = WTS.STOP_ID OR WDL1.DROP_OFF_STOP_ID = WTS.STOP_ID )
  ) AND ROWNUM = 1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.02 0.02 0 3 0 0
Fetch 100 731.88 1681.12 28279905 25817579 1800 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 731.91 1681.14 28279905 25817582 1800 0

Have applied the <patch 24348694>:R12.WSH.C.

Patch was successful but performance has not improved

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Submit the custom program to ship confirm the deliveries.
This program calls the seeded wsh_deliveries_pub.delivery_action API
Slow performance is observed

 

Changes

 

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