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

Last updated on JUNE 27, 2017

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 (per note 2175181.1)
Fixed files are as follows:
WSHYMSIB.pls 120.0.12020000.18
WSHYMSIS.pls 120.0.12020000.10

Patch was successful bu 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

 

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